CODEWITHSUNDEEP
pythonsqlms-accessinsert
Sarah-Ann
Sarah-Ann

Reputation: 149

INSERT INTO SELECT syntax error Python ODBC with Microsoft Access

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?

Upvotes: 1

Views: 1425

Answers (2)

HansUp
HansUp

Reputation: 97131

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

Upvotes: 1

Gerrat
Gerrat

Reputation: 29730

Your SQL needs an "INTO" clause:

SQL = "INSERT INTO UniqueData SELECT * FROM Rawdata WHERE PregnancyCode =%s"

Upvotes: 0

Related Questions