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 if self.cn is not None: self.cn.close()
238 self.cn = None
239
240 - def AddTable(self,tableName,colString):
241 """ adds a table to the database
242
243 **Arguments**
244
245 - tableName: the name of the table to add
246
247 - colString: a string containing column defintions
248
249 **Notes**
250
251 - if a table named _tableName_ already exists, it will be dropped
252
253 - the sqlQuery for addition is: "create table %(tableName) (%(colString))"
254
255
256 """
257 c = self.GetCursor()
258 try:
259 c.execute('drop table %s cascade'%tableName)
260 except Exception:
261 try:
262 c.execute('drop table %s'%tableName)
263 except Exception:
264 pass
265 self.Commit()
266
267 addStr = 'create table %s (%s)'%(tableName,colString)
268 try:
269 c.execute(addStr)
270 except Exception:
271 import traceback
272 print('command failed:',addStr)
273 traceback.print_exc()
274 else:
275 self.Commit()
277 """ inserts data into a table
278
279 **Arguments**
280
281 - tableName: the name of the table to manipulate
282
283 - vals: a sequence with the values to be inserted
284
285 """
286 c = self.GetCursor()
287 if type(vals) != tuple:
288 vals = tuple(vals)
289 insTxt = '('+','.join([DbModule.placeHolder]*len(vals))+')'
290
291
292 cmd = "insert into %s values %s"%(tableName,insTxt)
293 try:
294 c.execute(cmd,vals)
295 except Exception:
296 import traceback
297 print('insert failed:')
298 print(cmd)
299 print('the error was:')
300 traceback.print_exc()
301 raise DbError("Insert Failed")
302
304 """ inserts data into a particular column of the table
305
306 **Arguments**
307
308 - tableName: the name of the table to manipulate
309
310 - columnName: name of the column to update
311
312 - value: the value to insert
313
314 - where: a query yielding the row where the data should be inserted
315
316 """
317 c = self.GetCursor()
318 cmd = "update %s set %s=%s where %s"%(tableName,columnName,
319 DbModule.placeHolder,where)
320 c.execute(cmd,(value,))
321
322 - def AddColumn(self,tableName,colName,colType):
323 """ adds a column to a table
324
325 **Arguments**
326
327 - tableName: the name of the table to manipulate
328
329 - colName: name of the column to insert
330
331 - colType: the type of the column to add
332
333 """
334 c = self.GetCursor()
335 try:
336 c.execute("alter table %s add %s %s"%(tableName,colName,colType))
337 except Exception:
338 print('AddColumn failed')
339
341 """ commits the current transaction
342
343
344 """
345 self.cn.commit()
346