I'm using pysqlite to talk to a SQLite db, and I wonder what is the right way to check whether an UPDATE SQL statement has actually successfully update something in a table.
Is there a variable I can quickly check after the execution for this in pysqlite?
Check the cursor.rowcount attribute; it'll indicate the number of affected rows.
If an UPDATE was not successful the rowcount will be 0:
>>> conn = sqlite3.connect(':memory:')
>>> conn.execute('CREATE TABLE foo (bar, baz)')
<sqlite3.Cursor object at 0x1042ab6c0>
>>> conn.execute('INSERT INTO foo VALUES (1, 2)')
<sqlite3.Cursor object at 0x1042ab730>
>>> cursor = conn.cursor()
>>> cursor.execute('UPDATE foo SET baz=3 WHERE bar=2')
<sqlite3.Cursor object at 0x1042ab6c0>
>>> cursor.rowcount
0
>>> cursor.execute('UPDATE foo SET baz=3 WHERE bar=1')
<sqlite3.Cursor object at 0x1042ab6c0>
>>> cursor.rowcount
1
Of course, if you try to update a table or column that doesn't exist, an exception is thrown instead:
>>> cursor.execute('UPDATE nonesuch SET baz=3 WHERE bar=2')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: nonesuch
>>> cursor.execute('UPDATE foo SET nonesuchcolumn=3 WHERE bar=2')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such column: nonesuchcolumn
I used the sqlite3 library included with Python to demo this; pysqlite2 was added to Python under that name in Python 2.5. The difference is merely the import:
try:
import sqlite3 # included library
except ImportError:
from pysqlite2 import dbapi2 as sqlite3 # use pysqlite2 instead