class sqlite3.Cursor
A Cursor
instance has the following attributes and methods.
-
execute(sql[, parameters])
-
Executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The
sqlite3
module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).Here’s an example of both styles:
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())
execute()
will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise ansqlite3.Warning
. Useexecutescript()
if you want to execute multiple SQL statements with one call.
-
executemany(sql, seq_of_parameters)
-
Executes an SQL command against all parameter sequences or mappings found in the sequence seq_of_parameters. 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 import string def char_generator(): for c in string.ascii_lowercase: 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())
-
executescript(sql_script)
-
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 an instance of
str
.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 ); """)
-
fetchone()
-
Fetches the next row of a query result set, returning a single sequence, or
None
when no more data is available.
-
fetchmany(size=cursor.arraysize)
-
Fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available.
The number of rows to fetch per call is specified by the size parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.
Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one
fetchmany()
call to the next.
-
fetchall()
-
Fetches all (remaining) rows of a query result, returning a list. Note that the cursor’s arraysize attribute can affect the performance of this operation. An empty list is returned when no rows are available.
-
close()
-
Close the cursor now (rather than whenever
__del__
is called).The cursor will be unusable from this point forward; a
ProgrammingError
exception will be raised if any operation is attempted with the cursor.
-
rowcount
-
Although the
Cursor
class of thesqlite3
module implements this attribute, the database engine’s own support for the determination of “rows affected”/”rows selected” is quirky.For
executemany()
statements, the number of modifications are summed up intorowcount
.As required by the Python DB API Spec, the
rowcount
attribute “is -1 in case noexecuteXX()
has been performed on the cursor or the rowcount of the last operation is not determinable by the interface”. This includesSELECT
statements because we cannot determine the number of rows a query produced until all rows were fetched.With SQLite versions before 3.6.5,
rowcount
is set to 0 if you make aDELETE FROM table
without any condition.
-
lastrowid
-
This read-only attribute provides the rowid of the last modified row. It is only set if you issued an
INSERT
statement using theexecute()
method. For operations other thanINSERT
or whenexecutemany()
is called,lastrowid
is set toNone
.
-
description
-
This read-only attribute provides the column names of the last query. To remain compatible with the Python DB API, it returns a 7-tuple for each column where the last six items of each tuple are
None
.It is set for
SELECT
statements without any matching rows as well.
-
connection
-
This read-only attribute provides the SQLite database
Connection
used by theCursor
object. ACursor
object created by callingcon.cursor()
will have aconnection
attribute that refers to con:>>> con = sqlite3.connect(":memory:") >>> cur = con.cursor() >>> cur.connection == con True
Please login to continue.