Getting Started¶
Installation¶
Firebird-driver is written as pure-Python module (requires Python 3.8+) on top of Firebird client library (fbclient.so/dll) using ctypes. Driver supports Firebird version 3.0 and higher.
Firebird-driver is distributed as setuptools package and the preferred installation method is via pip tool.
Installation from PYPI¶
Run pip:
$ pip install firebird-driver
Quick-start Guide¶
This brief tutorial aims to get the reader started by demonstrating elementary usage of Firebird-driver. It is not a comprehensive Python Database API tutorial, nor is it comprehensive in its coverage of anything else.
The numerous advanced features of Firebird-driver are covered in another section of this documentation, which is not in a tutorial format, though it is replete with examples.
Driver configuration¶
The driver uses configuration built on top of configuration system
provided by firebird-base package. In addition to global settings, the configuration
also includes the definition of connection parameters to Firebird servers and databases.
The default configuration connects to embedded server using direct/local connection method.
To access remote servers and databases (or local ones through remote protocols), it’s
necessary to adjust default configuration, or register
them in configuration manager.
You can manipulate the configuration objects directly, or load configuration from files or
strings (in ini-style
ConfigParser
format).
Connecting to a Database¶
Example 1:
A simple database connection is typically established with code such as this:
from firebird.driver import connect
# Attach to 'employee' database/alias using embedded server connection
con = connect('employee', user='sysdba', password='masterkey')
# Attach to 'employee' database/alias using local server connection
from firebird.driver import driver_config
driver_config.server_defaults.host.value = 'localhost'
con = connect('employee', user='sysdba', password='masterkey')
# Set 'user' and 'password' via configuration
driver_config.server_defaults.user.value = 'SYSDBA'
driver_config.server_defaults.password.value = 'masterkey'
con = connect('employee')
Example 2:
A database connection typically uses specific configuration, and is established with code such as this:
from firebird.driver import connect, driver_config
# Register Firebird server
srv_cfg = """[local]
host = localhost
user = SYSDBA
password = masterkey
"""
driver_config.register_server('local', srv_cfg)
# Register database
db_cfg = """[employee]
server = local
database = employee.fdb
protocol = inet
charset = utf8
"""
driver_config.register_database('employee', db_cfg)
# Attach to 'employee' database
con = connect('employee')
Note
Some parameters like ‘user’ and ‘password’ could be overridden with keyword parameters. Few parameters like ‘crypt_callback’ or ‘no_db_triggers’ could be specified ONLY as keyword arguments.
Creating a Database¶
A database is created using create_database()
function.
Like connect()
, this function uses configuration for specification of
database parameters like page size, sweep interval etc.
Executing SQL Statements¶
For this section, suppose we have a table defined and populated by the following 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 of
the languages
table:
from firebird.driver import connect
con = connect('test.fdb', 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 of fetching a single row at a time from a SELECT
-cursor:
from firebird.driver import connect
con = connect('test.fdb', 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(f'{name} has been publicly available since {year_released}.')
# 2. Equivalently:
cur.execute(SELECT)
for row in cur:
print(f'{row[0]} has been publicly available since {row[1]}.')
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 this example to languages
):
from firebird.driver import connect, DESCRIPTION_NAME, DESCRIPTION_DISPLAY_SIZE
TABLE_NAME = 'languages'
SELECT = f'select * from {TABLE_NAME} order by year_released'
con = connect('test.fdb', user='sysdba', password='masterkey')
cur = con.cursor()
cur.execute(SELECT)
# Print a header.
for fieldDesc in cur.description:
print(fieldDesc[DESCRIPTION_NAME].ljust(fieldDesc[DESCRIPTION_DISPLAY_SIZE]), end='')
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][DESCRIPTION_DISPLAY_SIZE]
print(fieldValue.ljust(fieldMaxWidth), end='')
print() # Finish the row with a newline.
Sample output:
NAME YEAR_RELEASED
------------------------------------------------------------------------------
C 1972
Python 1991
Example 4
Let’s insert more languages:
from firebird.driver import connect
con = connect('test.fdb', 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 dealing with repetitive statements, this is much faster and less error-prone than assembling each SQL statement manually. (You can read more about parameterized SQL statements in the section on Prepared Statements.)
After running Example 4, the table printer from Example 3 would print:
NAME YEAR_RELEASED
------------------------------------------------------------------------------
Lisp 1958
C 1972
Python 1991
Dylan 1995
Calling Stored Procedures¶
Firebird supports stored procedures written in a proprietary procedural SQL language. Firebird stored procedures can have input parameters and/or output parameters. Some databases support input/output parameters, where the same parameter is used for both input and output; Firebird does not support this.
It is important to distinguish between procedures that return a result set and procedures that populate and return their output parameters exactly once. Conceptually, the latter “return their output parameters” like a Python function, whereas the former “yield result 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 is retrieved from a stored procedure by
SELECT
-ing from the procedure, whereas output parameters are retrieved with an
EXECUTE PROCEDURE
statement.
To retrieve a result set from a stored procedure with Firebird-driver, 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 row
con.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 the procedure’s output
parameters as the return value of Cursor.callproc()
. The Python DB API specification
requires the current behavior, however.