Utilities are provided to open an ADO connection, list all the tables found on the connection, and generate field definitions of any of the tables. An ADO connection string is used to open the connection. The field definitions are in the form of a PostgreSQL table creation script.
Python, 195 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | """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
|
These utilities were written to support moving a database from an Access97 mdb file to PostgreSQL. I came up with a 3 part approach:
1) Create tables in PostgreSQL equivalent to the Access97 tables. 2) Export the data from Access97 as text files. 3) Import the data in PostgreSQL with the Copy command.
I found the great recipe, Reverse engineer MS Access/Jet databases (by Matt Keranen), but did not know where to get the DAO library. (I have the mdb file, but do not have a Microsoft Office license)
The PythonWin Makepy utility to import these libraries: Microsoft ActiveX Data Objects 2.7 Library Microsoft ActiveX Data Objects Recordset 2.7 Library Microsoft ADO Ext. 2.7 for DDL and Security(2.7)
ADO libraries are included in the MDAC download. See http://www.microsoft.com/data/
For ADO information see http://msdn.microsoft.com/ MSDN Library->Data Access->Microsoft Data Access Components ->SDK Documention->Microsoft ActiveX Data Objects
Other sources of ADO information include: http://www.w3schools.com/ado/default.asp http://www.devguru.com/Technologies/ado/quickref/ado_intro.html
Tags: database
Nice. Here's a cleaner, dispatch version of __init__. You can replace that huge if...if...if.. block with:
Next step would be to set the reg_types dicitonary outside of __init__(), so it only has to be created once.