Engine Architecture

The Engine is the starting point for any SQLAlchemy Core application. It manages database connectivity, connection pooling, and dialect-specific behavior, serving as a factory for database connections.

Overview

The Engine doesn’t establish actual database connections until they’re needed. Instead, it holds configuration and manages a connection pool that creates connections on demand.

from sqlalchemy import create_engine

# Create an engine - no connection is made yet
engine = create_engine("postgresql+psycopg2://user:password@localhost/dbname")

# Connection is established when needed
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))

Creating an Engine

Basic Usage

Use create_engine() to create a new Engine instance:

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

The create_engine() function does not establish any actual database connections immediately. Connections are created when first requested via Engine.connect() or when executing statements.

Function Signature

def create_engine(
    url: Union[str, URL],
    *,
    # Connection arguments
    connect_args: Dict[Any, Any] = {},
    creator: Callable = None,
    
    # Logging and debugging
    echo: Union[bool, str] = False,
    echo_pool: Union[bool, str] = False,
    hide_parameters: bool = False,
    
    # Pool configuration
    pool: Pool = None,
    poolclass: Type[Pool] = None,
    pool_size: int = 5,
    max_overflow: int = 10,
    pool_timeout: float = 30,
    pool_recycle: int = -1,
    pool_pre_ping: bool = False,
    pool_use_lifo: bool = False,
    pool_reset_on_return: str = "rollback",
    
    # Engine options
    isolation_level: str = None,
    execution_options: Dict = {},
    json_serializer: Callable = None,
    json_deserializer: Callable = None,
    
    # Performance
    query_cache_size: int = 500,
    insertmanyvalues_page_size: int = 1000,
    
    **kwargs: Any
) -> Engine

URL Format

Database URLs follow the RFC-1738 format:

dialect[+driver]://username:password@host:port/database[?key=value...]

Common URL Examples

PostgreSQL

# Using psycopg2 (default driver)
engine = create_engine("postgresql://user:pass@localhost/dbname")

# Using psycopg2 explicitly
engine = create_engine("postgresql+psycopg2://user:pass@localhost/dbname")

# Using asyncpg (async)
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/dbname")

MySQL / MariaDB

# Using mysqlclient (recommended)
engine = create_engine("mysql+mysqldb://user:pass@localhost/dbname")

# Using PyMySQL (pure Python)
engine = create_engine("mysql+pymysql://user:pass@localhost/dbname")

# Using MySQL Connector/Python
engine = create_engine("mysql+mysqlconnector://user:pass@localhost/dbname")

SQLite

# In-memory database
engine = create_engine("sqlite:///:memory:")

# File-based database (relative path)
engine = create_engine("sqlite:///mydb.db")

# File-based database (absolute path)
engine = create_engine("sqlite:////absolute/path/to/mydb.db")

Oracle

# Using cx_Oracle
engine = create_engine("oracle+cx_oracle://user:pass@localhost:1521/?service_name=ORCL")

Microsoft SQL Server

# Using pyodbc
engine = create_engine("mssql+pyodbc://user:pass@localhost/dbname?driver=ODBC+Driver+17+for+SQL+Server")

# Using pymssql
engine = create_engine("mssql+pymssql://user:pass@localhost/dbname")

URL Components

dialect

string

required

Database backend name: postgresql, mysql, sqlite, oracle, mssql, etc.

driver

string

DBAPI driver name: psycopg2, pymysql, cx_oracle, etc. If omitted, the default driver for the dialect is used.

username

string

Database username. Must be URL-encoded if it contains special characters.

password

string

Database password. Must be URL-encoded if it contains special characters.

host

string

Hostname or IP address of the database server.

port

integer

Port number. Each dialect has a default port if omitted.

database

string

Database name or path (for SQLite).

query

string

Query string parameters for driver-specific or dialect-specific options.

Programmatic URL Construction

For dynamic URL construction or when handling special characters:

from sqlalchemy.engine import URL

url = URL.create(
    drivername="postgresql+psycopg2",
    username="user",
    password="p@ss!w0rd",  # No need to URL-encode
    host="localhost",
    port=5432,
    database="mydb",
    query={"sslmode": "require", "connect_timeout": "10"}
)

engine = create_engine(url)

When passing passwords as strings in create_engine(), special characters must be URL-encoded. Use URL.create() to avoid manual encoding.

Engine Configuration Options

Logging and Echo

echo

bool | str

default:"False"

Enable SQL statement logging. Set to True for statement logging, or "debug" to also log result sets.

engine = create_engine("postgresql://...", echo=True)

echo_pool

bool | str

default:"False"

Enable connection pool logging. Set to "debug" for detailed pool checkout/checkin logging.

engine = create_engine("postgresql://...", echo_pool="debug")

hide_parameters

bool

default:"False"

When True, SQL parameter values are hidden from logs for security.

Connection Pool Configuration

See the Connection Management page for detailed pool configuration.

pool_size

int

default:"5"

Number of connections to maintain in the pool.

max_overflow

int

default:"10"

Maximum number of connections that can be created beyond pool_size.

pool_timeout

float

default:"30"

Seconds to wait for a connection from the pool before raising an error.

pool_recycle

int

default:"-1"

Recycle connections after this many seconds. Useful for databases that close idle connections.

# Recycle connections after 1 hour (MySQL default timeout is 8 hours)
engine = create_engine("mysql://...", pool_recycle=3600)

