1
|
# Database access
|
2
|
|
3
|
import random
|
4
|
import re
|
5
|
import sys
|
6
|
|
7
|
import exc
|
8
|
import util
|
9
|
|
10
|
def get_cur_query(cur):
|
11
|
if hasattr(cur, 'query'): return cur.query
|
12
|
elif hasattr(cur, '_last_executed'): return cur._last_executed
|
13
|
else: return None
|
14
|
|
15
|
def _add_cursor_info(e, cur): exc.add_msg(e, 'query: '+get_cur_query(cur))
|
16
|
|
17
|
class DbException(exc.ExceptionWithCause):
|
18
|
def __init__(self, msg, cause=None, cur=None):
|
19
|
exc.ExceptionWithCause.__init__(self, msg, cause)
|
20
|
if cur != None: _add_cursor_info(self, cur)
|
21
|
|
22
|
class NameException(DbException): pass
|
23
|
|
24
|
class ExceptionWithColumn(DbException):
|
25
|
def __init__(self, col, cause=None):
|
26
|
DbException.__init__(self, 'column: '+col, cause)
|
27
|
self.col = col
|
28
|
|
29
|
class DuplicateKeyException(ExceptionWithColumn): pass
|
30
|
|
31
|
class NullValueException(ExceptionWithColumn): pass
|
32
|
|
33
|
class EmptyRowException(DbException): pass
|
34
|
|
35
|
def check_name(name):
|
36
|
if re.search(r'\W', name) != None: raise NameException('Name "'+name
|
37
|
+'" may contain only alphanumeric characters and _')
|
38
|
|
39
|
def run_query(db, query, params=None):
|
40
|
cur = db.cursor()
|
41
|
try: cur.execute(query, params)
|
42
|
except Exception, e:
|
43
|
_add_cursor_info(e, cur)
|
44
|
raise
|
45
|
return cur
|
46
|
|
47
|
def col(cur, idx): return cur.description[idx][0]
|
48
|
|
49
|
def rows(cur): return iter(lambda: cur.fetchone(), None)
|
50
|
|
51
|
def row(cur): return rows(cur).next()
|
52
|
|
53
|
def value(cur): return row(cur)[0]
|
54
|
|
55
|
def values(cur): return iter(lambda: value(cur), None)
|
56
|
|
57
|
def value_or_none(cur):
|
58
|
try: return value(cur)
|
59
|
except StopIteration: return None
|
60
|
|
61
|
def with_savepoint(db, func):
|
62
|
savepoint = 'savepoint_'+str(random.randint(0, sys.maxint)) # must be unique
|
63
|
run_query(db, 'SAVEPOINT '+savepoint)
|
64
|
try: return_val = func()
|
65
|
except:
|
66
|
run_query(db, 'ROLLBACK TO SAVEPOINT '+savepoint)
|
67
|
raise
|
68
|
else:
|
69
|
run_query(db, 'RELEASE SAVEPOINT '+savepoint)
|
70
|
return return_val
|
71
|
|
72
|
def select(db, table, fields, conds, limit=None):
|
73
|
assert limit == None or type(limit) == int
|
74
|
check_name(table)
|
75
|
map(check_name, fields)
|
76
|
map(check_name, conds.keys())
|
77
|
def cond(entry):
|
78
|
col, value = entry
|
79
|
cond_ = col+' '
|
80
|
if value == None: cond_ += 'IS'
|
81
|
else: cond_ += '='
|
82
|
cond_ += ' %s'
|
83
|
return cond_
|
84
|
query = 'SELECT '+', '.join(fields)+' FROM '+table
|
85
|
if conds != {}:
|
86
|
query += ' WHERE '+' AND '.join(map(cond, conds.iteritems()))
|
87
|
if limit != None: query += ' LIMIT '+str(limit)
|
88
|
return run_query(db, query, conds.values())
|
89
|
|
90
|
def insert(db, table, row):
|
91
|
check_name(table)
|
92
|
cols = row.keys()
|
93
|
map(check_name, cols)
|
94
|
query = 'INSERT INTO '+table
|
95
|
if row != {}: query += ' ('+', '.join(cols)+') VALUES ('\
|
96
|
+', '.join(['%s']*len(cols))+')'
|
97
|
else: query += ' DEFAULT VALUES'
|
98
|
return run_query(db, query, row.values())
|
99
|
|
100
|
def last_insert_id(db):
|
101
|
module = util.root_module(db)
|
102
|
if module == 'psycopg2': return value(run_query(db, 'SELECT lastval()'))
|
103
|
elif module == 'MySQLdb': return db.insert_id()
|
104
|
else: return None
|
105
|
|
106
|
def constraint_cols(db, table, constraint):
|
107
|
check_name(table)
|
108
|
check_name(constraint)
|
109
|
module = util.root_module(db)
|
110
|
if module == 'psycopg2':
|
111
|
return list(values(run_query(db, '''\
|
112
|
SELECT attname
|
113
|
FROM pg_constraint
|
114
|
JOIN pg_class ON pg_class.oid = conrelid
|
115
|
JOIN pg_attribute ON attrelid = conrelid AND attnum = ANY (conkey)
|
116
|
WHERE
|
117
|
relname = %(table)s
|
118
|
AND conname = %(constraint)s
|
119
|
ORDER BY attnum
|
120
|
''',
|
121
|
{'table': table, 'constraint': constraint})))
|
122
|
else: raise NotImplementedError("Can't list constraint columns for "+module+
|
123
|
' database')
|
124
|
|
125
|
def try_insert(db, table, row):
|
126
|
try: return with_savepoint(db, lambda: insert(db, table, row))
|
127
|
except Exception, e:
|
128
|
msg = str(e)
|
129
|
match = re.search(r'duplicate key value violates unique constraint "'
|
130
|
+table+'_(\w+)_index"', msg)
|
131
|
if match: raise DuplicateKeyException(match.group(1), e)
|
132
|
match = re.search(r'null value in column "(\w+)" violates not-null '
|
133
|
'constraint', msg)
|
134
|
if match: raise NullValueException(match.group(1), e)
|
135
|
raise # no specific exception raised
|
136
|
|
137
|
def pkey(db, cache, table): # Assumed to be first column in table
|
138
|
check_name(table)
|
139
|
if table not in cache:
|
140
|
cache[table] = col(run_query(db, 'SELECT * FROM '+table+' LIMIT 0'), 0)
|
141
|
return cache[table]
|
142
|
|
143
|
def get(db, table, row, pkey, create=False, row_ct_ref=None):
|
144
|
try: return value(select(db, table, [pkey], row, 1))
|
145
|
except StopIteration:
|
146
|
if not create: raise
|
147
|
# Insert new row
|
148
|
try:
|
149
|
row_ct = try_insert(db, table, row).rowcount
|
150
|
if row_ct_ref != None and row_ct >= 0: row_ct_ref[0] += row_ct
|
151
|
return last_insert_id(db)
|
152
|
except DuplicateKeyException, e:
|
153
|
return value(select(db, table, [pkey], {e.col: row[e.col]}))
|
154
|
|
155
|
def truncate(db, table):
|
156
|
check_name(table)
|
157
|
return run_query(db, 'TRUNCATE '+table+' CASCADE')
|
158
|
|
159
|
def tables(db):
|
160
|
module = util.root_module(db)
|
161
|
if module == 'psycopg2':
|
162
|
return values(run_query(db, "SELECT tablename from pg_tables "
|
163
|
"WHERE schemaname = 'public' ORDER BY tablename"))
|
164
|
elif module == 'MySQLdb': return values(run_query(db, 'SHOW TABLES'))
|
165
|
else: raise NotImplementedError("Can't list tables for "+module+' database')
|
166
|
|
167
|
def empty_db(db):
|
168
|
for table in tables(db): truncate(db, table)
|
169
|
|
170
|
db_engines = {
|
171
|
'MySQL': ('MySQLdb', {'password': 'passwd', 'database': 'db'}),
|
172
|
'PostgreSQL': ('psycopg2', {}),
|
173
|
}
|
174
|
|
175
|
DatabaseErrors_set = set([DbException])
|
176
|
DatabaseErrors = tuple(DatabaseErrors_set)
|
177
|
|
178
|
def _add_module(module):
|
179
|
DatabaseErrors_set.add(module.DatabaseError)
|
180
|
global DatabaseErrors
|
181
|
DatabaseErrors = tuple(DatabaseErrors_set)
|
182
|
|
183
|
def connect(db_config):
|
184
|
db_config = db_config.copy() # don't modify input!
|
185
|
module_name, mappings = db_engines[db_config.pop('engine')]
|
186
|
module = __import__(module_name)
|
187
|
_add_module(module)
|
188
|
for orig, new in mappings.iteritems():
|
189
|
try: util.rename_key(db_config, orig, new)
|
190
|
except KeyError: pass
|
191
|
return module.connect(**db_config)
|