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.
--
http://mail.python.org/mailman/listinfo/python-list