pool_pre_ping

bool

default:"False"

Test connections for liveness before using them.

# Enable pessimistic disconnect handling
engine = create_engine("postgresql://...", pool_pre_ping=True)

pool_use_lifo

bool

default:"False"

Use last-in-first-out for pool retrieval, allowing server-side timeouts to close unused connections.

Transaction Isolation

isolation_level

str

Set the default transaction isolation level. Valid values depend on the database backend:

"SERIALIZABLE"
"REPEATABLE READ"
"READ COMMITTED"
"READ UNCOMMITTED"
"AUTOCOMMIT"

engine = create_engine(
    "postgresql://...",
    isolation_level="REPEATABLE READ"
)

Performance Options

query_cache_size

int

default:"500"

Size of the SQL compilation cache. Set to 0 to disable caching.

insertmanyvalues_page_size

int

default:"1000"

Number of rows per batch in executemany operations with RETURNING support.

Engine from Configuration

Create an engine from a configuration dictionary (useful for config files):

from sqlalchemy import engine_from_config

config = {
    "sqlalchemy.url": "postgresql://user:pass@localhost/dbname",
    "sqlalchemy.echo": "True",
    "sqlalchemy.pool_size": "10",
    "sqlalchemy.max_overflow": "20"
}

engine = engine_from_config(config, prefix="sqlalchemy.")

Engine Methods

Connecting

# Get a connection from the pool
with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)

Executing (Context Manager with Transaction)

# Automatically begins and commits a transaction
with engine.begin() as conn:
    conn.execute(text("INSERT INTO users (name) VALUES (:name)"), {"name": "Alice"})
    conn.execute(text("INSERT INTO users (name) VALUES (:name)"), {"name": "Bob"})
# Transaction is committed automatically if no exception

Disposal

# Close all pooled connections
engine.dispose()

Call engine.dispose() when you need to ensure all database connections are closed, such as before forking a process or at application shutdown.

Engine Properties

engine.dialect - The Dialect instance for the database backend
engine.pool - The Pool instance managing connections
engine.url - The URL object representing the connection string
engine.driver - The DBAPI driver name (e.g., “psycopg2”)
engine.name - The database backend name (e.g., “postgresql”)

Custom DBAPI Connection

Override the default connection creation:

import psycopg2

def custom_creator():
    return psycopg2.connect(
        host="localhost",
        user="user",
        password="password",
        database="mydb",
        connect_timeout=10
    )

engine = create_engine("postgresql://", creator=custom_creator)

Advanced: Custom Connection Arguments

Pass additional arguments to the DBAPI connect() method:

engine = create_engine(
    "postgresql://user:pass@localhost/dbname",
    connect_args={
        "connect_timeout": 10,
        "application_name": "my_app",
        "options": "-c timezone=utc"
    }
)

Best Practices

Create Once, Use Many

Create one Engine per database URL in your application. The Engine is designed to be a global singleton.

# Good - module level
engine = create_engine("postgresql://...")

# Bad - creating engines repeatedly
def get_users():
    engine = create_engine("postgresql://...")  # Don't do this
    # ...

Use Connection Context Managers

Always use context managers to ensure connections are properly returned to the pool.

# Good
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))

# Avoid
conn = engine.connect()
result = conn.execute(text("SELECT 1"))
conn.close()  # Easy to forget

Configure Pool Appropriately

Tune pool settings based on your application’s concurrency and database limits.

engine = create_engine(
    "postgresql://...",
    pool_size=20,        # Base pool size
    max_overflow=10,     # Allow bursts up to 30 connections
    pool_recycle=3600,   # Recycle after 1 hour
    pool_pre_ping=True   # Check connection health
)

Enable Logging During Development

Use echo=True to see generated SQL during development.

engine = create_engine("postgresql://...", echo=True)

Getting Started

Tutorials

Core Concepts

Engine Architecture

Engine Architecture

Overview

Creating an Engine

Basic Usage

Function Signature

URL Format

Common URL Examples

URL Components

Programmatic URL Construction

Engine Configuration Options

Logging and Echo

Connection Pool Configuration

Transaction Isolation

Performance Options

Engine from Configuration

Engine Methods

Connecting

Executing (Context Manager with Transaction)

Disposal

Engine Properties

Custom DBAPI Connection

Advanced: Custom Connection Arguments

Best Practices

See Also

Getting Started

Tutorials

Core Concepts

​Engine Architecture

​Overview

​Creating an Engine

​Basic Usage

​Function Signature

​URL Format

​Common URL Examples

​URL Components

​Programmatic URL Construction

​Engine Configuration Options

​Logging and Echo

​Connection Pool Configuration

​Transaction Isolation

​Performance Options

​Engine from Configuration

​Engine Methods

​Connecting

​Executing (Context Manager with Transaction)

​Disposal

​Engine Properties

​Custom DBAPI Connection

​Advanced: Custom Connection Arguments

​Best Practices

​See Also

Engine Architecture

Overview

Creating an Engine

Basic Usage

Function Signature

URL Format

Common URL Examples

URL Components

Programmatic URL Construction

Engine Configuration Options

Logging and Echo

Connection Pool Configuration

Transaction Isolation

Performance Options

Engine from Configuration

Engine Methods

Connecting

Executing (Context Manager with Transaction)

Disposal

Engine Properties

Custom DBAPI Connection

Advanced: Custom Connection Arguments

Best Practices

See Also