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?

The 2nd argument to execute() needs to be a sequence. As written it is a single value. Use instead (PregCode,); note trailing comma. The comma makes it a tuple. — mechanical_meat
– mechanical_meat, Commented Jul 13, 2011 at 18:43

HansUp · Accepted Answer · 2011-07-13 19:26:23Z

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.

Gerrat · Accepted Answer · 2011-07-13 18:23:12Z

0

Your SQL needs an "INTO" clause:

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

answered Jul 13, 2011 at 18:23

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

1 Comment

Sarah-Ann Over a year ago

Sorry, that was a typo, had an INTO in there, and I still get an error.

Collectives™ on Stack Overflow

INSERT INTO SELECT syntax error Python ODBC with Microsoft Access

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related