Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 37 ยท Getting Started

Installing SQLAlchemy and Your First Program

Install SQLAlchemy 2.0 with pip, pick a database driver, check the version, and write a first program that creates a table, inserts rows and reads them back.

Getting SQLAlchemy onto your machine takes one command, and you do not need a database server to start: Python ships with SQLite, and SQLAlchemy talks to it out of the box. In this lesson you install the library, learn what a database driver is, and write a complete program that creates a table, inserts rows and reads them back.

The examples on this page run in your browser. On your own computer the code is identical; only the install step differs.

Installing on your computer

Use a virtual environment so the project has its own packages:

Terminal
python -m venv .venv
# Windows: .venv\Scripts\activate    macOS/Linux: source .venv/bin/activate
pip install "sqlalchemy>=2.0"
python -c "import sqlalchemy; print(sqlalchemy.__version__)"

SQLAlchemy itself is pure Python (with optional C extensions for speed). It needs a driver, a small library that actually speaks to one kind of database. SQLite's driver (sqlite3) is part of Python's standard library, so SQLite needs nothing extra. For other databases you install the driver too:

Database Typical driver Install
SQLite sqlite3 (built in) nothing
PostgreSQL psycopg pip install psycopg[binary]
MySQL / MariaDB pymysql or mysqlclient pip install pymysql
SQL Server pyodbc pip install pyodbc
Oracle oracledb pip install oracledb

Those server databases cannot run in the browser, so in this course they appear only as read-only snippets.

In the browser TryIts, the first two lines are always import micropip and await micropip.install("sqlalchemy"). They download SQLAlchemy into the page. You will not need them in a normal Python project.

Your first complete program

This program uses Core, the lower layer, because it shows what SQLAlchemy does with the least ceremony. It describes one table, creates it, inserts three rows and queries them.

Python Create, insert, select

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.

Walk through it:

  1. create_engine("sqlite:///:memory:") creates an engine for a throwaway database that lives in memory.
  2. MetaData is a container for table definitions, and Table(...) declares columns and types.
  3. metadata.create_all(engine) emits CREATE TABLE for every table that does not exist yet.
  4. engine.begin() opens a connection and a transaction that commits when the with block ends without an error.
  5. select(...) builds a query. Nothing is sent to the database until you call conn.execute(stmt).

Seeing the SQL

SQLAlchemy can show you the SQL it sends. The easiest way is echo=True on the engine, which logs every statement. You can also print a statement object to see the SQL text it would generate.

Python echo=True and printing statements

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.

You will see INSERT ... VALUES (?) followed by the parameter list on a separate line. The value python is never pasted into the SQL text. That separation is the injection protection from the previous lesson at work. Printing the select shows a named placeholder such as :name_1, because a statement is rendered for a generic dialect until it runs.

Turn echo=True off in production. It is a learning and debugging aid, and it writes every statement (with parameters) to your logs.

Common first-day mistakes

  • Forgetting to commit. With engine.connect() nothing is saved unless you call conn.commit(). engine.begin() commits for you. Chapter 2 covers this in detail.
  • Using the wrong URL. sqlite:///file.db has three slashes (relative path). Four slashes after the colon (sqlite:////tmp/file.db) mean an absolute path.
  • Mixing 1.x and 2.0 examples. If you see session.query(User).filter_by(...) or engine.execute(...), that is old code. engine.execute() no longer exists in 2.0.
  • Calling a table column as an attribute. On a Table, columns live in books.c, so you write books.c.title, not books.title.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which Python module provides the driver SQLAlchemy uses for SQLite?

    SQLite is built into Python through sqlite3, so you need no extra package for it.
  2. 2. What does engine.begin() do that engine.connect() alone does not?

    begin() is a convenience for connect() plus an automatic commit on success (and rollback on error).
  3. 3. How do you refer to the title column of a Table object named books?

    A Table exposes its columns through the .c collection, for example books.c.title.
  4. 4. Why is the value in an INSERT not visible inside the SQL string when echo=True?

    SQLAlchemy sends the SQL text with placeholders and passes values separately, which also prevents injection.

Summary

  • Install with pip install sqlalchemy; SQLite needs no extra driver, other databases need one.
  • create_engine(), MetaData/Table, create_all(), begin() and select() are enough for a full first program.
  • echo=True logs the SQL, and printing a statement shows its text.
  • Columns of a Table live in table.c.

Next: Core vs ORM: How SQLAlchemy Is Organized