Reputation: 71
I have a MS access DB and want to work with it from Python. The aim is to have a table, "units", which includes everything and in order to achieve that I would like to insert information to a table "units_temp" and then join these to tables.
The code is not complete yet but at the moment I am struggling with populating a random ID (purpose is only to not be forced to change ID in the code manually every time I want to try the code before every functionality is in place).
import pyodbc
from random import randint
random_id = randint(0,10000)
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\aaa.bbb\Documents\Python Scripts\DB\Test_db.accdb;')
cursor = conn.cursor()
mySql_insert_query='''INSERT INTO Units (client_id,client_first_name,client_last_name,units_ordered,product_price_per_unit,product_name) VALUES (%s,%s,%s,%s,%s,%s)'''
cursor.execute('''
INSERT INTO Units (client_id,client_first_name,client_last_name,units_ordered,product_price_per_unit,product_name)
VALUES ('124','aa','bb','2','500','phones')
''')
recordTuple = (random_id,'aa','bb','99','900','random')
cursor.execute(mySql_insert_query,recordTuple)
JoinQuery = "SELECT Units.client_id from Units INNER JOIN Units_temp on (Units.client_id=Units_temp.Client_id)"
cursor.execute(JoinQuery)
conn.commit()
I get the following error ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '%s'. (-3100) (SQLPrepare)")
Upvotes: 1
Views: 1269
Reputation: 123839
pyodbc uses ?
as the parameter placeholder, not %s
, so your query string should be
mySql_insert_query='''INSERT INTO Units (client_id,client_first_name, … ) VALUES (?,?, … )'''
then you execute it with
cursor.execute(mySql_insert_query,recordTuple)
as before.
Upvotes: 2
Reputation: 55981
It's the line
mySql_insert_query='''INSERT INTO Units (client_id,client_first_name,client_last_name,units_ordered,product_price_per_unit,product_name) VALUES (%s,%s,%s,%s,%s,%s)'''
You have no values for the %s parameter. Replace these with values as you do in the next line.
Also, I seriously doubt, that these fields should be text and not numbers:
units_ordered,product_price_per_unit
Upvotes: 0