or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

configuration.mdconnection-management.mdcore-integration.mddata-types.mdindex.md
tile.json

tessl/pypi-duckdb-engine

SQLAlchemy driver for DuckDB enabling seamless integration between Python applications and DuckDB's analytical database engine

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/duckdb-engine@0.17.x

To install, run

npx @tessl/cli install tessl/pypi-duckdb-engine@0.17.0

index.mddocs/

DuckDB Engine

A comprehensive SQLAlchemy driver for DuckDB that enables seamless integration between Python applications and DuckDB's analytical database engine. The driver extends PostgreSQL dialect functionality to accommodate DuckDB's specific features while maintaining compatibility with SQLAlchemy 1.3+ and Python 3.9+.

Package Information

  • Package Name: duckdb-engine
  • Language: Python
  • Installation: pip install duckdb-engine
  • SQLAlchemy Version: 1.3.22+
  • Python Version: 3.9+
  • DuckDB Version: 0.5.0+

Core Imports

from duckdb_engine import Dialect, ConnectionWrapper, CursorWrapper, DBAPI, DuckDBEngineWarning

For SQLAlchemy integration:

from sqlalchemy import create_engine

For PostgreSQL-style INSERT operations with DuckDB compatibility:

from duckdb_engine import insert  # Re-export of sqlalchemy.dialects.postgresql.insert

Basic Usage

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from sqlalchemy.orm import sessionmaker

# Create engine with DuckDB
engine = create_engine('duckdb:///:memory:')

# Define a simple table
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('email', String(100))
)

# Create table
metadata.create_all(engine)

# Insert data
with engine.connect() as conn:
    conn.execute(users.insert().values(name='John Doe', email='john@example.com'))
    conn.commit()
    
    # Query data
    result = conn.execute(users.select()).fetchall()
    print(result)

Architecture

DuckDB Engine implements the SQLAlchemy dialect interface with several key components:

  • Dialect: Main SQLAlchemy dialect extending PostgreSQL compatibility
  • Connection Management: Connection and cursor wrappers for DuckDB integration
  • Type System: Comprehensive type mapping including DuckDB-specific types
  • Schema Inspection: Database reflection and metadata capabilities
  • Transaction Handling: Transaction management with DuckDB-specific optimizations

Capabilities

Core SQLAlchemy Integration

Main dialect class and connection management providing SQLAlchemy compatibility for DuckDB databases, including connection pooling, transaction management, and schema operations.

class Dialect(PGDialect_psycopg2):
    name = "duckdb"
    driver = "duckdb_engine"
    
    def connect(self, *cargs, **cparams): ...
    def create_connect_args(self, url): ...
    def get_pool_class(cls, url): ...

Core Integration

Data Types and Type System

Comprehensive type system supporting DuckDB's native types including integers, complex types (STRUCT, MAP, UNION), and extension types with proper SQLAlchemy mapping.

class Struct(TypeEngine):
    def __init__(self, fields=None): ...

class Map(TypeEngine):
    def __init__(self, key_type, value_type): ...

class Union(TypeEngine):
    def __init__(self, fields): ...

Data Types

Connection and Cursor Management

Connection and cursor wrapper classes that adapt DuckDB's Python API to SQLAlchemy's expected interface, handling special DuckDB operations and DataFrame registration.

class ConnectionWrapper:
    def __init__(self, c): ...
    def cursor(self): ...
    def close(self): ...

class CursorWrapper:
    def __init__(self, c, connection_wrapper): ...
    def execute(self, statement, parameters=None, context=None): ...
    def executemany(self, statement, parameters=None, context=None): ...

Connection Management

Configuration and Extensions

Configuration management for DuckDB settings, extension loading, and filesystem registration with support for MotherDuck and custom configuration options.

def get_core_config():
    """Get set of core DuckDB configuration parameters."""
    
def apply_config(dialect, conn, ext):
    """Apply configuration settings to DuckDB connection."""

Configuration

Types

# Core SQLAlchemy types (for reference)
from sqlalchemy.engine.interfaces import Dialect as RootDialect
from sqlalchemy.dialects.postgresql.base import PGDialect_psycopg2
from sqlalchemy.sql.type_api import TypeEngine
from sqlalchemy.engine.url import URL
from sqlalchemy import pool
from typing import Type, Dict, List, Optional, Any

# DuckDB connection type
DuckDBPyConnection = Any  # duckdb.DuckDBPyConnection

class DBAPI:
    paramstyle: str
    apilevel: str
    threadsafety: int
    Error: Type[Exception]
    TransactionException: Type[Exception]
    ParserException: Type[Exception]
    
    @staticmethod
    def Binary(x): ...

class DuckDBEngineWarning(Warning):
    pass

# Version and feature detection constants
__version__: str  # Current duckdb-engine version
sqlalchemy_version: str  # SQLAlchemy version in use  
duckdb_version: str  # DuckDB version in use
supports_attach: bool  # Whether DuckDB supports ATTACH (v0.7.0+)
supports_user_agent: bool  # Whether DuckDB supports custom user agent (v0.9.2+)