Beginning Python (2005)
.pdfAccessing Databases
Try It Out |
Creating a Gadfly Database |
Enter the following script and name the file createdb.py:
import os import gadfly
connection = gadfly.gadfly()
os.mkdir(‘db’)
connection.startup(‘pydb’, ‘db’)
cursor = connection.cursor()
# Create tables. cursor.execute(“”” create table employee
(empid integer, firstname varchar, lastname varchar, dept integer, manager integer, phone varchar)
“””)
cursor.execute(“”” create table department
(departmentid integer, name varchar,
manager integer)
“””)
cursor.execute(“”” create table user
(userid integer, username varchar, employeeid integer)
“””)
# Create indices.
cursor.execute(“””create index userid on user (userid)”””) cursor.execute(“””create index empid on employee (empid)”””) cursor.execute(“””create index deptid on department (departmentid)”””) cursor.execute(“””create index deptfk on employee (dept)”””) cursor.execute(“””create index mgr on employee (manager)”””) cursor.execute(“””create index emplid on user (employeeid)”””) cursor.execute(“””create index deptmgr on department (manager)”””)
connection.commit()
cursor.close()
connection.close()
261
TEAM LinG
Chapter 14
When you run this script, you should see no output unless the script raised an error:
$ python createdb.py
$
How It Works
Gadfly has its own API along with the standard Python DB API. This script uses the Gadfly API, but you’ll notice that this API is very similar to the DB API covered in the following section, “Using the Python Database APIs.” This section briefly describes the Gadfly-specific code in the creatdb.py script.
Among the Gadfly-specific code, you need to create a Connection object using the gadfly function on the gadfly module. For example:
connection = gadfly.gadfly()
connection.startup(‘pydb’, ‘db’)
Note that the gadfly module has the Gadfly-specific API. You need to use the gadfly.dbapi20 module to work with the DB API 2.0.
Once you get a Connection object, you need to start up the database. Pass the name of the database, pydb here, and the path to the directory to use, db in this example. (This script creates the db directory using the standard Python os module.)
From there, the script gets a Cursor object, covered in the section “Working with Cursors.” The Cursor object is used to create three tables and define indexes on these tables.
The script calls the commit method on the Connection to save all the changes to disk.
Gadfly stores all of its data in a directory you define, db in this case. After running the createdb.py script, you should see the following in the db directory, where each .grl file is a gadfly table:
$ ls db
DEPARTMENT.grl EMPLOYEE.grl pydb.gfd USER.grl
You are now ready to start working with the Python database APIs.
Using the Python Database APIs
First, some history about Python and relational databases. Python’s support for relational databases started out with ad hoc solutions, with one solution written to interface with each particular database, such as Oracle. Each database module created its own API, which was highly specific to that database because each database vendor evolved its own API based on its own needs. This is hard to support, because coding for one database and trying to move it to the other gives a programmer severe heartburn, as everything needs to be completely rewritten and retested.
Over the years, though, Python has matured to support a common database, or DB, API, that’s called the DB API. Specific modules enable your Python scripts to communicate with different databases, such as
262 |
TEAM LinG |