Integrate Spanner with SQLAlchemy 2 ORM (PostgreSQL dialect)

SQLAlchemy 2 is a Python SQL toolkit and Object Relational Mapper (ORM).

You can use the SQLAlchemy 2 ORM in combination with the standard PostgreSQL psycopg3 driver and PGAdapter.

Set up SQLAlchemy 2 with Spanner PostgreSQL-dialect databases

  1. Ensure that PGAdapter is running on the same machine as the application that is connecting using SQLAlchemy 2 with Spanner.

    For more information, see Start PGAdapter.

  2. Add SQLAlchemy 2 and psycopg3 to your requirements.txt file.

    psycopg~=3.1.8
sqlalchemy~=2.0.1

  3. Specify postgresql+psycopg, localhost and 5432 as the database dialect, driver, server host and port in the SQLAlchemy 2 connection string. psycopg3 requires a username and password in the connection string. PGAdapter ignores these.

    Optionally, specify a different port number if PGAdapter is configured to listen on a port other than the default PostgreSQL port (5432).

    conn_string = "postgresql+psycopg://user:password@localhost:5432/my-database"
engine = create_engine(conn_string)

See the SQLAlchemy 2 with PostgreSQL documentation for more connection options for PostgreSQL.

Use SQLAlchemy 2 with PostgreSQL-dialect databases

For more information about the features and recommendations for SQLAlchemy 2 with PostgreSQL-dialect databases, please consult the reference documentation on GitHub.

What's next