0

I am trying to connect a Firebird database with Python. I already tried it with pyodbc:

import os
import pyodbc

server = '127.0.0.1/3050' 
database = 'Databse-Name' 
username = 'Username' 
password = 'password' 
cnxn = pyodbc.connect('DRIVER={Firebird/InterBase(r) 
driver};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

I get this error:

OperationalError: ('08004', "[08004] [ODBC Firebird Driver]Unable to connect to data source: library 'gds32.dll' failed to load (-904) (SQLDriverConnect); [08004] [ODBC Firebird Driver]Invalid connection string attribute (0)")

I am not sure why he tries to find 'gds32.dll'. In the ODBC-Connection I used this driver C:\Program Files (x86)\assfinet ams.5\BIN\FB30\x64\fbclient.dll

I am using Firebird as a 64-bit version, so I am a bit clueless because of the 32 in 'gds32.dll'.

I am not sure, if it is the right way to try it with pyodbc. I am open for other advice.

Has anyone an idea why it is not working?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Volker Aizpún
  • 1
  • 2

2 Answers2

0

If you are going to use pandas with a database other than SQLite you should be using SQLAlchemy (ref: here). In your case you would use the sqlalchemy-firebird dialect.

Edit re: comment to original answer

Since we are connecting to localhost we can expect that Firebird has been installed and therefore the client tools are available (which is true for a default install). In that case, the following works on a Windows 8.1 test machine:

import pandas as pd
import sqlalchemy as sa

# note the r"" string
engine = sa.create_engine(r"firebird://SYSDBA:masterkey@localhost/C:\ProgramData\assfinet\assfinet ams.5\Individuell 2022\DB0 - Stand 2022_02-10.FDB")

df = pd.read_sql_query("SELECT * FROM my_table", engine)

although a better approach would be to build the connection URL like this

connection_url = sa.engine.URL.create(
    "firebird",
    username="SYSDBA",
    password="masterkey",
    host="localhost",
    database=r"C:\ProgramData\assfinet\assfinet ams.5\Individuell 2022\DB0 - Stand 2022_02-10.FDB",
)
engine = sa.create_engine(connection_url)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Sorry mate, but I din't get it. I open JupyterLab and start: pip install sqlalchemy-firebird firebird://SYSDBA:masterkey@localhost/C:\ProgramData\assfinet\assfinet ams.5\Individuell 2022\DB0 - Stand 2022_02-10.FDB But it said Wrong syntax. Sorry that I am asking that dumb, but need I to import sqlalchemy-firebird first? Because here it also says wrong syntax. Can you help me with the first lines for this database? firebird://SYSDBA:masterkey@localhost/C:\ProgramData\assfinet\assfinet ams.5\Individuell 2022\DB0 - Stand 2022_02-10.FDB – Volker Aizpún May 15 '22 at 19:45
  • This works for me (note the `r""` string): `import sqlalchemy as sa; engine = sa.create_engine(r"firebird://SYSDBA:masterkey@localhost/C:\ProgramData\assfinet\assfinet ams.5\Individuell 2022\DB0 - Stand 2022_02-10.FDB")` – Gord Thompson May 15 '22 at 20:51
  • Thank you Gordon, this string worked pretty well. Can you give me an advice how I can put the parameter into variables? Because the r"" string just write the variables. How I can put them into the r"" string? Thank you – Volker Aizpún May 29 '22 at 08:38
0

The fact the error mentions gds32.dll means it tried to load fbclient.dll, and that didn't work. Then it tried to fallback to gds32.dll. The gds32.dll is supported historically, because Firebird was forked from InterBase 22 years ago, and InterBase used the name gds32.dll for its client library. The 64-bit version is also called gds32.dll.

The problem is that, unless the C:\Program Files (x86)\assfinet ams.5\BIN\FB30\x64\ folder is explicitly on the path, or you configured the CLIENT connection property, that no library is found (or possibly it's 32-bit not 64-bit).

You need a 64-bit fbclient.dll. If that C:\Program Files (x86)\assfinet ams.5\BIN\FB30\x64\ is really a 64-bit Firebird (then C:\Program Files (x86) is the wrong location), you either need to specify the path of the 64-bit client library in the CLIENT connection property, or you can install it with - from a command prompt started as administrator - instclient i f from a Windows 64-bit Firebird installation, or do a client install using a Firebird installer. Alternatively, you can download the zipkit of a Windows 64-bit Firebird and use its fbclient.dll.

You should also consider using one of the Firebird drivers for Python, instead of using ODBC. You can choose from:

  • firebird-driver - uses fbclient.dll
  • FDB - uses fbclient.dll (deprecated and replaced by firebird-driver)
  • firebirdsql (aka pyfirebirdsql) - a pure Python driver (no native dependencies)

Also, I'm not sure if Gordon's advice about using SQLAlchemy is correct, but I'd recommend investigating that (though below the covers SQLAlchemy will probably use FDB or maybe firebird-driver, so you'd still need a proper 64-bit client library to load).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • "below the covers SQLAlchemy will probably use FDB" - Yes, sqlalchemy-firebird does use FDB as its DBAPI driver, but the assumption for all SQLAlchemy dialects is that you need to be able to create a native DBAPI connection before you can use a SQLAlchemy dialect that relies on such a connection. (In this case, client tools. For pyodbc, a compatible ODBC driver. Et cetera.) – Gord Thompson May 18 '22 at 00:23