INSERT INTO SELECT syntax error Python ODBC with Microsoft Access
Question: Had a long search through here and the web and can't seem to find any examples
that explain why I am getting a syntax error on the following: import
win32com.client

    
    
    `
            PregCode = recordsetraw.Fields.Item("PregnancyCode").value
            SQL = "INSERT INTO UniqueData SELECT * FROM Rawdata WHERE PregnancyCode =%s"
            params = (PregCode)
            connection.execute(SQL, params)
            print PregCode
        recordsetraw.MoveNext()`
    

I'm getting the following error:

Traceback (most recent call last): File "testdb.py", line 22, in
connection.execute(SQL, params) File "", line 2, in execute
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft JET
D atabase Engine', u"Syntax error in query expression 'PregnancyCode = %s'.",
None , 5003000, -2147217900), None)

I have tried hardcoding in PregnancyCode to see if that would make a
difference but no, same error.

Any ideas what I'm doing wrong?


Answer: My Python is very rusty, and I don't recall what this type of substitution is
called, but I think you're trying to accomplish something similar to this with
your INSERT statement:

    
    
    >>> print "Hello %s" %("World")
    Hello World
    

From your code:

    
    
    SQL = "INSERT INTO UniqueData SELECT * FROM Rawdata WHERE PregnancyCode =%s"
    params = (PregCode)
    connection.execute(SQL, params)
    

Trouble is _connection_ is an ADO Connection, and its [Execute
Method](http://msdn.microsoft.com/en-us/library/ms675023%28VS.85%29.aspx)
won't perform the Pythonic text substitution you want.

I think you should do the text substitution in Python **before** you feed the
INSERT string to `connection.execute` Maybe something like this:

    
    
    SQL = "INSERT INTO UniqueData SELECT * FROM Rawdata WHERE PregnancyCode =%s" %(PregCode)
    connection.execute(SQL)
    

If I didn't get the Python quite right, hopefully it's close enough so you can
see how to fix it.

BTW, your title mentions Python ODBC, but I don't see that your code uses ODBC
at all.

