1
2
3
4
5
6
7 """ defines class _DbConnect_, for abstracting connections to databases
8
9 """
10 from rdkit import RDConfig
11 import sys,types
12 import exceptions
13
16
17 from rdkit.Dbase import DbUtils,DbInfo
18 import DbModule
19
20
22 """ This class is intended to abstract away many of the details of
23 interacting with databases.
24
25 It includes some GUI functionality
26
27 """
28 - def __init__(self,dbName='',tableName='',user='sysdba',password='masterkey'):
29 """ Constructor
30
31 **Arguments** (all optional)
32
33 - dbName: the name of the DB file to be used
34
35 - tableName: the name of the table to be used
36
37 - user: the username for DB access
38
39 - password: the password to be used for DB access
40
41
42 """
43
44 self.dbName = dbName
45 self.tableName = tableName
46 self.user = user
47 self.password = password
48 self.cn = None
49 self.cursor = None
50
52 """ Modifies a connect dialog to reflect new table names
53
54 **Arguments**
55
56 - dlg: the dialog to be updated
57
58
59 """
60 self.user = self.userEntry.GetValue()
61 self.password = self.passwdEntry.GetValue()
62 self.dbName = self.dbBrowseButton.GetValue()
63 for i in xrange(self.dbTableChoice.Number()):
64 self.dbTableChoice.Delete(0)
65
66 names = self.GetTableNames()
67
68 for name in names:
69 self.dbTableChoice.Append(name)
70 dlg.sizer.Fit(dlg)
71 dlg.sizer.SetSizeHints(dlg)
72 dlg.Refresh()
73
75 """ gets a list of tables available in a database
76
77 **Arguments**
78
79 - includeViews: if this is non-null, the views in the db will
80 also be returned
81
82 **Returns**
83
84 a list of table names
85
86 **Notes**
87
88 - this uses _DbInfo.GetTableNames_
89
90
91 """
92 return DbInfo.GetTableNames(self.dbName,self.user,self.password,
93 includeViews=includeViews,cn=self.cn)
94
96 """ gets a list of columns available in the current table
97
98 **Returns**
99
100 a list of column names
101
102 **Notes**
103
104 - this uses _DbInfo.GetColumnNames_
105
106
107 """
108 if not table: table = self.tableName
109 return DbInfo.GetColumnNames(self.dbName,table,
110 self.user,self.password,
111 join=join,what=what,cn=self.cn)
113 """ gets a list of columns available in the current table along with their types
114
115 **Returns**
116
117 a list of 2-tuples containing:
118
119 1) column name
120
121 2) column type
122
123 **Notes**
124
125 - this uses _DbInfo.GetColumnNamesAndTypes_
126
127
128 """
129 if not table: table = self.tableName
130 return DbInfo.GetColumnNamesAndTypes(self.dbName,table,
131 self.user,self.password,
132 join=join,what=what,cn=self.cn)
133 - def GetColumns(self,fields,table='',join='',**kwargs):
134 """ gets a set of data from a table
135
136 **Arguments**
137
138 - fields: a string with the names of the fields to be extracted,
139 this should be a comma delimited list
140
141 **Returns**
142
143 a list of the data
144
145 **Notes**
146
147 - this uses _DbUtils.GetColumns_
148
149 """
150 if not table: table = self.tableName
151 return DbUtils.GetColumns(self.dbName,table,fields,
152 self.user,self.password,
153 join=join)
154
155 - def GetData(self,table=None,fields='*',where='',removeDups=-1,join='',
156 transform=None,randomAccess=1,**kwargs):
157 """ a more flexible method to get a set of data from a table
158
159 **Arguments**
160
161 - table: (optional) the table to use
162
163 - fields: a string with the names of the fields to be extracted,
164 this should be a comma delimited list
165
166 - where: the SQL where clause to be used with the DB query
167
168 - removeDups: indicates which column should be used to recognize
169 duplicates in the data. -1 for no duplicate removal.
170
171 **Returns**
172
173 a list of the data
174
175 **Notes**
176
177 - this uses _DbUtils.GetData_
178
179
180 """
181 if table is None:
182 table = self.tableName
183 kwargs['forceList'] = kwargs.get('forceList',0)
184 return DbUtils.GetData(self.dbName,table,fieldString=fields,whereString=where,
185 user=self.user,password=self.password,removeDups=removeDups,
186 join=join,cn=self.cn,
187 transform=transform,randomAccess=randomAccess,**kwargs)
188
189 - def GetDataCount(self,table=None,where='',join='',**kwargs):
190 """ returns a count of the number of results a query will return
191
192 **Arguments**
193
194 - table: (optional) the table to use
195
196 - where: the SQL where clause to be used with the DB query
197
198 - join: the SQL join clause to be used with the DB query
199
200
201 **Returns**
202
203 an int
204
205 **Notes**
206
207 - this uses _DbUtils.GetData_
208
209 """
210 if table is None:
211 table = self.tableName
212 return DbUtils.GetData(self.dbName,table,fieldString='count(*)',
213 whereString=where,cn=self.cn,
214 user=self.user,password=self.password,join=join,forceList=0)[0][0]
215
216
218 """ returns a cursor for direct manipulation of the DB
219 only one cursor is available
220
221 """
222 if self.cursor is not None:
223 return self.cursor
224
225 self.cn = DbModule.connect(self.dbName,self.user,self.password)
226 self.cursor = self.cn.cursor()
227 return self.cursor
228
230 """ closes the cursor
231
232 """
233 self.cursor = None
234 self.cn = None
235
236 - def AddTable(self,tableName,colString):
237 """ adds a table to the database
238
239 **Arguments**
240
241 - tableName: the name of the table to add
242
243 - colString: a string containing column defintions
244
245 **Notes**
246
247 - if a table named _tableName_ already exists, it will be dropped
248
249 - the sqlQuery for addition is: "create table %(tableName) (%(colString))"
250
251
252 """
253 c = self.GetCursor()
254 try:
255 c.execute('drop table %s cascade'%tableName)
256 except:
257 try:
258 c.execute('drop table %s'%tableName)
259 except:
260 pass
261 self.Commit()
262
263 addStr = 'create table %s (%s)'%(tableName,colString)
264 try:
265 c.execute(addStr)
266 except:
267 import traceback
268 print 'command failed:',addStr
269 traceback.print_exc()
270 else:
271 self.Commit()
273 """ inserts data into a table
274
275 **Arguments**
276
277 - tableName: the name of the table to manipulate
278
279 - vals: a sequence with the values to be inserted
280
281 """
282 c = self.GetCursor()
283 if type(vals) != types.TupleType:
284 vals = tuple(vals)
285 insTxt = '('+','.join([DbModule.placeHolder]*len(vals))+')'
286
287
288 cmd = "insert into %s values %s"%(tableName,insTxt)
289 try:
290 c.execute(cmd,vals)
291 except:
292 import traceback
293 print 'insert failed:'
294 print cmd
295 print 'the error was:'
296 traceback.print_exc()
297 raise DbError,"Insert Failed"
298
300 """ inserts data into a particular column of the table
301
302 **Arguments**
303
304 - tableName: the name of the table to manipulate
305
306 - columnName: name of the column to update
307
308 - value: the value to insert
309
310 - where: a query yielding the row where the data should be inserted
311
312 """
313 c = self.GetCursor()
314 cmd = "update %s set %s=%s where %s"%(tableName,columnName,
315 DbModule.placeHolder,where)
316 c.execute(cmd,(value,))
317
318 - def AddColumn(self,tableName,colName,colType):
319 """ adds a column to a table
320
321 **Arguments**
322
323 - tableName: the name of the table to manipulate
324
325 - colName: name of the column to insert
326
327 - colType: the type of the column to add
328
329 """
330 c = self.GetCursor()
331 try:
332 c.execute("alter table %s add %s %s"%(tableName,colName,colType))
333 except:
334 print 'AddColumn failed'
335
337 """ commits the current transaction
338
339
340 """
341 self.cn.commit()
342