skip to content

In SQLAlchemy 2.1, what does a create_engine() URL encode, and how do you pass a password containing @ or / safely?

level: juniorimportance: must knowfreq 60%

answer

  1. scheme names two things
  2. default driver changed in 2.1
  3. percent-encoding in the userinfo
  4. a constructor that skips parsing

basics

~10 s

A SQLAlchemy URL reads dialect+driver://user:password@host:port/database, and create_engine() turns it into a dialect, a DBAPI driver and a pool. Percent-encode special password characters, or build the URL with URL.create() and pass them raw.

solid answer

~40 s

`create_engine()` takes a URL shaped `dialect+driver://username:password@host:port/database`. The dialect (`postgresql`, `mysql`, `sqlite`) decides what SQL gets compiled; the driver names the DBAPI package, and when it is left out SQLAlchemy uses the backend's default, which in 2.1 is `psycopg` (version 3) for PostgreSQL instead of `psycopg2`. Special characters in the username or password must be URL-encoded, so `@` becomes `%40` and `/` becomes `%2F`, for example with `urllib.parse.quote_plus()`. The cleaner route is `URL.create()`, which takes each field separately and unescaped and skips string parsing altogether. Printing the `Engine` or its `URL` masks the password as `***`. Create one engine per database URL per process and reuse it, because it owns the connection pool.

code

python · 23 lines
python
from sqlalchemy import URL, create_engine, make_url

# A raw "@" and "/" in the password break string parsing
broken = make_url("postgresql+psycopg://report:p@ss/[email protected]/orders")
print(broken.password, broken.host, broken.database)

# URL.create() takes every field unescaped and skips parsing
url = URL.create(
    "postgresql+psycopg",
    username="report",
    password="p@ss/word",
    host="db.internal",
    port=5432,
    database="orders",
)
print(url)  # the password is masked when printed

# With no driver named, 2.1 picks psycopg (version 3) for PostgreSQL
print(make_url("postgresql://[email protected]/orders").get_dialect().driver)

# Locally the same code reads a SQLite file
local_engine = create_engine("sqlite:///orders.db")
print(local_engine)

go deeper

for a junior

Recall the dialect+driver://user:password@host:port/database shape and the three SQLite forms, and know that an @ in a password needs encoding or URL.create().

for a middle

Explain what create_engine() assembles from the URL (dialect, driver, pool), what happens when the driver part is omitted, and why the engine is created once and shared.

for a senior

Show you can diagnose a connection to the wrong host caused by an unencoded password, and pin the driver explicitly so a 2.1 upgrade does not switch from psycopg2 to psycopg unnoticed.

for a principal

Talk about where connection credentials live, how they reach URL.create() without hand-encoding, and how a team keeps the driver choice explicit across environments and upgrades.

