Welcome, guest | Sign In | My Account | Store | Cart

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
  1
  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

1 comment

Robert Brewer 20 years, 7 months ago  # | flag

Nice. Here's a cleaner, dispatch version of __init__. You can replace that huge if...if...if.. block with:

def __init__(self, adoField):

    reg_types = {win32com.client.constants.adInteger: 'integer',
                 win32com.client.constants.adCurrency: 'money',
                 win32com.client.constants.adDate: 'timestamp',
                 win32com.client.constants.adBoolean: 'boolean',
                 win32com.client.constants.adDBTimeStamp: 'timestamp',
                 win32com.client.constants.adVarWChar: 'varchar(%d)' % adoField.DefinedSize,
                 win32com.client.constants.adLongVarWChar: 'text',
                }

    try:
        self.sqlString = reg_types[adoField.Type]
    except KeyError: raise "unrecognized ado field type %d" % adoField.Type
    self.name = adoField.Name
    self.adoType = adoField.Type
    self.adoLen = adoField.DefinedSize

Next step would be to set the reg_types dicitonary outside of __init__(), so it only has to be created once.