Update statement not working on sqlite3 using python

Question

I am slowly losing my mind. I cannot for the life of me figure out why I cannot get this update statement to work when I am using string from input(). This first section works fine:

table_name = 'ARCHIVE_BOXES'
column_name = 'status'
id_column = 'id'

c.execute("UPDATE {tn} SET {cn}=('reading') WHERE {idf}=(566)".
        format(tn=table_name, cn=column_name, idf=id_column))

conn.commit()

But this doesn't and I've been hitting my head against the wall for two days:

table_name = 'ARCHIVE_BOXES'
column_name = 'status'
id_column = 'id'
pk = ''
while not pk:
    pk = input()
    pk = ("(" + pk + ")")
    primary_key = pk

ns = ''
while not ns:
    ns = input()
    new_status = ("\'" + ns + "\'")
    new_status = ("(" + new_status + ")")

    print("new_status: " + new_status)

c.execute("""
    UPDATE
        {tn}
    SET
        {cn} = ?
    WHERE
        {idf} = ?""".
        format(tn=table_name, cn=column_name, idf=id_column),
        (new_status, primary_key))

conn.commit()

I've also tried doing the update statement this way w the same result:

c.execute("UPDATE {tn} SET {cn}={ns} WHERE {idf}={idn}".
           format(tn=table_name, cn=column_name2, idf=id_column,
           ns=new_status, idn=primary_key))

Much like this question, it is not throwing an error- just ignoring it entirely.

If you try simply printing "UPDATE {tn} SET {cn}={ns} WHERE {idf}={idn}".format(tn=table_name, cn=column_name2, idf=id_column, ns=new_status, idn=primary_key), does it look like it should? — sjw
– sjw, Commented Sep 19, 2018 at 10:45

Duncan · Accepted Answer · 2018-09-19 10:55:18Z

1

Your primary key is probably an integer, but in the main code (not the snippet) you're passing it a string with parentheses as part of the string. So in the main code the query will contain WHERE id_column='(566)' and simply won't match any records.

Try:

while not pk:
    pk = input()
    primary_key = int(pk)

and see if that works any better.

The ? placeholder in the SQL query will automatically put quotes round strings but not around numbers so you need to be sure the parameters you pass have the correct type.

answered Sep 19, 2018 at 10:55

Duncan

96.4k15 gold badges129 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cestMoiBaliBalo · Accepted Answer · 2018-09-19 10:56:03Z

Why put both new status and primary key values between apostrophes and parentheses?

Just get the inputs and gather them together into a tuple defined into the execute statement.

If conn is you connection object you can also use its execute method directly without defining a cursor object.

You can also use a context manager to commit your changes only if no exception hasn't been raised (see very interesting documentation sqlite3 python module). You haven't to declare the commit statement explicitly.

primary_key = ''
while not primary_key:
    primary_key = input()
    # or primary_key = int(input())

new_status= ''
while not new_status:
    new_status = input()

conn = sqlite3.connect("your_database")
with conn:
    conn.execute("UPDATE {tn} SET {cn}=? WHERE {idf}=?".format(tn=table_name, cn=column_name, idf=id_column), (new_status, primary_key))

Does that work better?

Collectives™ on Stack Overflow

Update statement not working on sqlite3 using python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related