## What create_engine() builds from a URL An **Engine** is SQLAlchemy's entry point to one database. `create_engine()` receives a **database URL** and assembles three things from it: - a **dialect**, the component that knows the backend's SQL flavour, its type rules and its parameter style; - a **DBAPI driver**, the Python package (PEP 249 module) that opens real network or file connections; - a **connection pool** that keeps those connections for reuse. Nothing connects at `create_engine()` time; the first connection opens when code calls `engine.connect()` or `engine.begin()`. The driver module is imported immediately, though, so a URL naming a driver that is not installed fails at once with `ModuleNotFoundError` rather than at the first query. ## The URL format, field by field The general form is `dialect+driver://username:password@host:port/database`. | Part | Example | Meaning | |---|---|---| | dialect | `postgresql`, `mysql`, `sqlite` | which SQLAlchemy dialect compiles statements | | `+driver` | `+psycopg`, `+psycopg2`, `+pymysql` | which DBAPI package to load; optional | | `username:password` | `report:s3cret` | credentials, URL-encoded | | `host:port` | `db.internal:5432` | server address; port optional | | `/database` | `/orders` | database name, or a file path for SQLite | SQLite has no server, so its URL carries a path: `sqlite:///orders.db` is a file relative to the working directory (three slashes), `sqlite:////var/data/orders.db` an absolute path (four), and `sqlite://` an in-memory database. This is why one code base can read a PostgreSQL URL in production and a SQLite file locally: only the configured string changes, though the SQL each backend accepts still differs. ## Default drivers and what changed in 2.1 When the `+driver` part is omitted, SQLAlchemy imports that dialect's default DBAPI. **In SQLAlchemy 2.1 the PostgreSQL default is `psycopg` (version 3)**; through 2.0 it was `psycopg2`. So `postgresql://[email protected]/orders` now needs `psycopg` installed, and a deployment that only has `psycopg2` must say `postgresql+psycopg2://` explicitly. The 2.1 migration notes call naming the driver explicitly the preferred form, and doing so keeps an upgrade from silently switching drivers. ## Passwords with special characters The URL is parsed like a web address, so characters with meaning in a URL break it when they appear raw in a password. A password `p@ss/word` written straight into the string is split at the wrong places: 1. `postgresql+psycopg://report:p@ss/[email protected]/orders` parses as password `p`, host `ss` and database `[email protected]/orders`. 2. The engine then tries to reach a host called `ss`, and the error points at the network, not at the password. There are two correct fixes: - **Percent-encode** the username and password before building the string: `urllib.parse.quote_plus("p@ss/word")` gives `p%40ss%2Fword`. Backslashes or quotes do not escape anything here. - **Build the URL as an object** with `URL.create("postgresql+psycopg", username=..., password=..., host=..., port=..., database=...)`. Each field is passed separately and unescaped, no parsing happens, and the object goes straight to `create_engine()`. `URL.create()` is the safer habit when credentials come from a secret store or environment variables, because nobody has to remember the encoding step. `make_url()` does the reverse: it parses a string into a `URL` object, which is handy for checking what a configured string really means. ## One engine per URL per process The documentation describes the typical use as **one `create_engine()` call per database URL, held for the lifetime of the process**. The engine is safe to share across threads and manages many DBAPI connections. Creating an engine inside a function or per request builds a fresh pool every time, so connections are not reused. The nightly report script and the long-running service in this scenario each create their engine once at module level. Pool sizing and what to do after a process fork are separate pool-configuration subjects. ## Keeping the password out of logs and code - `str(url)` and `repr(engine)` render the password as `***`, so logging the engine does not print it. - `url.render_as_string(hide_password=False)` returns the full string when a tool really needs it; treat its output as a secret. - Keep the URL, or its parts, in configuration rather than source code, and pass the password through `URL.create()` so it never needs hand-encoding.

  • What happens at create_engine() time if the driver the URL names is not installed?
    `create_engine()` imports the DBAPI module straight away, so a missing package fails immediately with `ModuleNotFoundError`, before any connection is attempted. In 2.1 this also catches a bare `postgresql://` URL on a machine that only has `psycopg2`, because the default is now `psycopg`; name `postgresql+psycopg2://` if you mean the older driver.
  • How do the SQLite URL forms differ, and why is that useful for local development?
    `sqlite:///orders.db` is a file path relative to the working directory, `sqlite:////var/data/orders.db` an absolute path, and `sqlite://` an in-memory database. Reading the URL from configuration lets the same code use a SQLite file locally and PostgreSQL in production, though SQL that relies on one backend's features may not run on the other.
  • Why does SQLAlchemy's documentation say to call create_engine() once per URL per process?
    The `Engine` owns a pool of DBAPI connections and is built to be shared by concurrent callers. Creating it in each function or request builds a new pool each time, so connections are opened again instead of reused. Create it at module level, or in application start-up, and pass it around.

  • Put the raw password in the URL string; create_engine() works out where it ends.
  • A bare postgresql:// URL still loads psycopg2 by default in SQLAlchemy 2.1.
  • Escape an @ in the password with a backslash inside the URL string.
  • create_engine() opens a database connection as soon as it is called.
  • Call create_engine() inside every function that needs the database.

Assumes SQLAlchemy 2.1, where a PostgreSQL URL without a driver loads psycopg (version 3) instead of psycopg2.