Get data from tables in Firebird database

Question

I'm trying to get the data from the tables in a Firebird database. So far, I've managed to make a successful connection and to get the table names. Following some other posts here, I've managed to "select" (whatever that means and implies) with cursor function the database I want to read, but I haven't been able to retrieve the information and use it in Pandas, which is what I want.

This is my code, I hope you help me with this is issue:

#Connection is made
con=fdb.connect(dsn=r'C:\table.FDB',user='SYSD', password='key')

#I don't know what this is for, but it helps me get the table names and somehow I think I'm getting closer.

schema1=fdb.schema.Schema()
schema1.bind(con)
for i in range(len(schema1.tables)):
    print(schema1.tables[i].name)
    
#This I got it from this post that said it would retrieve the data, but I just don't know how to get it: https://stackoverflow.com/questions/64826318/extract-data-from-a-firebird-database-with-python-fdb-module 

cur1=con.cursor()
cur1.execute('select * from "INVE04"')

#I get the following:
<fdb.fbcore.Cursor at 0x2b213a0fe20>

What should I do next to read the data? I'm not familiar with Firebird, so consulting the documentation I couldn’t find any method or way to read/extract/consume the data in each table. Am I going the right way here?

Answer 1

The Firebird FDB driver implements the Python DB API 2.0 (PEP-249). How you retrieve rows is standardized in this API, and is also documented in the FDB documentation. Specifically, Executing SQL Statements shows 3 different ways to process query results:

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. Using built-in support for iteration protocol to 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)
# or alternatively you can take an advantage of cur.execute returning self.
for (name, year_released) in cur.execute(SELECT):
    print '%s has been publicly available since %d.' % (name, year_released)

# 2. Equivalently using fetchall():
# This is potentially dangerous if result set is huge, as the whole result set is first materialized
# as list and then used for iteration.
cur.execute(SELECT)
for row in cur.fetchall():
    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

In short, you can iterate over the cursor itself (sequence iteration, option 1), you can fetch all rows into a list using fetchall() (option 2), or you can use itermap() (mapping-iteration, option 3).

There are additional options (like repeatedly calling fetchone(), or fetching in batches using fetchmany()).

Answer 2

I used the Firebird ODBC driver to make queries. In case of Python >=3.8 the other official python package for FB is working well

$ pip install firebird-driver

This driver uses new Firebird OO API provided by fbclient library (.so/.dll) and works like mentioned by mark by iterating over cur.fetchall() and also like rodrigo suggests except that shortcut functions for pandas.DataFrame might even be more convenient and can reduce code like so:

import pandas as pd
from firebird.driver import connect, driver_config

driver_config.fb_client_library.value = ODBC_LIB_PATH
con = connect(VARIO_DB, user='ODBC', password='odbc', role='VF_USER_RO')

# IMPORTANT: python str uses "" while sql query uses ''
sql = "SELECT some_col FROM some_table WHERE another_col = 'interesting'"

df = pd.read_sql(sql, con)
print(df.head())

Answer 3

I was facing the same problem recent. I think this code can help you.

import fdb
import pandas as pd

con = fdb.connect(dsn='/temp/test.db', user='sysdba', password='masterkey')

cur = con.cursor()
result = cur.execute(" SELECT = select name, year_released from languages order by year_released")

result = result.fatchall()

df = pd.DataFrame(result)