sqlalchemy-seerdb

A SQLAlchemy dialect for the seerdb driver.

import sqlalchemy as sa

engine = sa.create_engine('oracle+seerdb://user:password@host:1521/?service_name=XE')

Why this exists

The SQL is nothing new — this inherits SQLAlchemy's built-in compiler, DDL and reflection wholesale and supplies only what is specific to this DBAPI. What it adds is reach.

seerdb speaks the wire protocol itself, in pure Python, with no vendor client libraries. The alternatives connect directly only to newer servers and fall back to loading vendor client libraries for anything older. So this dialect covers a range that otherwise needs a native client installed:

Verified against live servers: an 11g instance reports server_version_info == (11, 2, 0, 2, 0) and a current one reports (23, 1, 162, 0, 0), both over the same dialect.

If you are on a modern server and can install a native client, the dialects that ship with SQLAlchemy are the better-trodden path. This one is for the cases they do not reach.

Status

Early. Connections, Core select, DDL, parameter binding and reflection all work against live servers — has_table, get_columns, get_pk_constraint and autoload_with round-trip.

The current target is SQLAlchemy's dialect compliance suite. Progress is tracked under the SQLAlchemy conformance milestone.

Running the tests

The suite is SQLAlchemy's dialect compliance suite, which upstream names as the target for third-party dialects. It is entirely live-database driven — there is no offline mode — so point it at a server:

pytest --dburi "oracle+seerdb://user:password@host:1521/?service_name=XE"

Run it from the repository root. test.cfg has to be found in the working directory: SQLAlchemy's plugin reads it with configparser and does not look at pyproject.toml.

One setup step, and it needs a DBA

The suite expects a second namespace called test_schema, which on this backend is a username, and the test account must be able to create and drop tables inside it. Skipping this does not fail a handful of tests — every test in ComponentReflectionTest errors in setup, because they share a fixture that builds tables there.

The test account cannot create it (ORA-01031), so run this as a DBA once:

CREATE USER test_schema IDENTIFIED BY test_schema;
GRANT CREATE SESSION TO test_schema;
ALTER USER test_schema QUOTA UNLIMITED ON USERS;

-- so the test account can build and drop the fixtures inside that schema
GRANT CREATE ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE,
      UPDATE ANY TABLE, DELETE ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX,
      CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY SEQUENCE, DROP ANY SEQUENCE,
      COMMENT ANY TABLE, ANALYZE ANY TO <your test user>;

Those ANY privileges are broad. They are fine on a throwaway test instance — CI creates the namespace in a container it discards — but narrow them before granting on anything long-lived.

Connect string

Everything after ? is passed through to seerdb.connect, with integers and booleans coerced:

oracle+seerdb://user:password@host:1521/?service_name=XE
oracle+seerdb://user:password@host:1521/?sid=XE&timeout=5000

Licence

MIT. This repository is REUSE compliant.