Simple mx.ODBC prob seeks simple answer

[Greg Corradini]

Thanks Steve,
Once again your advice solved the problem

Greg

Steve Holden wrote:
> 
> 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
> 
> 

-- 
View this message in context: http://www.nabble.com/Simple-mx.ODBC-prob-seeks-simple-answer-tf3536661.html#a9873176
Sent from the Python - python-list mailing list archive at Nabble.com.