FDB is written as pure-Python module on top of Firebird client library (fbclient.so/dll) using ctypes,so make sure you have Firebird client properly installed before you try to install FDB, otherwise theinstallation will fail. FDB supports Firebird version 2.0 and higher.
FDB is distributed as setuptools package, so you’ll need setuptools orcompatible package installed toinstall FDB properly.
Download the source tarball, uncompress it, then run the install command:
$ curl -O http://pypi.python.org/packages/source/f/fdb/fdb-0.9.1.tar.gz$ tar -xzvf fdb-0.9.1.tar.gz$ cd fdb-0.9.1$ python setup.py install
This brief tutorial aims to get the reader started by demonstratingelementary usage of FDB. It is not a comprehensive PythonDatabase API tutorial, nor is it comprehensive in its coverage ofanything else.
The numerous advanced features of FDB are covered in anothersection of this documentation, which is not in a tutorial format, though itis replete with examples.
Example 1
A database connection is typically established with code such as this:
import fdb# The server is named 'bison'; the database file is at '/temp/test.db'.con = fdb.connect(dsn='bison:/temp/test.db', user='sysdba', password='pass')# Or, equivalently:con = fdb.connect( host='bison', database='/temp/test.db', user='sysdba', password='pass' )
Example 2
Suppose we want to connect to the database in SQL Dialect 1 and specifyingUTF-8 as the character set of the connection:
import fdbcon = fdb.connect( dsn='bison:/temp/test.db', user='sysdba', password='pass', dialect=1, # necessary for all dialect 1 databases charset='UTF8' # specify a character set for the connection )
For this section, suppose we have a table defined and populated by thefollowing SQL code:
create table languages( name varchar(20), year_released integer);insert into languages (name, year_released) values ('C', 1972);insert into languages (name, year_released) values ('Python', 1991);Example 1
This example shows the simplest way to print the entire contents ofthe languages table:
import fdbcon = fdb.connect(dsn='/temp/test.db', user='sysdba', password='masterkey')# Create a Cursor object that operates in the context of Connection con:cur = con.cursor()# Execute the SELECT statement:cur.execute("select * from languages order by year_released")# Retrieve all rows as a sequence and print that sequence:print cur.fetchall()Sample output:
[('C', 1972), ('Python', 1991)]Example 2
Here’s another trivial example that demonstrates various ways offetching a single row at a time from a SELECT-cursor:
import fdbcon = fdb.connect(dsn='/temp/test.db', user='sysdba', password='masterkey')cur = con.cursor()SELECT = "select name, year_released from languages order by year_released"# 1. Iterate over the rows available from the cursor, unpacking the# resulting sequences to yield their elements (name, year_released):cur.execute(SELECT)for (name, year_released) in cur: print '%s has been publicly available since %d.' % (name, year_released)# 2. Equivalently:cur.execute(SELECT)for row in cur: print '%s has been publicly available since %d.' % (row[0], row[1])# 3. Using mapping-iteration rather than sequence-iteration:cur.execute(SELECT)for row in cur.itermap(): print '%(name)s has been publicly available since %(year_released)d.' % row
Sample output:
C has been publicly available since 1972.Python has been publicly available since 1991.C has been publicly available since 1972.Python has been publicly available since 1991.C has been publicly available since 1972.Python has been publicly available since 1991.
Example 3
The following program is a simplistic table printer (applied in thisexample to languages):
import fdbTABLE_NAME = 'languages'SELECT = 'select * from %s order by year_released' % TABLE_NAMEcon = fdb.connect(dsn='/temp/test.db', user='sysdba', password='masterkey')cur = con.cursor()cur.execute(SELECT)# Print a header.for fieldDesc in cur.description: print fieldDesc[fdb.DESCRIPTION_NAME].ljust(fieldDesc[fdb.DESCRIPTION_DISPLAY_SIZE]) ,print # Finish the header with a newline.print '-' * 78# For each row, print the value of each field left-justified within# the maximum possible width of that field.fieldIndices = range(len(cur.description))for row in cur: for fieldIndex in fieldIndices: fieldValue = str(row[fieldIndex]) fieldMaxWidth = cur.description[fieldIndex][fdb.DESCRIPTION_DISPLAY_SIZE] print fieldValue.ljust(fieldMaxWidth) , print # Finish the row with a newline.
Sample output:
NAME YEAR_RELEASED------------------------------------------------------------------------------C 1972Python 1991
Example 4
Let’s insert more languages:
import fdbcon = fdb.connect(dsn='/temp/test.db', user='sysdba', password='masterkey')cur = con.cursor()newLanguages = [ ('Lisp', 1958), ('Dylan', 1995), ]cur.executemany("insert into languages (name, year_released) values (?, ?)", newLanguages )# The changes will not be saved unless the transaction is committed explicitly:con.commit()Note the use of a parameterized SQL statement above. When dealingwith repetitive statements, this is much faster and less error-pronethan assembling each SQL statement manually. (You can read more aboutparameterized SQL statements in the section on Prepared Statements.)
After running Example 4, the table printer from Example 3 would print:
NAME YEAR_RELEASED------------------------------------------------------------------------------Lisp 1958C 1972Python 1991Dylan 1995
Firebird supports stored procedures written in a proprietary proceduralSQL language. Firebird stored procedures can have input parameters and/oroutput parameters. Some databases support input/output parameters,where the same parameter is used for both input and output; Firebird doesnot support this.
It is important to distinguish between procedures that return aresult set and procedures that populate and return their outputparameters exactly once. Conceptually, the latter “return theiroutput parameters” like a Python function, whereas the former “yieldresult rows” like a Python generator.
Firebird’s server-side procedural SQL syntax makes no such distinction,but client-side SQL code (and C API code) must. A result set isretrieved from a stored procedure by SELECT`ing from the procedure,whereas output parameters are retrieved with an `EXECUTE PROCEDUREstatement.
To retrieve a result set from a stored procedure with FDB,use code such as this:
cur.execute("select output1, output2 from the_proc(?, ?)", (input1, input2))# Ordinary fetch code here, such as:for row in cur: ... # process rowcon.commit() # If the procedure had any side effects, commit them.To execute a stored procedure and access its output parameters,use code such as this:
cur.callproc("the_proc", (input1, input2))# If there are output parameters, retrieve them as though they were the# first row of a result set. For example:outputParams = cur.fetchone()con.commit() # If the procedure had any side effects, commit them.This latter is not very elegant; it would be preferable to access theprocedure’s output parameters as the return value ofCursor.callproc(). The Python DB API specification requires thecurrent behavior, however.
