Re: Simple mx.ODBC prob seeks simple answer

Discussion in 'Python' started by Steve Holden, Apr 6, 2007.

  1. Steve Holden

    Steve Holden Guest

    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
    Steve Holden, Apr 6, 2007
    #1
    1. Advertising

  2. On Fri, 06 Apr 2007 09:12:55 -0400, Steve Holden <>
    declaimed the following in comp.lang.python:


    > > #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]))


    Seems the above could be sped up quite a bit...

    SQL = "select TISCODE, EXISTSIN into Table_II from %s" % worksheet2
    curse.execute(SQL)

    would replace the "create table", the "select"/"fetchall", and the
    "insert"

    Though it will raise a JET error if Table_II already exists -- so
    you can't use it to combine two or more batches of data (you'd maybe
    have to use a subselect to first joint the two batches -- or, as I just
    noticed from the help file: "insert into Table_II (TISCODE, EXISTSIN)
    select from %s (TISCODE, EXISTSIN)" % other_table_name ).

    No need to pass the data through Python, it all takes place inside
    the JET engine

    >
    > 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:
    >

    As shown above, JET SQL (according to the Access help file) supports
    "select into" (creates destination table and copies data) and "insert
    into" (appends data to existing table) queries. And it does look like
    all source data in the supplied problem come from the same database, so
    no worries about having to specify an "external database" (which the
    syntax seems to support too)


    SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
    FROM source

    INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
    SELECT [source.]field1[, field2[, ...]
    FROM tableexpression

    With only the one SQL statement, and all processing done internal to
    JET, should be quite efficient.
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Apr 6, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rob Meade

    Newbie seeks a little assistance..

    Rob Meade, Apr 24, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    323
    Rob Meade
    Apr 25, 2004
  2. =?Utf-8?B?SWxsdXN0cmlz?=

    newbie seeks CustomValidation advice/problem solving...

    =?Utf-8?B?SWxsdXN0cmlz?=, Sep 8, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    510
    =?Utf-8?B?SWxsdXN0cmlz?=
    Sep 10, 2005
  3. VJL
    Replies:
    0
    Views:
    364
  4. Greg Corradini

    Simple mx.ODBC prob seeks simple answer

    Greg Corradini, Apr 6, 2007, in forum: Python
    Replies:
    0
    Views:
    300
    Greg Corradini
    Apr 6, 2007
  5. Wes Gamble
    Replies:
    1
    Views:
    148
    Gerardo Santana Gómez Garrido
    Apr 5, 2006
Loading...

Share This Page