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