Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 37 ยท Engine and Connections

Creating an Engine and Database URLs

Learn how create_engine() works, how database URLs are built for SQLite, PostgreSQL and MySQL, how to hide passwords, and which engine options matter first.

Every SQLAlchemy program starts with an Engine. The engine is a factory and a home base for database connections: it knows which database to reach, which dialect of SQL to speak, and how to pool connections. You normally create exactly one engine per database for the whole life of your application.

An engine is created with create_engine() and a database URL, a single string that says which database, which driver and where. Getting comfortable with URLs makes switching between a local SQLite file and a production PostgreSQL server a one-line change.

Anatomy of a URL

A URL has this general form:

TEXT
dialect+driver://username:password@host:port/database?option=value
  • dialect is the kind of database: sqlite, postgresql, mysql, mssql, oracle.
  • driver is the Python library used to talk to it: psycopg, pymysql, pyodbc. It is optional; each dialect has a default.
  • The rest is ordinary connection information. SQLite has no host or user, so a SQLite URL is just a path.
Goal Example URL
SQLite in memory sqlite:///:memory: or just sqlite://
SQLite file, relative path sqlite:///app.db
SQLite file, absolute path sqlite:////var/data/app.db
PostgreSQL postgresql+psycopg://app:secret@localhost:5432/shop
MySQL mysql+pymysql://app:secret@localhost/shop?charset=utf8mb4

Only the SQLite lines can run in the browser. PostgreSQL and MySQL URLs are shown so you recognize them in real projects; this site cannot connect to those servers.

Building and inspecting URLs

You rarely need to assemble a URL string by hand. URL.create() builds one safely, which matters because passwords may contain characters such as @ or / that would break a plain string. make_url() parses a string back into an object.

Python URL objects

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

URLs are immutable: set(), update_query_dict() and similar methods return a new object. When a URL is printed, the password is replaced by ***, which keeps secrets out of logs.

Never hard-code real credentials in source code. Read the URL from an environment variable or a secrets manager: create_engine(os.environ["DATABASE_URL"]).

Creating an engine

create_engine() returns immediately. It does not connect to the database; the first connection happens when you first use the engine. That means a wrong password only shows up at first use.

Python An engine is lazy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the two failure points. An unknown dialect raises as soon as you call create_engine(). A server that is down or a wrong password raises when you first connect.

Engine options worth knowing

create_engine() accepts many keyword arguments. These are the ones you will meet first:

Option Meaning
echo=True Log every SQL statement. For learning and debugging only.
pool_size, max_overflow How many connections the pool keeps and may add temporarily (next lessons).
pool_pre_ping=True Test a connection before handing it out, to survive dropped connections.
pool_recycle=1800 Replace connections older than this many seconds.
connect_args={...} Extra arguments passed straight to the driver's connect().
isolation_level="..." Default transaction isolation level for all connections.
execution_options={...} Default options for every statement.

connect_args is how driver-specific settings travel through. For example, SQLite's check_same_thread=False allows using one connection from several threads. You can also open a second engine with engine.execution_options(...), which returns a lightweight copy that shares the same pool.

A common pattern: one config function

Real programs keep URL creation in one place, so tests can point at SQLite and production at PostgreSQL.

Python Choosing the URL from configuration

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When does create_engine() actually open a database connection?

    The engine is lazy. It validates the URL up front but connects on first use.
  2. 2. What does the URL sqlite:////var/data/app.db point to?

    Three slashes introduce a relative path; the fourth slash starts an absolute path.
  3. 3. Why use URL.create() instead of an f-string?

    URL.create() handles quoting for you and masks the password in the string form.
  4. 4. Where should production credentials live?

    Keep secrets out of source control and read them at start-up.

Summary

  • An engine represents one database: its URL, dialect and connection pool. Create one per database.
  • URLs follow dialect+driver://user:pass@host:port/db; build them with URL.create() and read with make_url().
  • create_engine() is lazy; errors about servers and passwords appear at first connect.
  • Useful options: echo, pool_pre_ping, pool_recycle, connect_args, isolation_level.

Next: Connections and Transactions