provided in SQLite; in other words, don’t depend on it if you want your database scripts
to work on other database systems.
Parameters to substitute into the SQL statement string are generally passed in as a
sequence (e.g., list or tuple). Notice the module’s paramstyle—this tells us what style
it uses for substitution targets in the statement string. Here, qmark means this module
accepts? for replacement targets. Other database modules might use styles such as
format (meaning a %s target), or numeric indexes or mapping keys; see the DB API for
more details.
To insert multiple rows with a single statement, use the executemany method and a
sequence of row sequences (e.g., a list of lists). This call is like calling execute once for
each row sequence in the argument, and in fact may be implemented as such; database
interfaces may also use database-specific techniques to make this run quicker, though:
>>> curs.executemany('insert into people values (?, ?, ?)',
... [ ('Sue', 'mus', '70000'),
... ('Ann', 'mus', '60000')])>>> curs.rowcount
2We inserted two rows at once in the last statement. It’s hardly any more work to achieve
the same result by inserting one row at a time with a Python loop:
>>> rows = [['Tom', 'mgr', 100000],
... ['Kim', 'adm', 30000],
... ['pat', 'dev', 90000]]>>> for row in rows:
... curs.execute('insert into people values (? , ?, ?)', row)
...>>> conn.commit()Blending Python and SQL like this starts to open up all sorts of interesting possibilities.
Notice the last command; we always need to call the connection’s commit method to
write our changes out to the database. Otherwise, when the connection is closed, our
changes may be lost. In fact, until we call the commit method, none of our inserts may
be visible from other database connections.
Technically, the API suggests that a connection object should automatically call its
rollback method to back out changes that have not yet been committed, when it is
closed (which happens manually when its close method is called, or automatically
when the connection object is about to be garbage collected). For database systems
that don’t support transaction commit and rollback operations, these calls may do
nothing. SQLite implements both the commit and rollback methods; the latter rolls
back any changes made since the last commit.
SQL Database Interfaces| 1335