Help with sqlite3 sql query statement with named parameters

Python Help
1

I created an sqlite3 database and want to use python to update it when user calls a function. In my code listing below, query#1 works, but query #2,3 doesn’t, as the query statement creates tuples, whilst the interpreter expects a string. How can I get queries with named parameters like #2,3 to work?

def save():
    conn=sqlite3.connect('luckyNumbers.sqlite')
    cur=conn.cursor()

    v1=int(label1.cget('text'))
    v2=int(label2.cget('text'))
    v3=int(label3.cget('text'))
    v4=int(label4.cget('text'))
    v5=int(label5.cget('text'))
    v6=int(label6.cget('text'))
    
    #1) qry = "insert into luckyNumbers (N1,N2,N3,N4,N5,N6,Datetime) values ({},{},{},{},{},{},datetime())".format(v1,v2,v3,v4,v5,v6)
    #2) qry = ("insert into luckyNumbers (N1,N2,N3,N4,N5,N6,Datetime) values(N1=:v1,N2=:v2,N3=:v3,N4=:v4,N5=:v5,N6=:v6,Datetime=datetime())",{"v1":v1,"v2":v2,"v3":v3,"v4":v4,"v5":v5,"v6":v6})
    #3) qry = ("insert into luckyNumbers (N1,N2,N3,N4,N5,N6,Datetime) values (:v1,:v2,:v3,:v4,:v5,:v6,Datetime=datetime())",{"v1":v1,"v2":v2,"v3":v3,"v4":v4,"v5":v5,"v6":v6})
    print(qry)

    try:
        cur.execute(qry)
        conn.commit()
        print('record successfully saved')
    except ValueError as e:
        print('Error creating record:', e)
        conn.rollback
    conn.close()
2

Best to not use format at all,

use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value

Its documented here sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.8.17 documentation

1 Like
3

The 3.8 docs are slightly outdated; see sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.12.0 documentation :slight_smile:

4

Thanks for your reply.

For query#1, once I replace the query with a “?” parametized one as follows, I get the following error msg.

TypeError: execute() argument 1 must be str, not tuple

edited query#1:
qry = “insert into luckyNumbers (N1,N2,N3,N4,N5,N6,Datetime) values (?,?,?,?,?,?,date.today())”,(v1,v2,v3,v4,v5,v6)

my query was printed by the interpreter as follows:
(‘insert into luckyNumbers (N1,N2,N3,N4,N5,N6,Datetime) values (?,?,?,?,?,?,?)’, (44, 32, 56, 58, 29, 18, datetime.date(2023, 9, 17)))

For query #2,3 I was following the DB-API 2.0 by using a parametized query. In fact I followed their given example, reproduced below. But I can’t get it to work.

Any suggestions will be helpful. Thks

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print(cur.fetchone())

con.close()
5

You’re passing a tuple to execute. You need to unpack it first, for example using the * notation:

cur.execute(*qry)

Also, you did not follow my suggestion of looking at the updated docs.

6

Ah…the cur.execute(*qry) did the trick. Many thanks.