pythonpython-3.xms-accesspyodbcpypyodbc

Passing parameters to a saved query in MS Access


Ok, I tried every possible situation from SO, and none of it did the job.

I have a problem calling a saved query which requires 4 parameters to be passed (talking about access database), and I have a simple code :

params = ()
sql_saldo = "{CALL KUP_BalansKupDosp(204701,0,#3/5/2020#,0)}"
saldo = cursor.execute(sql_saldo)
for row in saldo.fetchall():
    print(row)

This works!

But since I need those parameters to be dynamic, when I try :

params = ('204701','0,#3/5/2020#','0')
sql_saldo = "{CALL KUP_BalansKupDosp(?,?,?,?)}"
saldo = cursor.execute(sql_saldo, params)
for row in saldo.fetchall():
    print(row)

I get an error Data type mismatch in criteria expression

This is original parameter definition PARAMETERS InKupID Long, InSekID Long, InDatum DateTime, InToler Currency = 1; So it takes, string, string, datetime, and int i suppose. So when I try with :

today_row = date.today()
today = today_row.strftime("%d/%m/%Y")
params = (204701,0,today,0)
sql_saldo = "{CALL KUP_BalansKupDosp(?,?,?,?)}"
saldo = cursor.execute(sql_saldo, params)
for row in saldo.fetchall():
    print(row)

I also get the same error, i even tried with every possible combo and I just cant figure it out.

PS. My connection is working great since I have 10 more queries besides this one.

Someone had a similar problem?


Solution

  • I manage to solve it with this :

    params = (204701, 0, today, 0)
    sql_saldo = "{CALL KUP_BalansKupDosp(%s,%s,%s,%s)}" % params
    saldo = cursor.execute(sql_saldo)
    for row in saldo.fetchall():
        print(row)