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()).