In this example of interaction with the Firebird database, information from the following pages was used:

The example shows how to create a database, table, insert and select records.

Database creation

import fdb
con = fdb.create_database("create database '127.0.0.1:d:\ib\python.fb' user 'sysdba' password 'masterkey'")

Creating a database connection

con = fdb.connect(host='127.0.0.1', database='d:\ib\python.fb', user='sysdba', password='masterkey', charset='UTF8')

Create a table with an index

cur = con.cursor()
cur.execute("recreate table COUNTRY (ID_COUNTRY int, CNT_NAME varchar(50))")
con.commit()
cur.execute("create unique index COUNTRY_ID on COUNTRY(ID_COUNTRY)")
con.commit()

Insert one row

cur = con.cursor()
cur.execute("insert into COUNTRY (ID_COUNTRY,CNT_NAME) values (?,?)",(1,"Afghanistan"))
con.commit()

Insert a few rows

cur = con.cursor()
data = [
    (2,'Albania'),
    (3,'Algeria'),
    (4,'Andorra')
]
cur.executemany("insert into COUNTRY (ID_COUNTRY,CNT_NAME) values (?,?)",data)
con.commit()

Selection with access by column name

cur = con.cursor()
cur.execute("select ID_COUNTRY,CNT_NAME from COUNTRY")

for row in cur.itermap():
    print(str(row['ID_COUNTRY']).ljust(5),row['CNT_NAME'])

1     Afghanistan
2     Albania
3     Algeria
4     Andorra

column names, dimension and type

cur = con.cursor()
cur.execute("select * from COUNTRY")

for fieldDesc in cur.description:
    print (fieldDesc[fdb.DESCRIPTION_NAME], 
           fieldDesc[fdb.DESCRIPTION_DISPLAY_SIZE],
           fieldDesc[fdb.DESCRIPTION_TYPE_CODE]
          )

ID_COUNTRY 11 <class 'int'>
CNT_NAME 50 <class 'str'>

Displaying a table with any number of columns

cur = con.cursor()
cur.execute("select * from COUNTRY")

# print the table header
for fieldDesc in cur.description:
    print (fieldDesc[fdb.DESCRIPTION_NAME].ljust(fieldDesc[fdb.DESCRIPTION_DISPLAY_SIZE]), end="|")
print("")
print("="*63)
# output table data
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), end="|")    
    print("")

ID_COUNTRY |CNT_NAME                                          |
===============================================================
1          |Afghanistan                                       |
2          |Albania                                           |
3          |Algeria                                           |
4          |Andorra                                           |

Python and Firebird (fdb library)

Leave a Comment Cancel Reply