Skip to main content

sqlalchemy-vertica

Modern Vertica Analytic Database dialect for SQLAlchemy 2.0+ with full support for Async operations, Alembic migrations, and modern Python (3.9 - 3.14+).

https://img.shields.io/pypi/v/sqlalchemy-vertica.svg https://img.shields.io/badge/SQLAlchemy-2.0+-blue.svg https://img.shields.io/badge/python-3.9+-blue.svg https://img.shields.io/badge/Vertica-11--24+-green.svg https://img.shields.io/badge/license-MIT-green.svg https://github.com/lv10/sqlalchemy-vertica/actions/workflows/ci.yml/badge.svg https://img.shields.io/badge/Buy%20Me%20a%20Coffee-Donate-yellow.svg

Features

  • Full SQLAlchemy 2.0+ Architecture: Built on DefaultDialect with query caching (supports_statement_cache = True), 2.0 execution semantics, and parameter-bound reflection.

  • First-Class Async Engine Support: Run queries asynchronously with create_async_engine() and AsyncSession via vertica+vertica_python_async:// without blocking the asyncio event loop.

  • Alembic Migrations: Native VerticaImpl integration with transactional DDL, type synonym resolution, and index no-op handling (since Vertica utilizes projections).

  • Multi-Driver Support: * vertica-python (Synchronous pure-Python DBAPI driver) * vertica-python-async (Asynchronous DBAPI adapter for non-blocking asyncio / FastAPI apps) * pyodbc (ODBC driver) * turbodbc (High-speed ODBC driver for Arrow / NumPy / Pandas data workflows)

  • Rich Vertica Data Types: * Geospatial: GEOMETRY, GEOGRAPHY * Identifiers: native UUID * Large objects: LONG VARCHAR, LONG VARBINARY (up to 32MB) * Complex types: ARRAY, MAP, ROW (Vertica 10+) * Temporal: TIMESTAMPTZ, TIMETZ, INTERVAL

  • Complete Reflection: Automatic introspection of schemas, tables, temp tables, views, view definitions, columns, primary keys, foreign keys, unique constraints, check constraints, table & column comments.

Installation

Install from PyPI with your desired driver extras:

# Pure Python sync driver (recommended for sync applications)
pip install "sqlalchemy-vertica[vertica-python]"

# Pure Python async driver (for AsyncEngine / FastAPI / asyncio)
pip install "sqlalchemy-vertica[asyncio]"

# ODBC drivers
pip install "sqlalchemy-vertica[pyodbc]"
pip install "sqlalchemy-vertica[turbodbc]"

# Alembic migrations support
pip install "sqlalchemy-vertica[alembic]"

# Install all drivers and tools
pip install "sqlalchemy-vertica[all]"

Connection Strings

import sqlalchemy as sa
from sqlalchemy.ext.asyncio import create_async_engine

# 1. Async (for FastAPI / asyncio applications)
async_engine = create_async_engine(
    "vertica+vertica_python_async://user:pwd@host:5433/database?connection_timeout=10"
)

# 2. Sync vertica-python
engine = sa.create_engine(
    "vertica+vertica_python://user:pwd@host:5433/database?connection_timeout=10"
)

# 3. PyODBC with connection string
engine_pyodbc = sa.create_engine(
    "vertica+pyodbc:///?odbc_connect=DSN%3DVerticaDSN"
)

# 4. Turbodbc with DSN
engine_turbodbc = sa.create_engine(
    "vertica+turbodbc:///?DSN=VerticaDSN"
)

Quick Start

Synchronous SQLAlchemy 2.0

from sqlalchemy import create_engine, text

engine = create_engine("vertica+vertica_python://user:pwd@localhost:5433/mydb")

with engine.connect() as conn:
    result = conn.execute(text("SELECT version()"))
    print(result.scalar())

# Transaction block
with engine.begin() as conn:
    conn.execute(
        text("INSERT INTO my_table (name) VALUES (:name)"),
        {"name": "Alice"}
    )

