-
Notifications
You must be signed in to change notification settings - Fork 1
/
db.py
151 lines (110 loc) · 4.02 KB
/
db.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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
## hood - collective neighborhood opinion map generator
##Copyright (C) 2005 Matt Chisholm & Ross Cohen
##This program is free software; you can redistribute it and/or
##modify it under the terms of the GNU General Public License
##as published by the Free Software Foundation, version 2.
##This program is distributed in the hope that it will be useful,
##but WITHOUT ANY WARRANTY; without even the implied warranty of
##MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
##GNU General Public License for more details.
from psycopg2 import psycopg1 as psycopg
class DB(object):
def __init__(self, config):
self.conn = None
self.host = config['db_host']
self.db = config['db_name']
self.user = config['db_user']
self.pwd = config['db_pass']
self.open_db()
def open_db(self):
self.conn = psycopg.connect("host=%s dbname=%s user=%s password=%s"
% (self.host, self.db, self.user, self.pwd))
def close_db(self):
self.conn.close()
def cursor(self):
return self.conn.cursor()
def _key_tuple(self, keys):
return ['%s = %%(%s)s'%(k,k) for k in keys]
def get(self, select, d):
curs = self.cursor()
curs.execute(select, d)
res = []
row = curs.fetchone()
while row is not None:
res.append(row)
row = curs.fetchone()
return res
def get_by_dict(self, d):
select = """
SELECT * FROM location WHERE
""" + \
' AND '.join( self._key_tuple(d.keys()) )
return self.get(select, d)
def get_mappable_by_hood_id(self, hood_id, distinct=True):
distinct = distinct and 'DISTINCT' or ''
select_hood = """SELECT %s long, lat FROM location """ % distinct
select_stat = """SELECT
AVG(%s long),
AVG(%s lat ),
STDDEV(%s long),
STDDEV(%s lat )
FROM location """ % ((distinct,)*4)
where = """
WHERE
hood_id = %(hood_id)s
AND
long IS NOT NULL AND lat IS NOT NULL
AND
lat <= 37.84 AND lat >= 37.70
AND
long <= -122.36 AND long >= -122.52
;
"""
hoods = self.get(select_hood+where, {'hood_id':hood_id})
stats = self.get(select_stat+where, {'hood_id':hood_id})[0]
avg = stats[0:2]
stddev = stats[2:]
return avg, stddev, hoods
def get_mappable_no_hood(self, hood, distinct=True):
distinct = distinct and 'DISTINCT' or ''
select = """
SELECT %s long, lat FROM location WHERE
long IS NOT NULL
AND
lat IS NOT NULL
AND
hood IS NULL
;
""" % distinct
return self.get(select, {'hood':hood})
def get_neighborhoods(self):
select = """SELECT id, name FROM neighborhood ORDER BY lower(name);"""
hoods = self.get(select, {})
return [(h[0], h[1]) for h in hoods]
def get_by_url(self, url):
return self.get_by_dict({'url':url})
def get_fixable(self):
select = """
SELECT * FROM location WHERE
lat IS NULL AND long IS NULL AND loc IS NOT NULL;
"""
return self.get(select, {})
def insert_location(self, values):
if not values.has_key('source'):
values['source'] = 1
cols = values.keys()
insert = "INSERT INTO location (id, " + \
', '.join(cols) + \
") VALUES (nextval('loc_seq'), " + \
', '.join(["%%(%s)s"%c for c in cols]) + ') ;'
curs = self.cursor()
curs.execute(insert, values)
self.conn.commit()
def update_location(self, loc_id, values):
update = "UPDATE location SET " + \
', '.join( self._key_tuple(values.keys()) ) +\
" WHERE id = %(loc_id)s; "
values['loc_id'] = loc_id
curs = self.cursor()
curs.execute(update, values)
self.conn.commit()