Greg Corradini wrote:
> Hello all,
> I'm having trouble inserting an SQL selection into a new MS Access table. I
> get a parameter error on my insert statement when I try this (see below for
> code and error msg). I'm not sure if 'insert' or 'update' is the route I
> should be taking.
>
> CODE:
> #Import Pythond Standard Library Modules
> import win32com.client, sys, os, string, copy, glob
> import mx.ODBC.Windows as odbc
>
> # Create the Geoprocessor Object
> gp = win32com.client.Dispatch("esriGeoprocessing.GpDispatch.1")
> gp.overwriteoutput = 1
>
> # Variables
> tempspace = "C:\Documents and Settings\corr1gre\Desktop\Workspace\DBFs &
> Shapefiles\TEST.mdb"
> workspace = string.replace(tempspace,"\\","/")
> worksheet1 = "Mower_I"
> worksheet2 = "Mower_II"
>
> #Conection to Access
> driv = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+workspace
> conn = odbc.DriverConnect(driv)
> curse = conn.cursor()
>
> #Drop Tables if they already exist
> try:
> curse.execute('Drop table Table_I')
> curse.execute('Drop table Table_II')
> curse.execute('Drop table Checker')
> except:
> pass
> #Create a New Tables
> curse.execute('Create table Table_I (TISCODE TEXT(12), EXISTSIN
> TEXT(4),STATUS TEXT(3),NOTES TEXT(50))')
> curse.execute('Create table Table_II(TISCODE TEXT(12), EXISTSIN TEXT(4))')
> curse.execute('Create table Checker (TISCODE TEXT(12), EXISTSIN
> TEXT(4),STATUS TEXT(3),NOTES TEXT(50))')
> conn.commit()
>
> #Upload DBF 1 as a List of Tuples: Returns tuple as ('1021500000','BMP')
> sql = 'SELECT TISCODE,EXISTSIN from '+worksheet2
> curse.execute(sql)
> x = curse.fetchall()
>
> #Put the fetched Data into Table_II
> for i in x:
> curse.execute('Insert into Table_II (TISCODE,EXISTSIN) values
> (%s,%s)'%(i[0],i[1]))
> conn.commit()
> conn.close()
>
> TRACEBACK ERROR MSG:
> Traceback (most recent call last):
> File "C:/Documents and Settings/corr1gre/Desktop/Workspace/Python/ArcGIS
> Python/ExistenceChecker and Update/Access Double Checker/Access_SQL.py",
> line 40, in ?
> curse.execute('Insert into Table_II (TISCODE,EXISTSIN) values
> (%s,%s)'%(i[0],i[1]))
> ProgrammingError: ('07001', -3010, '[Microsoft][ODBC Microsoft Access
> Driver] Too few parameters. Expected 1.', 4612)
That error usually occurs when you use a name that isn't defined int he
database (typically I mistype a column name) - the JET engine then
thinks it's missing a value for some parameter.
In your case it's because you aren't surrounding the string literal
value for TISCODE in your statement with the appropriate '' single
quotes. The engine thus parses it as a name, hence the assumption that a
parameter is missing.
It's actually good that you have made this error, because it allows me
to expound yet again on the dangers of constructing your own SQL
statements instead of using parameterised statements. In the case of
mxODBC the correct parameter mark to use is a question mark. You should
then supply the data to be substituted for the parameter marks as a
tuple argument to the cursor's execute() method.
So what you really need is:
#Put the fetched Data into Table_II
for i in x:
curse.execute("""Insert into Table_II (TISCODE,EXISTSIN)
values (?, ?)""", i)
conn.commit()
conn.close()
A couple of other points:
1. It would actually be better to put the commit() call outside the
loop. This is not only more efficient but it defines the whole set of
changes as a transaction.
2. It would be even more efficient not to use a loop at all but to use
the cursor's executemany() method to perform all inserts with a single
call as follows:
#Put the fetched Data into Table_II
curse.executemany("""Insert into Table_II (TISCODE,EXISTSIN)
values (?, ?)""", x)
conn.commit()
conn.close()
For more on using the DBI API, including something about the risks of
SQL injection vulnerabilities, take a look at the notes from my PyCon
tutorial at
http://www.holdenweb.com/PyConTX2007/dbapi.tgz
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com
--
http://mail.python.org/mailman/listinfo/python-list
