13.13.3 Cursor Objects
A Cursor instance has the following attributes and methods:
-
Executes a SQL statement. The SQL statement may be parametrized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).
This example shows how to use parameters with qmark style:
import sqlite3 con = sqlite3.connect("mydb") cur = con.cursor() who = "Yeltsin" age = 72 cur.execute("select name_last, age from people where name_last=? and age=?", (who, age)) print cur.fetchone()
This example shows how to use the named style:
import sqlite3 con = sqlite3.connect("mydb") cur = con.cursor() who = "Yeltsin" age = 72 cur.execute("select name_last, age from people where name_last=:who and age=:age", {"who": who, "age": age}) print cur.fetchone()
execute() will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript() if you want to execute multiple SQL statements with one call.
-
Executes a SQL command against all parameter sequences or mappings found in the
sequence sql. The sqlite3 module also allows
using an iterator yielding parameters instead of a sequence.
import sqlite3 class IterChars: def __init__(self): self.count = ord('a') def __iter__(self): return self def next(self): if self.count > ord('z'): raise StopIteration self.count += 1 return (chr(self.count - 1),) # this is a 1-tuple con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("create table characters(c)") theIter = IterChars() cur.executemany("insert into characters(c) values (?)", theIter) cur.execute("select c from characters") print cur.fetchall()
Here's a shorter example using a generator:
import sqlite3 def char_generator(): import string for c in string.letters[:26]: yield (c,) con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("create table characters(c)") cur.executemany("insert into characters(c) values (?)", char_generator()) cur.execute("select c from characters") print cur.fetchall()
-
This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter.
sql_script can be a bytestring or a Unicode string.
Example:
import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() cur.executescript(""" create table person( firstname, lastname, age ); create table book( title, author, published ); insert into book(title, author, published) values ( 'Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', 1987 ); """)
-
Although the Cursor class of the sqlite3 module implements this
attribute, the database engine's own support for the determination of "rows
affected"/"rows selected" is quirky.
For
SELECT
statements, rowcount is always None because we cannot determine the number of rows a query produced until all rows were fetched.For
DELETE
statements, SQLite reports rowcount as 0 if you make aDELETE FROM table
without any condition.For executemany statements, the number of modifications are summed up into rowcount.
As required by the Python DB API Spec, the rowcount attribute "is -1 in case no executeXX() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface".
See About this document... for information on suggesting changes.