Simple mx.ODBC prob seeks simple answer

[Steve Holden]

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