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