CODEWITHSUNDEEP
pythondatabasems-accesspyodbc
NewDev
NewDev

Reputation: 71

insert and join in Access database using pyodbc

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

Answers (2)

Gord Thompson
Gord Thompson

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

Gustav
Gustav

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

Related Questions