def GetTableNames(dBase, user='******', password='******', includeViews=0, cn=None):
""" returns a list of tables available in a database
**Arguments**
- dBase: the name of the DB file to be used
- user: the username for DB access
- password: the password to be used for DB access
- includeViews: if this is non-null, the views in the db will
also be returned
**Returns**
- a list of table names (strings)
"""
if not cn:
try:
cn = DbModule.connect(dBase, user, password)
except Exception:
print('Problems opening database: %s' % (dBase))
return []
c = cn.cursor()
if not includeViews:
comm = DbModule.getTablesSql
else:
comm = DbModule.getTablesAndViewsSql
c.execute(comm)
names = [str(x[0]).upper() for x in c.fetchall()]
if RDConfig.usePgSQL and 'PG_LOGDIR_LS' in names:
names.remove('PG_LOGDIR_LS')
return names
def GetColumnNames(dBase, table, user='******', password='******', join='', what='*', cn=None):
""" gets a list of columns available in a DB table
**Arguments**
- dBase: the name of the DB file to be used
- table: the name of the table to query
- user: the username for DB access
- password: the password to be used for DB access
- join: an optional join clause (omit the verb 'join')
- what: an optional clause indicating what to select
**Returns**
- a list of column names
"""
if not cn:
cn = DbModule.connect(dBase, user, password)
c = cn.cursor()
cmd = 'select %s from %s' % (what, table)
if join:
if join.strip().find('join') != 0:
join = 'join %s' % (join)
cmd += ' ' + join
c.execute(cmd)
c.fetchone()
desc = c.description
res = [str(x[0]) for x in desc]
return res
def GetDbNames(user='******', password='******', dirName='.', dBase='::template1', cn=None):
""" returns a list of databases that are available
**Arguments**
- user: the username for DB access
- password: the password to be used for DB access
**Returns**
- a list of db names (strings)
"""
if DbModule.getDbSql:
if not cn:
try:
cn = DbModule.connect(dBase, user, password)
except Exception:
print('Problems opening database: %s' % (dBase))
return []
c = cn.cursor()
c.execute(DbModule.getDbSql)
if RDConfig.usePgSQL:
names = ['::' + str(x[0]) for x in c.fetchall()]
else:
names = ['::' + str(x[0]) for x in c.fetchall()]
names.remove(dBase)
elif DbModule.fileWildcard:
import os.path, glob
names = glob.glob(os.path.join(dirName, DbModule.fileWildcard))
else:
names = []
return names
def GetDbNames(user='******',password='******',dirName='.',dBase='::template1',cn=None):
""" returns a list of databases that are available
**Arguments**
- user: the username for DB access
- password: the password to be used for DB access
**Returns**
- a list of db names (strings)
"""
if DbModule.getDbSql:
if not cn:
try:
cn = DbModule.connect(dBase,user,password)
except:
print('Problems opening database: %s'%(dBase))
return []
c = cn.cursor()
c.execute(DbModule.getDbSql)
if RDConfig.usePgSQL:
names = ['::'+str(x[0]) for x in c.fetchall()]
else:
names = ['::'+str(x[0]) for x in c.fetchall()]
names.remove(dBase)
elif DbModule.fileWildcard:
import os.path,glob
names = glob.glob(os.path.join(dirName,DbModule.fileWildcard))
else:
names = []
return names
def GetColumnNamesAndTypes(dBase, table, user='******', password='******', join='', what='*',
cn=None):
""" gets a list of columns available in a DB table along with their types
**Arguments**
- dBase: the name of the DB file to be used
- table: the name of the table to query
- user: the username for DB access
- password: the password to be used for DB access
- join: an optional join clause (omit the verb 'join')
- what: an optional clause indicating what to select
**Returns**
- a list of 2-tuples containing:
1) column name
2) column type
"""
if not cn:
cn = DbModule.connect(dBase, user, password)
c = cn.cursor()
cmd = 'select %s from %s' % (what, table)
if join:
cmd += ' join %s' % (join)
c.execute(cmd)
return GetColumnInfoFromCursor(c)
def GetColumns(dBase,table,fieldString,user='******',password='******',
join='',cn=None):
""" gets a set of data from a table
**Arguments**
- dBase: database name
- table: table name
- fieldString: a string with the names of the fields to be extracted,
this should be a comma delimited list
- user and password:
- join: a join clause (omit the verb 'join')
**Returns**
- a list of the data
"""
if not cn:
cn = DbModule.connect(dBase,user,password)
c = cn.cursor()
cmd = 'select %s from %s'%(fieldString,table)
if join:
if join.strip().find('join') != 0:
join = 'join %s'%(join)
cmd +=' ' + join
c.execute(cmd)
return c.fetchall()
def GetAtomicData(atomDict, descriptorsDesired, dBase=_atomDbName, table='atomic_data', where='',
user='******', password='******', includeElCounts=0):
""" pulls atomic data from a database
**Arguments**
- atomDict: the dictionary to populate
- descriptorsDesired: the descriptors to pull for each atom
- dBase: the DB to use
- table: the DB table to use
- where: the SQL where clause
- user: the user name to use with the DB
- password: the password to use with the DB
- includeElCounts: if nonzero, valence electron count fields are added to
the _atomDict_
"""
extraFields = ['NVAL', 'NVAL_NO_FULL_F', 'NVAL_NO_FULL_D', 'NVAL_NO_FULL']
from rdkit.Dbase import DbModule
cn = DbModule.connect(dBase, user, password)
c = cn.cursor()
descriptorsDesired = [s.upper() for s in descriptorsDesired]
if 'NAME' not in descriptorsDesired:
descriptorsDesired.append('NAME')
if includeElCounts and 'CONFIG' not in descriptorsDesired:
descriptorsDesired.append('CONFIG')
for field in extraFields:
if field in descriptorsDesired:
descriptorsDesired.remove(field)
toPull = ','.join(descriptorsDesired)
command = 'select %s from atomic_data %s' % (toPull, where)
try:
c.execute(command)
except Exception:
print('Problems executing command:', command)
return
res = c.fetchall()
for atom in res:
tDict = {}
for i in range(len(descriptorsDesired)):
desc = descriptorsDesired[i]
val = atom[i]
tDict[desc] = val
name = tDict['NAME']
atomDict[name] = tDict
if includeElCounts:
config = atomDict[name]['CONFIG']
atomDict[name]['NVAL'] = ConfigToNumElectrons(config)
atomDict[name]['NVAL_NO_FULL_F'] = ConfigToNumElectrons(config, ignoreFullF=1)
atomDict[name]['NVAL_NO_FULL_D'] = ConfigToNumElectrons(config, ignoreFullD=1)
atomDict[name]['NVAL_NO_FULL'] = ConfigToNumElectrons(
config, ignoreFullF=1, ignoreFullD=1)
def GetAtomicData(atomDict, descriptorsDesired, dBase=_atomDbName, table='atomic_data', where='',
user='******', password='******', includeElCounts=0):
""" pulls atomic data from a database
**Arguments**
- atomDict: the dictionary to populate
- descriptorsDesired: the descriptors to pull for each atom
- dBase: the DB to use
- table: the DB table to use
- where: the SQL where clause
- user: the user name to use with the DB
- password: the password to use with the DB
- includeElCounts: if nonzero, valence electron count fields are added to
the _atomDict_
"""
extraFields = ['NVAL', 'NVAL_NO_FULL_F', 'NVAL_NO_FULL_D', 'NVAL_NO_FULL']
from rdkit.Dbase import DbModule
cn = DbModule.connect(dBase, user, password)
c = cn.cursor()
descriptorsDesired = [s.upper() for s in descriptorsDesired]
if 'NAME' not in descriptorsDesired:
descriptorsDesired.append('NAME')
if includeElCounts and 'CONFIG' not in descriptorsDesired:
descriptorsDesired.append('CONFIG')
for field in extraFields:
if field in descriptorsDesired:
descriptorsDesired.remove(field)
toPull = ','.join(descriptorsDesired)
command = 'select %s from atomic_data %s' % (toPull, where)
try:
c.execute(command)
except Exception:
print('Problems executing command:', command)
return
res = c.fetchall()
for atom in res:
tDict = {}
for i in xrange(len(descriptorsDesired)):
desc = descriptorsDesired[i]
val = atom[i]
tDict[desc] = val
name = tDict['NAME']
atomDict[name] = tDict
if includeElCounts:
config = atomDict[name]['CONFIG']
atomDict[name]['NVAL'] = ConfigToNumElectrons(config)
atomDict[name]['NVAL_NO_FULL_F'] = ConfigToNumElectrons(config, ignoreFullF=1)
atomDict[name]['NVAL_NO_FULL_D'] = ConfigToNumElectrons(config, ignoreFullD=1)
atomDict[name]['NVAL_NO_FULL'] = ConfigToNumElectrons(config, ignoreFullF=1, ignoreFullD=1)
def DatabaseToText(dBase,table,fields='*',join='',where='',
user='******',password='******',delim=',',cn=None):
""" Pulls the contents of a database and makes a deliminted text file from them
**Arguments**
- dBase: the name of the DB file to be used
- table: the name of the table to query
- fields: the fields to select with the SQL query
- join: the join clause of the SQL query
(e.g. 'join foo on foo.bar=base.bar')
- where: the where clause of the SQL query
(e.g. 'where foo = 2' or 'where bar > 17.6')
- user: the username for DB access
- password: the password to be used for DB access
**Returns**
- the CSV data (as text)
"""
if len(where) and where.strip().find('where')==-1:
where = 'where %s'%(where)
if len(join) and join.strip().find('join') == -1:
join = 'join %s'%(join)
sqlCommand = 'select %s from %s %s %s'%(fields,table,join,where)
if not cn:
cn = DbModule.connect(dBase,user,password)
c = cn.cursor()
c.execute(sqlCommand)
headers = []
colsToTake = []
# the description field of the cursor carries around info about the columns
# of the table
for i in range(len(c.description)):
item = c.description[i]
if item[1] not in DbInfo.sqlBinTypes:
colsToTake.append(i)
headers.append(item[0])
lines = []
lines.append(delim.join(headers))
# grab the data
results = c.fetchall()
for res in results:
d = _take(res,colsToTake)
lines.append(delim.join(map(str,d)))
return '\n'.join(lines)
def GetCursor(self):
""" returns a cursor for direct manipulation of the DB
only one cursor is available
"""
if self.cursor is not None:
return self.cursor
self.cn = DbModule.connect(self.dbName, self.user, self.password)
self.cursor = self.cn.cursor()
return self.cursor
def _AddDataToDb(dBase,table,user,password,colDefs,colTypes,data,
nullMarker=None,blockSize=100,cn=None):
""" *For Internal Use*
(drops and) creates a table and then inserts the values
"""
if not cn:
cn = DbModule.connect(dBase,user,password)
c = cn.cursor()
try:
c.execute('drop table %s'%(table))
except:
print('cannot drop table %s'%(table))
try:
sqlStr = 'create table %s (%s)'%(table,colDefs)
c.execute(sqlStr)
except:
print('create table failed: ', sqlStr)
print('here is the exception:')
import traceback
traceback.print_exc()
return
cn.commit()
c = None
block = []
entryTxt = [DbModule.placeHolder]*len(data[0])
dStr = ','.join(entryTxt)
sqlStr = 'insert into %s values (%s)'%(table,dStr)
nDone = 0
for row in data:
entries = [None]*len(row)
for col in xrange(len(row)):
if row[col] is not None and \
(nullMarker is None or row[col] != nullMarker):
if colTypes[col][0] == types.FloatType:
entries[col] = float(row[col])
elif colTypes[col][0] == types.IntType:
entries[col] = int(row[col])
else:
entries[col] = str(row[col])
else:
entries[col] = None
block.append(tuple(entries))
if len(block)>=blockSize:
nDone += _insertBlock(cn,sqlStr,block)
if not hasattr(cn,'autocommit') or not cn.autocommit:
cn.commit()
block = []
if len(block):
nDone += _insertBlock(cn,sqlStr,block)
if not hasattr(cn,'autocommit') or not cn.autocommit:
cn.commit()
def GetCursor(self):
""" returns a cursor for direct manipulation of the DB
only one cursor is available
"""
if self.cursor is not None:
return self.cursor
self.cn = DbModule.connect(self.dbName,self.user,self.password)
self.cursor = self.cn.cursor()
return self.cursor
def GetColumnNames(dBase,
table,
user='******',
password='******',
join='',
what='*',
cn=None):
""" gets a list of columns available in a DB table
**Arguments**
- dBase: the name of the DB file to be used
- table: the name of the table to query
- user: the username for DB access
- password: the password to be used for DB access
- join: an optional join clause (omit the verb 'join')
- what: an optional clause indicating what to select
**Returns**
- a list of column names
"""
if not cn:
cn = DbModule.connect(dBase, user, password)
c = cn.cursor()
cmd = 'select %s from %s' % (what, table)
if join:
if join.strip().find('join') != 0:
join = 'join %s' % (join)
cmd += ' ' + join
c.execute(cmd)
c.fetchone()
desc = c.description
res = [str(x[0]) for x in desc]
return res
def GetTableNames(dBase,
user='******',
password='******',
includeViews=0,
cn=None):
""" returns a list of tables available in a database
**Arguments**
- dBase: the name of the DB file to be used
- user: the username for DB access
- password: the password to be used for DB access
- includeViews: if this is non-null, the views in the db will
also be returned
**Returns**
- a list of table names (strings)
"""
if not cn:
try:
cn = DbModule.connect(dBase, user, password)
except Exception:
print('Problems opening database: %s' % (dBase))
return []
c = cn.cursor()
if not includeViews:
comm = DbModule.getTablesSql
else:
comm = DbModule.getTablesAndViewsSql
c.execute(comm)
names = [str(x[0]).upper() for x in c.fetchall()]
if RDConfig.usePgSQL and 'PG_LOGDIR_LS' in names:
names.remove('PG_LOGDIR_LS')
return names
def GetColumnNamesAndTypes(dBase,
table,
user='******',
password='******',
join='',
what='*',
cn=None):
""" gets a list of columns available in a DB table along with their types
**Arguments**
- dBase: the name of the DB file to be used
- table: the name of the table to query
- user: the username for DB access
- password: the password to be used for DB access
- join: an optional join clause (omit the verb 'join')
- what: an optional clause indicating what to select
**Returns**
- a list of 2-tuples containing:
1) column name
2) column type
"""
if not cn:
cn = DbModule.connect(dBase, user, password)
c = cn.cursor()
cmd = 'select %s from %s' % (what, table)
if join:
cmd += ' join %s' % (join)
c.execute(cmd)
return GetColumnInfoFromCursor(c)
def GetData(dBase,table,fieldString='*',whereString='',user='******',password='******',
removeDups=-1,join='',forceList=0,transform=None,randomAccess=1,extras=None,cn=None):
""" a more flexible method to get a set of data from a table
**Arguments**
- fields: a string with the names of the fields to be extracted,
this should be a comma delimited list
- where: the SQL where clause to be used with the DB query
- removeDups indicates the column which should be used to screen
out duplicates. Only the first appearance of a duplicate will
be left in the dataset.
**Returns**
- a list of the data
**Notes**
- EFF: this isn't particularly efficient
"""
if not cn:
cn = DbModule.connect(dBase,user,password)
c = cn.cursor()
cmd = 'select %s from %s'%(fieldString,table)
if join:
if join.strip().find('join') != 0:
join = 'join %s'%(join)
cmd += ' ' + join
if whereString:
if whereString.strip().find('where')!=0:
whereString = 'where %s'%(whereString)
cmd += ' ' + whereString
if forceList:
try:
if not extras:
c.execute(cmd)
else:
c.execute(cmd,extras)
except:
sys.stderr.write('the command "%s" generated errors:\n'%(cmd))
import traceback
traceback.print_exc()
return None
if transform is not None:
raise ValueError('forceList and transform arguments are not compatible')
if not randomAccess:
raise ValueError('when forceList is set, randomAccess must also be used')
data = c.fetchall()
if removeDups>0:
seen = []
for entry in data[:]:
if entry[removeDups] in seen:
data.remove(entry)
else:
seen.append(entry[removeDups])
else:
if randomAccess:
klass = RandomAccessDbResultSet
else:
klass = DbResultSet
data = klass(c,cn,cmd,removeDups=removeDups,transform=transform,extras=extras)
return data
