"""Demonstrate using ADO to get database table definitions.
A PostgreSQL create script is generated.
Author: Craig H. Anderson craigha@attbi.com
"""
__version__ = "$Revision: 1.11 $"
__source__ = "$Header: /home/cvsroot/home/craig/swCommunityMinistry/winPyTools/adoTableDef.py,v 1.11 2001/12/28 22:40:17 craig Exp $"
# Author used this command for testing
# execfile("E:\\FoodBank\\PyTools\\adoTableDef.py")
##--- Example output
##Drop Table Visit_Date;
##Create Table Visit_Date (
## VisitNumber integer
## ,FoodBankID integer
## ,Date timestamp
## ,EstServiceValue money
## ,Adults integer
## ,Children integer
## ,Gasoline money
## ,Tokens integer
##);
import sys
import win32com.client
def sqlName( nameStr ):
"""Make a proper token out of a string with embedded spaces and special characters
' ' -> '_'
'#' -> 'Num'
"""
ss = nameStr.replace(" ","_")
ss = ss.replace("#","Num")
return ss
class InfoAboutADOField:
"""Get information about an ADO field
Information about database types is added manually to __init__()
type constants from the Microsoft ActiveX Data Objects 2.7 Library:
adBoolean =0xb # from enum DataTypeEnum
adCurrency =0x6 # from enum DataTypeEnum
adDate =0x7 # from enum DataTypeEnum
adDBTimeStamp =0x87 # from enum DataTypeEnum
adInteger =0x3 # from enum DataTypeEnum
adLongVarWChar =0xcb # from enum DataTypeEnum
adVarWChar =0xca # from enum DataTypeEnum
The target database is PostgreSql. Changes may be necessary for other
databases.
"""
def __init__(self,adoField):
"""Get name and type information from adoField"""
self.name = None
self.adoType = None
self.adoLen = None
self.sqlString = None # For sql create table
if adoField.Type == win32com.client.constants.adInteger:
self.name = adoField.Name
self.adoType = adoField.Type # 3
self.adoLen = adoField.DefinedSize
self.sqlString = 'integer'
return
if adoField.Type == win32com.client.constants.adCurrency:
self.name = adoField.Name
self.adoType = adoField.Type # 6
self.adoLen = adoField.DefinedSize
self.sqlString = 'money'
return
if adoField.Type == win32com.client.constants.adDate:
self.name = adoField.Name
self.adoType = adoField.Type # 7
self.adoLen = adoField.DefinedSize
self.sqlString = 'timestamp'
return
if adoField.Type == win32com.client.constants.adBoolean:
self.name = adoField.Name
self.adoType = adoField.Type # 11
self.adoLen = adoField.DefinedSize
self.sqlString = 'boolean'
return
if adoField.Type == win32com.client.constants.adDBTimeStamp:
self.name = adoField.Name
self.adoType = adoField.Type # 135
self.adoLen = adoField.DefinedSize
self.sqlString = 'timestamp'
return
if adoField.Type == win32com.client.constants.adVarWChar:
self.name = adoField.Name
self.adoType = adoField.Type # 202
self.adoLen = adoField.DefinedSize
self.sqlString = 'varchar(%d)' % self.adoLen
return
if adoField.Type == win32com.client.constants.adLongVarWChar:
self.name = adoField.Name
self.adoType = adoField.Type # 203
self.adoLen = adoField.DefinedSize
self.sqlString = 'text'
return
raise "unrecognized ado field type %d" % adoField.Type
class TableInfo:
"""Use ADO Recordset to get information about the fields in a table
name - table name
fieldList - list of InfoAboutADOField() for each field
"""
def __init__(self,adoConnection,tableName):
self.name = tableName
self.fieldList = []
stmt = '[%s]' % self.name
adoRecordSet = win32com.client.Dispatch(r'ADODB.Recordset')
adoRecordSet.Open(stmt,adoConnection,
win32com.client.constants.adOpenKeyset,
win32com.client.constants.adLockOptimistic)
for adoField in adoRecordSet.Fields:
self.fieldList.append(InfoAboutADOField(adoField))
return
class AdoTableDef:
"""Demonstrate using ADO to get database table definitions.
"""
def __init__(self):
self.connectionString = None # set by AdoTableDef.open()
self.adoConnection = None # set by AdoTableDef.open()
self.tableNames = [] # set by AdoTableDef.loadTablenames()
self.tableInfo = None # set by AdoTableDef.genTableDef()
return
def genAllTableDefs(self,printObj=sys.stdout):
"""Print sql describing all the tables in self.tableNames[]
Must call AdoTableDef.open() and AdoTableDef.loadTableNames() first
"""
for tbl in self.tableNames:
self.genTableDef(tbl,printObj)
return self.tableInfo # for the last table
def genTableDef(self,tableName,printObj=sys.stdout):
"""Print sql describing table tableName
Must call AdoTableDef.open() and AdoTableDef.loadTableNames() first
"""
self.tableInfo = TableInfo(self.adoConnection,tableName)
print >> printObj,"Drop Table %s;" % sqlName(tableName)
print >> printObj,"Create Table %s (" % sqlName(tableName)
ii = 0
for field in self.tableInfo.fieldList:
fmt = "%s %s"
if ii > 0:
fmt = "," + fmt
fmt = " " + fmt
print >> printObj,(fmt % (sqlName(field.name),field.sqlString))
ii = ii + 1
print >> printObj,");"
return self.tableInfo
def loadTableNames(self):
"""Use ADOX Catalog object to get a list of table Names
on the currently open ADO connection
Must call AdoTableDef.open() first
"""
catalog = win32com.client.Dispatch(r'ADOX.Catalog')
catalog.SetActiveConnection(self.adoConnection)
self.tableNames = []
for adoTable in catalog.Tables:
if adoTable.Type == 'TABLE':
self.tableNames.append(adoTable.Name)
return self.tableNames
def open(self,connectionStr):
"""Open an ADO connection using connectionStr
"""
self.connectionString = connectionStr
self.adoConnection = win32com.client.Dispatch(r'ADODB.Connection')
self.adoConnection.Open(self.connectionString)
return self.adoConnection
if __name__ == '__main__':
"""Sample program used by the author"""
tableDef = AdoTableDef()
# reading from a Jet mdb file
mdbFilePath = "E:\\FoodBank\\Export20011222\\Export20011222.mdb"
connectionStr = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;' % mdbFilePath
tableDef.open(connectionStr) # tableDef.adoConnection holds the open ADO connection
tableDef.loadTableNames() # tableDef.tableNames[] has a list of tables on the open ADO connection
#tableDef.genTableDef(tableDef.tableNames[3]) # Picked a table to see definiton
tableDef.genAllTableDefs(open('C:\\glop.txt','w'))
#tableDef.genTableDef(tableDef.tableNames[3],open('C:\\glop.txt','w')) # try output to file
