Mercurial > pylearn

annotate pylearn/dbdict/api0.py @ 538:798607a058bd

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