sqlalchemy Getting started with sqlalchemy Hello, World! (SQLAlchemy Core)

Example

This example shows how to create a table, insert data, and select from the database using SQLAlchemy Core. For information re: the SQLAlchemy ORM, see here.

First, we'll need to connect to our database.

from sqlalchemy import create_engine

engine = create_engine('sqlite://')

The engine is the starting point for any SQLAlchemy application. It’s a “home base” for the actual database and its DBAPI, delivered to an SQLAlchemy application through a connection pool and a dialect, which describes how to talk to a specific kind of database/DBAPI combination. The Engine references both a dialect and a connection pool, which together interpret the DBAPI’s module functions as well as the behaviour of the database.

After creating our engine, we need to define and create our tables.

from sqlalchemy import Column, Integer, Text, MetaData, Table

metadata = MetaData()
messages = Table(
    'messages', metadata,
    Column('id', Integer, primary_key=True),
    Column('message', Text),
)

messages.create(bind=engine)

To futher explain the MetaData object, see the below from the docs:

A collection of Table objects and their associated child objects is referred to as database metadata

We define our tables all within a catalog called MetaData, using the Table construct, which resembles regular SQL CREATE TABLE statements.

Now that we have our tables defined and created, we can start inserting data! Inserting involves two steps. Composing the insert construct, and executing the final query.

insert_message = messages.insert().values(message='Hello, World!')
engine.execute(insert_message)

Now that we have data, we can use the select function to query our data. Column objects are available as named attributes of the c attribute on the Table object, making it easy to select columns directly. Executing this select statement returns a ResultProxy object which has access to a few methods, fetchone(), fetchall(), and fetchmany(), all of which return a number of database rows queried in our select statement.

from sqlalchemy import select
stmt = select([messages.c.message]) 
message, = engine.execute(stmt).fetchone()
print(message)

Hello, World!

And that's it! See the SQLAlchemy SQL Expressions Tutorial for more examples and information.