Firebird for SQLAlchemy

Common errors

• sqlalchemy.exc.ArgumentError: Could not determine dialect for 'firebird+fdb'

  cause The Python DBAPI driver specified in the connection string (e.g., 'fdb') is not installed or not accessible.
  fix Install the missing driver: `pip install fdb` (or `pip install firebird-driver` if using `firebird+firebird://`).

• (fdb.fbcore.DatabaseError) ('Error while connecting to database:\n- SQLCODE: -551\n- no permission for read-write access - database /path/to/my.fdb'

  cause The Firebird server process (or the user running an embedded Firebird) lacks sufficient read/write permissions for the specified database file or its directory.
  fix Grant appropriate filesystem permissions to the Firebird database file and its containing directory for the user account under which the Firebird server or your application is running.

• (fdb.fbcore.DatabaseError) ('Error while connecting to database:\n- SQLCODE: -902\n- I/O error during "CreateFile (open)" operation for file "D:/.../mydb.FDB"\n- Error while trying to open file\n- The system cannot find the path specified. '

  cause The database file path in the connection string is incorrect, the file does not exist, or the Firebird client library cannot be found/loaded.
  fix Verify the absolute path to your `.fdb` file. Ensure the Firebird client library (`fbclient.dll` or `libfbclient.so`) is in your system's PATH or explicitly passed in the URI (e.g., `?fb_client_library=/path/to/libfbclient.so`). Check for non-latin characters in the path.

• AttributeError: module 'sqlalchemy.ext.baked' has no attribute 'bakery'

  cause You are attempting to use the `baked` query extension, which was removed in SQLAlchemy 2.0. This error occurs if you upgraded SQLAlchemy while `sqlalchemy-firebird` is installed.
  fix Remove all references to `sqlalchemy.ext.baked`. Rewrite queries using the standard SQLAlchemy 2.0 `select()` construct, which has built-in caching.

Warnings

• breaking When migrating an existing SQLAlchemy application from 1.x to 2.0 with `sqlalchemy-firebird`, be aware of major API shifts in SQLAlchemy core. This includes changes to `select()` (positional arguments), removal of the `sqlalchemy.ext.baked` extension, and changes to `Query.get()` (now `Session.get()`), and the consolidation of declarative API imports. While `sqlalchemy-firebird` is 2.0 compatible, your application code may require updates.
  Fix: Refer to the SQLAlchemy 2.0 Major Migration Guide for detailed steps. Update `select()` calls, replace `baked` queries with standard 2.0-style statements, and adjust ORM configuration imports.
• gotcha Firebird databases can exhibit aggressive locking behavior, especially during Data Definition Language (DDL) operations (e.g., `DROP TABLE`). Transactions may hang if other connections are active. This also applies if result sets are not fully consumed, as the underlying connection resources might not be immediately released.
  Fix: Ensure DDL operations are performed when there are no active connections. For queries, always fully consume result sets (e.g., by iterating or calling `.fetchall()`, `.first()`, or ensuring the `ResultProxy` is closed).
• gotcha The appropriate Python DBAPI driver must be installed alongside `sqlalchemy-firebird` and chosen correctly in the connection string. For Python 3.8+ and Firebird 3+, `firebird-driver` is generally preferred (`firebird+firebird://`). For older Python versions (e.g., 3.6/3.7) or Firebird 2.5 servers, `fdb` might be necessary (`firebird+fdb://`).
  Fix: Install the correct driver (`pip install fdb` if `firebird-driver` isn't suitable) and adjust your connection URI to `firebird+fdb://` if using `fdb`.
• gotcha Connecting to a Firebird database, especially for embedded or local server instances, often requires the native Firebird client library (`fbclient.dll` on Windows, `libfbclient.so` on Linux) to be present and discoverable in the system's PATH or explicitly specified in the connection string. Without it, connection attempts will fail.
  Fix: Ensure the Firebird client library is installed and its directory is in your system's PATH environment variable, or add `?fb_client_library=/path/to/fbclient.dll` (or `.so`) to your connection URI.
• gotcha Using non-ASCII or non-latin characters in the database file path can lead to connection errors, particularly when using the `fdb` driver. The driver or underlying Firebird client library might not correctly interpret the encoded path.
  Fix: Store Firebird database files in paths containing only ASCII characters. If unavoidable, investigate filesystem encoding settings or driver-specific path encoding options, though ASCII paths are the most reliable solution.

Install

• pip install sqlalchemy-firebird Basic Installation (Python 3.8+)
• pip install sqlalchemy-firebird fdb For Firebird 2.5 servers or older Python versions

Imports

• create_engine

  from sqlalchemy import create_engine

Quickstart

This quickstart demonstrates how to establish a connection to a Firebird database using SQLAlchemy-Firebird and the `create_engine` function. It utilizes environment variables for database credentials and paths, making it suitable for various deployments. It includes a basic query to retrieve the Firebird engine version to confirm connectivity. The example defaults to `firebird-driver` (via `firebird+firebird://`) but notes the `fdb` alternative.

import os
from sqlalchemy import create_engine, text

# Configure Firebird connection details via environment variables
FB_USER = os.environ.get('FIREBIRD_USER', 'sysdba')
FB_PASSWORD = os.environ.get('FIREBIRD_PASSWORD', 'masterkey')
FB_HOST = os.environ.get('FIREBIRD_HOST', 'localhost')
FB_PORT = os.environ.get('FIREBIRD_PORT', '3050')
FB_DB_PATH = os.environ.get('FIREBIRD_DB_PATH', '/opt/firebird/data/employee.fdb') # Example for Linux
# For Windows, path might be 'C:/path/to/my_project.fdb'
# Optional: Specify client library path if needed, e.g., 'C:/Firebird/Firebird_4_0/bin/fbclient.dll'
FB_CLIENT_LIB = os.environ.get('FIREBIRD_CLIENT_LIBRARY', '')

# Construct the connection URI. Use 'firebird+firebird' for firebird-driver (Python 3.8+)
# or 'firebird+fdb' for fdb driver (older Python/Firebird 2.5).
# The 'firebird' driver is assumed to be firebird-driver for Python 3.8+
db_uri_parts = [
    f"firebird+firebird://{FB_USER}:{FB_PASSWORD}@{FB_HOST}:{FB_PORT}{FB_DB_PATH}"
]
if FB_CLIENT_LIB:
    db_uri_parts.append(f"?fb_client_library={FB_CLIENT_LIB}")

db_uri = "".join(db_uri_parts)

print(f"Attempting to connect to: {db_uri.split(':', 2)[0]}://{FB_USER}:****@{FB_HOST}:{FB_PORT}{FB_DB_PATH}")

try:
    engine = create_engine(db_uri, echo=True)

    with engine.connect() as connection:
        # Example: Fetch Firebird engine version
        result = connection.execute(text("SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION') FROM RDB$DATABASE"))
        version = result.scalar()
        print(f"Successfully connected to Firebird. Engine Version: {version}")
        connection.commit()

except Exception as e:
    print(f"Connection failed: {e}")