Mercurial > pylearn
annotate pylearn/dbdict/api0.py @ 628:739720896d61
added get by id to api0
author | James Bergstra <bergstrj@iro.umontreal.ca> |
---|---|
date | Mon, 19 Jan 2009 18:34:13 -0500 |
parents | 25d9f91f1afa |
children | 4e7781972999 |
rev | line source |
---|---|
538 | 1 from sqlalchemy import create_engine, desc |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
2 import sqlalchemy.pool |
538 | 3 from sqlalchemy.orm import sessionmaker |
4 from sqlalchemy import Table, Column, MetaData, ForeignKey | |
5 from sqlalchemy import Integer, String, Float, Boolean, DateTime, Text, Binary | |
6 from sqlalchemy.orm import mapper, relation, backref, eagerload | |
7 from sqlalchemy.sql import operators, select | |
8 from sql_commands import crazy_sql_command | |
9 | |
10 class Todo(Exception): """Replace this with some working code!""" | |
11 | |
12 class DbHandle (object): | |
13 """ | |
14 This class also provides filtering shortcuts that hide the names of the | |
15 DbHandle internal databases. | |
16 | |
17 Attributes: | |
18 dict_table | |
19 pair_table | |
20 link_table | |
21 | |
22 | |
23 | |
24 dict_table | |
25 | |
26 An SqlAlchemy-mapped class corresponding to database table with the | |
27 following schema: | |
28 | |
29 Column('id', Integer, primary_key=True) | |
30 Column('create', DateTime) | |
31 Column('write', DateTime) | |
32 Column('read', DateTime) | |
33 | |
34 #TODO: reconsider create/read/write | |
35 | |
36 pair_table | |
37 | |
38 An SqlAlchemy-mapped class corresponding to database table with the | |
39 following schema: | |
40 | |
41 Column('id', Integer, primary_key=True) | |
42 Column('name', String(128)) | |
43 Column('ntype', Boolean) | |
44 Column('fval', Double) | |
45 Column('sval', Text)) | |
46 Column('bval', Blob) | |
47 | |
48 #TODO: Consider difference between text and binary | |
49 #TODO: Consider adding a 'type' column | |
50 #TODO: Consider union? | |
51 #TODO: Are there stanard ways of doing this kind of thing? | |
52 | |
53 link_table | |
54 | |
55 An SqlAlchemy-mapped class corresponding to database table with the | |
56 following schema: | |
57 | |
58 Column('dict_id', Integer, ForeignKey('%s.id' % t_trial), primary_key=True), | |
59 Column('keyval_id', Integer, ForeignKey('%s.id' % t_keyval), primary_key=True)) | |
60 | |
61 """ | |
62 | |
63 e_bad_table = 'incompatible columns in table' | |
64 | |
65 def __init__(h_self, Session, engine, dict_table, pair_table, link_table): | |
66 h_self._engine = engine; | |
67 h_self._dict_table = dict_table | |
68 h_self._pair_table = pair_table | |
69 h_self._link_table = link_table | |
70 | |
71 #TODO: replace this crude algorithm (ticket #17) | |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
72 if ['id', 'create', 'write', 'read', 'status', 'priority'] != [c.name for c in dict_table.c]: |
538 | 73 raise ValueError(h_self.e_bad_table, dict_table) |
74 if ['id', 'name', 'ntype', 'fval', 'sval', 'bval'] != [c.name for c in pair_table.c]: | |
75 raise ValueError(h_self.e_bad_table, pair_table) | |
76 if ['dict_id', 'pair_id'] != [c.name for c in link_table.c]: | |
77 raise ValueError(h_self.e_bad_table, pair_table) | |
78 | |
79 h_self._session_fn = Session | |
80 | |
81 class KeyVal (object): | |
82 """KeyVal interfaces between python types and the database. | |
83 | |
84 It encapsulates heuristics for type conversion. | |
85 """ | |
86 def __init__(k_self, name, val): | |
87 k_self.name = name | |
88 k_self.val = val | |
89 def __repr__(k_self): | |
90 return "<Param(%s,'%s', %s)>" % (k_self.id, k_self.name, repr(k_self.val)) | |
91 def __get_val(k_self): | |
92 val = None | |
93 if k_self.fval is not None: val = [int, float][k_self.ntype](k_self.fval) | |
94 if k_self.bval is not None: val = eval(str(k_self.bval)) | |
95 if k_self.sval is not None: val = k_self.sval | |
96 return val | |
97 def __set_val(k_self, val): | |
98 if isinstance(val, (str,unicode)): | |
99 k_self.fval = None | |
100 k_self.bval = None | |
101 k_self.sval = val | |
102 else: | |
103 k_self.sval = None | |
104 try: | |
105 f = float(val) | |
106 except (TypeError, ValueError): | |
107 f = None | |
108 if f is None: #binary data | |
109 k_self.bval = repr(val) | |
110 assert eval(k_self.bval) == val | |
111 k_self.fval = None | |
112 k_self.ntype = None | |
113 else: | |
114 k_self.bval = None | |
115 k_self.fval = f | |
116 k_self.ntype = isinstance(val,float) | |
117 val = property(__get_val, __set_val) | |
118 | |
119 mapper(KeyVal, pair_table) | |
120 | |
121 class Dict (object): | |
122 """ | |
123 Instances are dict-like objects with additional features for | |
124 communicating with an active database. | |
125 | |
126 This class will be mapped by SqlAlchemy to the dict_table. | |
127 | |
128 Attributes: | |
129 handle - reference to L{DbHandle} (creator) | |
130 | |
131 """ | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
132 def __init__(d_self, session=None): |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
133 if session is None: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
134 s = h_self._session_fn() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
135 s.add(d_self) #d_self transient -> pending |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
136 s.commit() #d_self -> persistent |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
137 s.close() #d_self -> detached |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
138 else: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
139 s = session |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
140 s.save(d_self) |
538 | 141 |
142 _forbidden_keys = set(['session']) | |
143 | |
144 # | |
145 # dictionary interface | |
146 # | |
147 | |
148 def __contains__(d_self, key): | |
149 for a in d_self._attrs: | |
150 if a.name == key: | |
151 return True | |
152 return False | |
153 | |
154 def __eq__(self, other): | |
155 return dict(self) == dict(other) | |
156 def __neq__(self, other): | |
157 return dict(self) != dict(other) | |
158 | |
159 def __getitem__(d_self, key): | |
160 for a in d_self._attrs: | |
161 if a.name == key: | |
162 return a.val | |
163 raise KeyError(key) | |
164 | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
165 def __setitem__(d_self, key, val, session=None): |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
166 if session is None: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
167 s = h_self._session_fn() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
168 s.add(d_self) |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
169 d_self._set_in_session(key, val, s) |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
170 s.commit() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
171 s.close() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
172 else: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
173 s = session |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
174 s.add(d_self) |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
175 d_self._set_in_session(key, val, s) |
538 | 176 |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
177 def __delitem__(d_self, key, session=None): |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
178 if session is None: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
179 s = h_self._session_fn() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
180 commit_close = True |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
181 else: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
182 s = session |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
183 commit_close = False |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
184 s.add(d_self) |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
185 |
538 | 186 #find the item to delete in d_self._attrs |
187 to_del = None | |
188 for i,a in enumerate(d_self._attrs): | |
189 if a.name == key: | |
190 assert to_del is None | |
191 to_del = (i,a) | |
192 if to_del is None: | |
193 raise KeyError(key) | |
194 else: | |
195 i, a = to_del | |
196 s.delete(a) | |
197 del d_self._attrs[i] | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
198 if commit_close: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
199 s.commit() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
200 s.close() |
538 | 201 |
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
538
diff
changeset
|
202 def iteritems(d_self): |
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
538
diff
changeset
|
203 return d_self.items() |
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
538
diff
changeset
|
204 |
538 | 205 def items(d_self): |
206 return [(kv.name, kv.val) for kv in d_self._attrs] | |
207 | |
208 def keys(d_self): | |
209 return [kv.name for kv in d_self._attrs] | |
210 | |
211 def values(d_self): | |
212 return [kv.val for kv in d_self._attrs] | |
213 | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
214 def update(d_self, dct, session=None, **kwargs): |
538 | 215 """Like dict.update(), set keys from kwargs""" |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
216 if session is None: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
217 s = h_self._session_fn() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
218 commit_close = True |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
219 else: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
220 s = session |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
221 commit_close = False |
624
25d9f91f1afa
fixing session management in db dict update
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
623
diff
changeset
|
222 s.add(d_self) |
538 | 223 for k, v in dct.items(): |
224 d_self._set_in_session(k, v, s) | |
225 for k, v in kwargs.items(): | |
226 d_self._set_in_session(k, v, s) | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
227 |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
228 if commit_close: |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
229 s.commit() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
230 s.close() |
538 | 231 |
232 def get(d_self, key, default): | |
233 try: | |
234 return d_self[key] | |
235 except KeyError: | |
236 return default | |
237 | |
589 | 238 def __str__(self): |
239 return 'Dict'+ str(dict(self)) | |
240 | |
538 | 241 # |
242 # database stuff | |
243 # | |
244 | |
245 def refresh(d_self, session=None): | |
246 """Sync key-value pairs from database to self | |
247 | |
248 @param session: use the given session, and do not commit. | |
249 | |
250 """ | |
251 if session is None: | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
252 session = h_self._session_fn() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
253 session.add(d_self) #so session knows about us |
538 | 254 session.refresh(d_self) |
255 session.commit() | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
256 session.close() |
538 | 257 else: |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
258 session.add(d_self) #so session knows about us |
538 | 259 session.refresh(self.dbrow) |
260 | |
261 def delete(d_self, session=None): | |
262 """Delete this dictionary from the database | |
263 | |
264 @param session: use the given session, and do not commit. | |
265 """ | |
266 if session is None: | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
267 session = h_self._session_fn() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
268 session.add(d_self) #so session knows about us |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
269 session.delete(d_self) #mark for deletion |
538 | 270 session.commit() |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
271 session.close() |
538 | 272 else: |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
273 session.add(d_self) #so session knows about us |
538 | 274 session.delete(d_self) |
275 | |
276 # helper routine by update() and __setitem__ | |
277 def _set_in_session(d_self, key, val, session): | |
278 """Modify an existing key or create a key to hold val""" | |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
279 #FIRST SOME MIRRORING HACKS |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
280 if key == 'dbdict.status': |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
281 ival = int(val) |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
282 d_self.status = ival |
624
25d9f91f1afa
fixing session management in db dict update
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
623
diff
changeset
|
283 if key == 'dbdict.sql.priority': |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
284 fval = float(val) |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
285 d_self.priority = fval |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
286 |
538 | 287 if key in d_self._forbidden_keys: |
288 raise KeyError(key) | |
289 created = None | |
290 for i,a in enumerate(d_self._attrs): | |
291 if a.name == key: | |
292 assert created == None | |
293 created = h_self._KeyVal(key, val) | |
294 d_self._attrs[i] = created | |
295 if not created: | |
296 created = h_self._KeyVal(key, val) | |
297 d_self._attrs.append(created) | |
298 session.save(created) | |
299 | |
300 mapper(Dict, dict_table, | |
301 properties = { | |
302 '_attrs': relation(KeyVal, | |
303 secondary=link_table, | |
304 cascade="all, delete-orphan") | |
305 }) | |
306 | |
307 class _Query (object): | |
308 """ | |
309 Attributes: | |
310 _query - SqlAlchemy.Query object | |
311 """ | |
312 | |
313 def __init__(q_self, query): | |
314 q_self._query = query | |
315 | |
316 def __iter__(q_self): | |
317 return q_self.all().__iter__() | |
318 | |
319 def __getitem__(q_self, item): | |
320 return q_self._query.__getitem__(item) | |
321 | |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
322 def filter_eq(q_self, kw, arg): |
538 | 323 """Return a Query object that restricts to dictionaries containing |
324 the given kwargs""" | |
325 | |
326 #Note: when we add new types to the key columns, add them here | |
327 q = q_self._query | |
328 T = h_self._Dict | |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
329 if isinstance(arg, (str,unicode)): |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
330 q = q.filter(T._attrs.any(name=kw, sval=arg)) |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
331 else: |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
332 try: |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
333 f = float(arg) |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
334 except (TypeError, ValueError): |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
335 f = None |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
336 if f is None: |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
337 q = q.filter(T._attrs.any(name=kw, bval=repr(arg))) |
538 | 338 else: |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
339 q = q.filter(T._attrs.any(name=kw, fval=f)) |
538 | 340 |
341 return h_self._Query(q) | |
342 | |
619
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
343 def filter_eq_dct(q_self, dct): |
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
344 rval = q_self |
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
345 for key, val in dct.items(): |
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
346 rval = rval.filter_eq(key,val) |
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
347 return rval |
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
348 |
538 | 349 def all(q_self): |
350 """Return an iterator over all matching dictionaries. | |
351 | |
352 See L{SqlAlchemy.Query} | |
353 """ | |
354 return q_self._query.all() | |
355 | |
356 def count(q_self): | |
357 """Return the number of matching dictionaries. | |
358 | |
359 See L{SqlAlchemy.Query} | |
360 """ | |
361 return q_self._query.count() | |
362 | |
363 def first(q_self): | |
364 """Return some matching dictionary, or None | |
365 See L{SqlAlchemy.Query} | |
366 """ | |
367 return q_self._query.first() | |
368 | |
369 def all_ordered_by(q_self, key, desc=False): | |
370 """Return query results, sorted. | |
371 | |
372 @type key: string or tuple of string or list of string | |
373 @param: keys by which to sort the results. | |
374 | |
375 @rtype: list of L{DbHandle._Dict} instances | |
376 @return: query results, sorted by given keys | |
377 """ | |
378 | |
379 # order_by is not easy to do in SQL based on the data structures we're | |
380 # using. Considering we support different data types, it may not be | |
381 # possible at all. | |
382 # | |
383 # It would be easy if 'pivot' or 'crosstab' were provided as part of the | |
384 # underlying API, but they are not. For example, read this: | |
385 # http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/ | |
386 | |
387 # load query results | |
388 results = list(q_self.all()) | |
389 | |
390 if isinstance(key, (tuple, list)): | |
391 val_results = [([d[k] for k in key], d) for d in results] | |
392 else: | |
393 val_results = [(d[key], d) for d in results] | |
394 | |
395 val_results.sort() #interesting: there is an optional key parameter | |
396 if desc: | |
397 val_results.reverse() | |
398 return [vr[-1] for vr in val_results] | |
399 | |
400 h_self._KeyVal = KeyVal | |
401 h_self._Dict = Dict | |
402 h_self._Query = _Query | |
403 | |
404 def __iter__(h_self): | |
623
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
405 s = h_self.session() |
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
406 rval = list(h_self.query(s).__iter__()) |
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
407 s.close() |
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
408 return rval.__iter__() |
538 | 409 |
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
410 def insert_kwargs(h_self, session=None, **dct): |
538 | 411 """ |
412 @rtype: DbHandle with reference to self | |
413 @return: a DbHandle initialized as a copy of dct | |
414 | |
415 @type dct: dict-like instance whose keys are strings, and values are | |
416 either strings, integers, floats | |
417 | |
418 @param dct: dictionary to insert | |
419 | |
420 """ | |
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
421 return h_self.insert(dct, session=session) |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
422 |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
423 def insert(h_self, dct, session=None): |
538 | 424 """ |
425 @rtype: DbHandle with reference to self | |
426 @return: a DbHandle initialized as a copy of dct | |
427 | |
428 @type dct: dict-like instance whose keys are strings, and values are | |
429 either strings, integers, floats | |
430 | |
431 @param dct: dictionary to insert | |
432 | |
433 """ | |
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
434 if session is None: |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
435 s = h_self.session() |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
436 rval = h_self._Dict(s) |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
437 if dct: rval.update(dct, session=s) |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
438 s.commit() |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
439 s.close() |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
440 else: |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
441 rval = h_self._Dict(session) |
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
442 if dct: rval.update(dct, session=session) |
538 | 443 return rval |
444 | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
445 def query(h_self, session): |
538 | 446 """Construct an SqlAlchemy query, which can be subsequently filtered |
447 using the instance methods of DbQuery""" | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
448 return h_self._Query(session.query(h_self._Dict)\ |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
449 .options(eagerload('_attrs'))) |
538 | 450 |
451 def createView(h_self, view): | |
452 | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
453 s = h_self.session() |
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
454 cols = [] |
538 | 455 |
456 for col in view.columns: | |
457 if col.name is "id": | |
458 continue; | |
459 elif isinstance(col.type, (Integer,Float)): | |
460 cols.append([col.name,'fval']); | |
461 elif isinstance(col.type,String): | |
462 cols.append([col.name,'sval']); | |
463 elif isinstance(col.type,Binary): | |
464 cols.append([col.name,'bval']); | |
465 else: | |
466 assert "Error: wrong column type in view",view.name; | |
467 | |
468 # generate raw sql command string | |
469 viewsql = crazy_sql_command(view.name, cols, \ | |
470 h_self._pair_table.name, \ | |
471 h_self._link_table.name); | |
472 | |
473 #print 'Creating sql view with command:\n', viewsql; | |
474 | |
475 h_self._engine.execute(viewsql); | |
476 s.commit(); | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
477 s.close() |
538 | 478 |
479 class MappedClass(object): | |
480 pass | |
481 | |
482 mapper(MappedClass, view) | |
483 | |
484 return MappedClass | |
485 | |
486 def session(h_self): | |
487 return h_self._session_fn() | |
628
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
488 |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
489 def get(h_self, id): |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
490 s = h_self.session() |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
491 rval = s.query(h_self._Dict).get(id) |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
492 if rval: |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
493 #eagerload hack |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
494 str(rval) |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
495 rval.id |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
496 s.close() |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
497 return rval |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
498 |
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
499 |
538 | 500 |
501 | |
502 def db_from_engine(engine, | |
503 table_prefix='DbHandle_default_', | |
504 trial_suffix='trial', | |
505 keyval_suffix='keyval', | |
506 link_suffix='link'): | |
507 """Create a DbHandle instance | |
508 | |
509 @type engine: sqlalchemy engine (e.g. from create_engine) | |
510 @param engine: connect to this database for transactions | |
511 | |
512 @type table_prefix: string | |
513 @type trial_suffix: string | |
514 @type keyval_suffix: string | |
515 @type link_suffix: string | |
516 | |
517 @rtype: DbHandle instance | |
518 | |
519 @note: The returned DbHandle will use three tables to implement the | |
520 many-to-many pattern that it needs: | |
521 - I{table_prefix + trial_suffix}, | |
522 - I{table_prefix + keyval_suffix} | |
523 - I{table_prefix + link_suffix} | |
524 | |
525 """ | |
526 Session = sessionmaker(autoflush=True, autocommit=False) | |
527 | |
528 metadata = MetaData() | |
529 | |
530 t_trial = Table(table_prefix+trial_suffix, metadata, | |
531 Column('id', Integer, primary_key=True), | |
532 Column('create', DateTime), | |
533 Column('write', DateTime), | |
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
534 Column('read', DateTime), |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
535 Column('status', Integer), |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
536 Column('priority', Float(53)) |
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
537 ) |
538 | 538 |
539 t_keyval = Table(table_prefix+keyval_suffix, metadata, | |
540 Column('id', Integer, primary_key=True), | |
541 Column('name', String(128), nullable=False), #name of attribute | |
542 Column('ntype', Boolean), | |
543 Column('fval', Float(53)), | |
544 Column('sval', Text), | |
545 Column('bval', Binary)) | |
546 | |
547 t_link = Table(table_prefix+link_suffix, metadata, | |
548 Column('dict_id', Integer, ForeignKey('%s.id' % t_trial), | |
549 primary_key=True), | |
550 Column('pair_id', Integer, ForeignKey('%s.id' % t_keyval), | |
551 primary_key=True)) | |
552 | |
553 metadata.bind = engine | |
554 metadata.create_all() # no-op when tables already exist | |
555 #warning: tables can exist, but have incorrect schema | |
556 # see bug mentioned in DbHandle constructor | |
557 | |
558 return DbHandle(Session, engine, t_trial, t_keyval, t_link) | |
559 | |
560 def sqlite_memory_db(echo=False, **kwargs): | |
561 """Return a DbHandle backed by a memory-based database""" | |
562 engine = create_engine('sqlite:///:memory:', echo=False) | |
563 return db_from_engine(engine, **kwargs) | |
564 | |
565 def sqlite_file_db(filename, echo=False, **kwargs): | |
566 """Return a DbHandle backed by a file-based database""" | |
567 engine = create_engine('sqlite:///%s' % filename, echo=False) | |
568 return db_from_engine(engine, **kwargs) | |
569 | |
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
570 def postgres_db(user, password, host, database, echo=False, poolclass=sqlalchemy.pool.NullPool, **kwargs): |
538 | 571 """Create an engine to access a postgres_dbhandle |
572 """ | |
573 db_str ='postgres://%(user)s:%(password)s@%(host)s/%(database)s' % locals() | |
574 | |
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
575 engine = create_engine(db_str, echo=echo, poolclass=poolclass) |
538 | 576 |
577 return db_from_engine(engine, **kwargs) | |
578 |