Asynchronous SQLAlchemy 2.0 & FastAPI

import asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker

async def main():
    engine = create_async_engine(
        "vertica+vertica_python_async://user:pwd@localhost:5433/mydb",
        pool_size=10,
    )

    async with engine.connect() as conn:
        result = await conn.execute(text("SELECT 1"))
        print(result.scalar())

    # Using AsyncSession
    session_factory = async_sessionmaker(engine, class_=AsyncSession)
    async with session_factory() as session:
        result = await session.execute(text("SELECT COUNT(*) FROM my_table"))
        print("Count:", result.scalar())

    await engine.dispose()

asyncio.run(main())

Alembic Migrations

In your Alembic env.py, simply import sqlalchemy_vertica:

import sqlalchemy_vertica  # Registers VerticaImpl plugin automatically
from alembic import context

# configure context
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    transactional_ddl=True,
)

Vertica does not support traditional B-tree indexes (it utilizes projections). sqlalchemy-vertica treats index creation/dropping as safe no-ops in migrations to ensure multi-database migration scripts run seamlessly.

Custom Data Types

from sqlalchemy import Column, Integer, Table, MetaData
from sqlalchemy_vertica import (
    GEOMETRY,
    GEOGRAPHY,
    UUID,
    LONG_VARCHAR,
    ARRAY,
    MAP,
    ROW,
    TIMESTAMPTZ,
)

metadata = MetaData()

places = Table(
    "places",
    metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("guid", UUID, nullable=False),
    Column("description", LONG_VARCHAR),
    Column("location", GEOMETRY(srid=4326)),
    Column("tags", ARRAY(LONG_VARCHAR)),
    Column("metadata", MAP(LONG_VARCHAR, LONG_VARCHAR)),
    Column("created_at", TIMESTAMPTZ),
)

Testing & Coverage

Run the automated test suite with pytest and pytest-cov:

pytest -v --cov=sqlalchemy_vertica --cov-report=term-missing

Support

If you find this project helpful and want to support its maintenance and development, you can buy me a coffee:

Buy Me A Coffee

License

MIT License. See LICENSE for details.

Metadata

Release files for sqlalchemy-vertica 1.0.4

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for sqlalchemy-vertica 1.0.4
File Size Uploaded Provenance Actions
sqlalchemy_vertica-1.0.4.tar.gz 26.4 kB With provenance Details for sqlalchemy_vertica-1.0.4.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for sqlalchemy-vertica 1.0.4
File Interpreter ABI Platform Actions
sqlalchemy_vertica-1.0.4-py3-none-any.whl Python 3 none any Details for sqlalchemy_vertica-1.0.4-py3-none-any.whl

Total release size: 44.1 kB

Release files / sqlalchemy_vertica-1.0.4.tar.gz

Download URL sqlalchemy_vertica-1.0.4.tar.gz
Size 26.4 kB
Tags Source
SHA-256 checksum
How to use checksums
45bd4c7bf1e37138440dabd95671b21b93019a9cc595acc31178579bed4d3e44
BLAKE2b-256 checksum
How to use checksums
a2a622d499ae1794e254dd0041baf17260ddca7e1f63df60dd1f7e51ea1f882f
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 17, 2026.

Transparency log

Release files / sqlalchemy_vertica-1.0.4-py3-none-any.whl

Download URL sqlalchemy_vertica-1.0.4-py3-none-any.whl
Size 17.7 kB
Tags Python 3
SHA-256 checksum
How to use checksums
ffa99877cc536eb282b932e885a412b64b745d6bf730d9b1d1acfdd0c64a1e47
BLAKE2b-256 checksum
How to use checksums
379b6130369e12e369b34697c9ed651fb0515634e31a991d2bb4b96329026126
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 17, 2026.

Transparency log

Security for sqlalchemy-vertica 1.0.4

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for sqlalchemy_vertica-1.0.4.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 17, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page