SQlite3, Trying to insert values from user input into DB browser db using placeholders with a field that is autoincrement

Question

I'm trying to insert values into my db for the MyAccount table using placeholders. I think the issue lies in the fact that I have a field that is autoincrement and so I am not referencing it. I keep getting different errors, such as database data type mismatch which i think is for the autoincrement field that has no input for it (accountID) and it's just not working and I have tried to look up so many different variations. Any help?

I tried this, I was expecting the values to insert into the db

Please don't post your code as an image, post it as a code block. — AKX, Apr 17 '23 at 10:35

score 1 · Answer 1 · answered Apr 17 '23 at 10:36

1

You'll need to call execute like (e.g.)

cur = con.cursor()
cur.execute(
    'INSERT INTO MyAccount (fullName, username, password, school, phoneNumber, yearGroup) VALUES (?, ?, ?, ?, ?, ?)',
    (input_fullName, input_username, input_password, input_school, input_phoneNumber, input_yearGroup),
)
con.commit()

, that is

the query as a string with ? placeholders (and I've here spelled out the column names so the placeholders' order doesn't matter)
the placeholders' values as a single tuple

This (and the alternative dictionary method) is documented in the sqlite3 module's documentation.

answered Apr 17 '23 at 10:36

AKX

152,115
15
115
172

Thanks, it still does not seem to work, with a tuple – allybobanley Apr 17 '23 at 11:14
"still does not seem to work" – what happens? What doesn't? – AKX Apr 17 '23 at 11:15
Error in saving to database Incorrect number of bindings supplied. The current statement uses 6, and there are 0 supplied – allybobanley Apr 17 '23 at 13:10
Are you _sure_ you're passing that second argument to `cur.execute()` then? – AKX Apr 17 '23 at 13:11

score 0 · Answer 2 · answered Apr 17 '23 at 12:30

As AKX mentioned you have to pass the parameters by tuple or list. Here's a short example for a function:

def qry_exec(db, sql, parameters):
    qry = QSqlQuery (db)
    qry.prepare (sql)
    for value in parameters:
       qry.addBindValue (value)
    if not qry.exec_ ():
        print (qry.lastError ().text ())
        return False
    else:
        return True

You can call it by:

    sql = "INSERT INTO dbtable (field1, field2, field3) VALUES ( ?, ?, ?)"
    ret = QtSqlTools.qry_exec (db, sql, [ data1, data2, data3])

where db is an open database

SQlite3, Trying to insert values from user input into DB browser db using placeholders with a field that is autoincrement

2 Answers2