A Cursor instance has the following attributes and methods:

execute( sql, [parameters])

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.

executemany( sql, seq_of_parameters)
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()

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 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
       );
       """)

rowcount
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 a DELETE 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”.

Comments are closed.