-
Notifications
You must be signed in to change notification settings - Fork 3
/
lobby.py
124 lines (100 loc) · 4.62 KB
/
lobby.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
"""
Import OpenSecrets.org's lobbying tables to MySQL
"""
import MySQLdb
import sys
import logging
import os
import re
class LobbyDownloader(object):
def __init__(self,cursor,path):
self.cursor = cursor
self.dest_path = path
def createtables(self):
queries = [
"""CREATE TABLE IF NOT EXISTS crp_lobbying(
uniqid varchar(56) NOT NULL,
registrant_raw varchar(95) NULL,
registrant varchar(40) NULL,
isfirm char(1) NULL,
client_raw varchar(95) NULL,
client varchar(40) NULL,
ultorg varchar(40) NULL,
amount float NULL,
catcode char(5) NULL,
source char (5) NULL,
self char(1) NULL,
IncludeNSFS char(1) NULL,
usethis char(1) NULL,
ind char(1) NULL,
year char(4) NULL,
type char(4) NULL,
typelong varchar(50) NULL,
orgID char(10) NULL,
affiliate char(1) NULL,
PRIMARY KEY (uniqid)
);""",
"""CREATE TABLE IF NOT EXISTS crp_lobbyist(
uniqID varchar(56) NOT NULL,
lobbyist varchar(50) NULL,
lobbyist_raw varchar(50) NULL,
lobbyist_id char(15) NULL,
year varchar(5) NULL,
Offic_position varchar(100) NULL,
cid char (12) NULL,
formercongmem char(1) NULL,
INDEX u (uniqID)
);""",
"""CREATE TABLE IF NOT EXISTS crp_lob_indus(
client varchar(40) NULL,
sub varchar(40) NULL,
total float NULL,
year char(4) NULL,
catcode char(5) NULL
);""",
"""CREATE TABLE IF NOT EXISTS crp_lob_agency(
uniqID varchar(56) NOT NULL,
agencyID char(4) NOT NULL,
Agency varchar(80) NULL,
INDEX u (uniqID)
);""",
"""CREATE TABLE IF NOT EXISTS crp_lob_issue(
SI_ID int NOT NULL,
uniqID varchar(56) NOT NULL,
issueID char(3) NOT NULL,
issue varchar(50) NULL,
SpecificIssue varchar(255) NULL,
year char (4) NULL
);""",
"""CREATE TABLE IF NOT EXISTS crp_lob_bills(
B_ID int NULL,
si_id int NULL,
CongNo char(3) NULL,
Bill_Name varchar(15) NOT NULL
);""",
"""CREATE TABLE IF NOT EXISTS crp_lob_rpt(
TypeLong varchar (50) NOT NULL,
Typecode char(4) NOT NULL
);"""
]
for query in queries:
self.cursor.execute(query)
def populatetables(self):
ext = ".txt"
self.cursor.execute("DELETE FROM crp_lobbying")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_lobbying" + ext)))
self.cursor.execute("DELETE FROM crp_lobbyist")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_lobbyist" + ext)))
self.cursor.execute("DELETE FROM crp_lob_indus")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_indus" + ext)))
self.cursor.execute("DELETE FROM crp_lob_agency")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_agency" + ext)))
self.cursor.execute("DELETE FROM crp_lob_issue")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_issue" + ext)))
self.cursor.execute("DELETE FROM crp_lob_bills")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_bills" + ext)))
self.cursor.execute("DELETE FROM crp_lob_rpt")
self.cursor.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE crp_%s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '|'" % ( os.path.join(self.dest_path, "lob_rpt" + ext)))
def go(self):
self.createtables()
self.populatetables()