Thanks to Python’s portable database API, though, other popular database packages
such as PostgreSQL, MySQL, and Oracle are used almost identically; the initial call to
log in to the database will be all that normally requires different argument values for
scripts that use standard SQL code. Because of this, we can use the SQLite system both
as a prototyping tool in applications development and as an easy way to get started
with the Python SQL database API in this book.
As mentioned earlier, the third edition’s coverage of MySQL had to be
replaced here because the interface used is not yet ported to Python 3.X.
However, the third edition’s MySQL-based examples and overview are
available in the book examples package, in directory C:\...\PP4E\Dbase
\Sql\MySql-2.X, and its Documentation subdirectory. The examples are
in Python 2.X form, but their database-related code is largely version
neutral. Since that code is also largely database neutral, it is probably of
limited value to most readers; the scripts listed in this book should work
on other database packages like MySQL with only trivial changes.Getting started
Regardless of which database system your scripts talk to, the basic SQL interface in
Python is very simple. In fact, it’s hardly object-oriented at all—queries and other da-
tabase commands are sent as strings of SQL. If you know SQL, you already have most
of what you need to use relational databases in Python. That’s good news if you fall
into this category, but adds a prerequisite if you don’t.
This isn’t a book on the SQL language, so we’ll defer to other resources for details on
the commands we’ll be running here (O’Reilly has a suite of books on the topic). In
fact, the databases we’ll use are tiny, and the commands we’ll use are deliberately simple
as SQL goes—you’ll want to extrapolate from what you see here to the more realistic
tasks you face. This section is just a brief look at how to use the Python language in
conjunction with an SQL database.
Whether large or small, though, the Python code needed to process your database turns
out to be surprisingly straightforward. To get started, the first thing we need to do is
open a connection to the database and create a table for storing records:
C:\...\PP4E\Dbase\Sql> python
>>> import sqlite3
>>> conn = sqlite3.connect('dbase1') # use a full path for files elsewhereWe start out by importing the Python SQLite interface here—it’s a standard library
module called sqlite3 to our scripts. Next we create a connection object, passing in
the items our database requires at start-up time—here, the name of the local file where
our databases will be stored. This file is what you’ll want to back up to save your
database. It will create the file if needed, or open its current content; SQLite also accepts
that special string “:memory:” to create a temporary database in memory instead.
SQL Database Interfaces| 1333