I'm trying to transfer an access database table to Postgres.
I have created a Postgres table with the same fields as the access
database.
I'm attempting to use the fields read from the access db to set up
parameters for insert into the Postgres db.
I get the error and traceback shown below.
Any suggestions on how to do this? Thanks.
#### code fragment
srcRs = win32com.client.Dispatch(r'ADODB.Recordset')
srcStmt = 'Select * from [%s]' % srcTable
srcRs.Open(srcStmt,srcConn,1,3)
srcRs.MoveFirst()
dstStmt = dstStmt + ')'
dstCmd = win32com.client.Dispatch(r'ADODB.Command')
dstCmd.ActiveConnection = dstConn
dstCmd.Prepared = 1
dstCmd.CommandType = win32com.client.constants.adCmdText
dstCmd.CommandTimeout = 20
dstCmd.CommandText = dstStmtv
dstCmd.Execute(RecordsAffected=pythoncom.Empty,
Parameters=parms,Options=win32com.client.constants.adCmdText)
parms = []
for ff in srcRs.Fields:
parms.append(ff)
dstCmd.Execute(RecordsAffected=pythoncom.Empty,Parameters=parms,Options=win32com.client.constants.adCmdText)
########### Error on Execute()
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "E:\FoodBank\PyTools\adoXferJet2Sql.py", line 103, in ?
stmt,count = xfer.xferTable(table,srcConn,dstConn)
File "E:\FoodBank\PyTools\adoXferJet2Sql.py", line 90, in xferTable
dstCmd.Execute(RecordsAffected=pythoncom.Empty,Parameters=parms,Options=win32com.client.constants.adCmdText)
File
"E:\Python21\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7.py",
line 1746, in Execute
return self._ApplyTypes_(0x5, 1, (9, 0), ((16396, 18), (16396, 17),
(3, 49)), 'Execute',
'{00000556-0000-0010-8000-00AA006D2EA4}',RecordsAffected, Parameters,
Options)
File "E:\Python21\win32com\client\__init__.py", line 343, in
_ApplyTypes_
return self._get_good_object_(apply(self._oleobj_.InvokeTypes,
(dispid, 0, wFlags, retType, argTypes) + args), user, resultCLSID)
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for ODBC Drivers', 'Error while executing the query;\nERROR:
array_in: Need to specify dimension', None, 0, -2147467259), None)
################# Full test Source code
rcsHeader = "$Header:
/home/cvsroot/home/craig/swCommunityMinistry/winPyTools/adoXferJet2Sql.py,v
1.5 2001/12/27 17:21:02 craig Exp $"
# execfile("E:\\FoodBank\\PyTools\\adoXferJet2Sql.py")
import win32com.client
import pythoncom
def sqlName( nameStr ):
ss = nameStr.replace(" ","_")
ss = ss.replace("#","Num")
return ss
class AdoXferJet2Sql:
def __init__(self):
self.jetPath = None
self.jetDsn = None
self.jetConn = None
self.tableList = []
self.odbcDsn = None
self.odbcConn = None
return
def setupJetSrc( self,jetPath ):
self.jetPath = jetPath
self.jetDsn = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=' +
self.jetPath
self.jetConn = win32com.client.Dispatch(r'ADODB.Connection')
self.jetConn.Open(self.jetDsn)
catalog = win32com.client.Dispatch('ADOX.Catalog')
catalog.SetActiveConnection(self.jetConn)
self.tableList = []
for table in catalog.Tables:
if table.Type == 'TABLE':
self.tableList.append(table.Name)
return self.jetConn,self.tableList
def setupOdbcDest( self,odbcDsn ):
self.odbcDsn=odbcDsn
self.odbcConn = win32com.client.Dispatch(r'ADODB.Connection')
self.odbcConn.Open(self.odbcDsn)
return self.odbcConn
def xferTable(self,srcTable,srcConn,dstConn):
srcFields = []
srcRs = win32com.client.Dispatch(r'ADODB.Recordset')
srcStmt = 'Select * from [%s]' % srcTable
srcRs.Open(srcStmt,srcConn,1,3)
if srcRs.EOF:
print "AdoXferJet2Sql.xferTable() no rows in table", srcTable
return
srcRs.MoveFirst()
srcFields = []
for ff in srcRs.Fields:
srcFields.append(ff.Name)
dstFields = []
for ff in srcFields:
dstFields.append(sqlName(ff))
## dstParms = []
## for ff in srcRs.Fields:
## newParm = win32com.client.Dispatch(r'ADODB.Parameter')
## newParm.Direction = win32com.client.constants.adParamOutput
## newParm.Type = ff.Type
## newParm.Value = None
## dstParms.append(newParm)
dstStmt = 'Insert into %s (' % sqlName(srcTable)
ii = 0
for ff in dstFields:
fmt = '%s'
if ii > 0:
fmt = ',' + fmt
dstStmt = dstStmt + (fmt % ff)
ii = ii + 1
dstStmt = dstStmt + ') Values ('
ii = 0
for ff in dstFields:
if ii == 0:
dstStmt = dstStmt + '?'
else:
dstStmt = dstStmt + ',?'
ii = ii + 1
dstStmt = dstStmt + ')'
dstCmd = win32com.client.Dispatch(r'ADODB.Command')
dstCmd.ActiveConnection = dstConn
dstCmd.Prepared = 1
dstCmd.CommandType = win32com.client.constants.adCmdText
dstCmd.CommandTimeout = 20
dstCmd.CommandText = dstStmt
## for parm in dstParms:
## dstCmd.Parameters.Append(parm)
ii = 0
while srcRs.EOF == 0:
parms = []
for ff in srcRs.Fields:
print 'record',ii,'parm',ff.Name,ff.Value
parms.append(ff)
dstCmd.Execute(RecordsAffected=pythoncom.Empty,Parameters=parms,Options=win32com.client.constants.adCmdText)
ii = ii + 1
if 1:
return dstStmt,ii # debug
srcRs.MoveNext()
return dstStmt,ii
if __name__ == '__main__':
print "adoXferJet2Sql.py main here"
xfer = AdoXferJet2Sql()
srcConn,srcTables =
xfer.setupJetSrc("E:\\FoodBank\\Export20011222\\Export20011222.mdb")
dstConn = xfer.setupOdbcDest("FoodBankPG") # ODBC DSN connected to
PostgresDB database
for table in srcTables:
stmt,count = xfer.xferTable(table,srcConn,dstConn)
if 1:
break # debug
--
Craig H. Anderson
_______________________________________________
ActivePython mailing list
[EMAIL PROTECTED]
http://listserv.ActiveState.com/mailman/listinfo/activepython
