python的Firebird驱动：FDB使用说明

水平所限，有很多不准确的地方。

    原文在这里：http://www.firebirdsql.org/file/documentation/drivers_documentation/python/fdb/getting-started.html#quick-start-guide

    firebird 的 python 驱动下载地址：https://pypi.org/project/fdb/#files

安装步骤：

——————————————————————————————————————

 

  FDB 是基于Firebird客户端库文件（fbclient.so/dll），用ctypes写的纯python模块，因此，安装FDB前，要确保firebird客户端正确安装。FDB支持Firebird 2.0及以上版本。

    FDB作为setuptools包分发，所以，需要先安装 setuptools 或者 compatible package。

 

从PYPI安装：

——————————————————————————————————————

运行easy_install 或 pip:

$ pip install fdb

或者：

$ easy_install fdb

 

从源码安装：

——————————————————————————————————————

下载，解压缩，然后运行安装命令

$ 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

 

快速开始：

——————————————————————————————————————

本文仅仅演示一些基本的功能。

数据库连接：

例1：

建立一个典型的数据库连接。 

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'
  )

例2：

假设我们想要用 SQL Dialect 1，以及指定用 UTF-8 编码：

import fdb

con = 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
  )

 

执行sql语句：

For this section, suppose we have a table defined and populated by the following SQL code:

本部分，假设我们已经定义了一张表，并且使用如下的 sql 代码写入数据：

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);

例1：

显示表中的所有内容：

import fdb

con = 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()

例2：

演示了取得每次提取一行数据的多种方法。

import fdb

con = 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

例3：

简化的表格打印。

import fdb

TABLE_NAME = 'languages'
SELECT = 'select * from %s order by year_released' % TABLE_NAME

con = 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.

例4：

插入数据。

import fdb

con = 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()

注意上面的参数化sql语句。当执行重复的语句，这种方式比手工组合sql语句速度更快，更不易出错。参考： Prepared Statements.

运行例4后，再运行例3。结果会是这样：

NAME                 YEAR_RELEASED
------------------------------------------------------------------------------
Lisp                 1958
C                    1972
Python               1991
Dylan                1995

 

调用存储过程：

 

更多内容，请看原文......