Кратко про SQLAlchemy Core

SQLAlchemy Engine это слой абстракции над DB-API.
Он содержит DB-API драйвера для разных СУБД, их можно указать в строке подключения.
Engine.execute() и Engine.connect() два основных метода.
Так же у него есть свой пул соединений.
create_engine() - фабричная функция для создания Engine.

Metadata это логическая структура базы данных. Она содержит список таблиц(Table), их отношения и другие объекты.
Table абстракция таблицы. Содержит колонки(Column) и другие свойства.
Таким образом можно иметь одновременно несколько engine с разными драйверами и коннектами, разные metadata, и совместно их использовать.
примеры на github

from sqlalchemy import MetaData, Table, Column, Integer, Numeric, String, DateTime
from datetime import datetime

metadata = MetaData()

users = Table('users', metadata,
              Column('user_id', Integer(), primary_key=True),
              Column('username', String(15), nullable=False, unique=True),
              Column('email_address', String(255), nullable=False),
              Column('phone', String(20), nullable=False),
              Column('password', String(25), nullable=False),
              Column('created_on', DateTime(), default=datetime.now),
              Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
              )

Функция datetime.now будет вызвана при создании и изменении объекта.

default=datetime.now, onupdate=datetime.now

Ключи, ограничения, индексы

PrimaryKeyConstraint('user_id', name='user_pk')
UniqueConstraint('username', name='uix_username')
CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')
Index('ix_cookies_cookie_name', 'cookie_name')
ForeignKeyConstraint(['order_id'], ['orders.order_id'])

Создание и удаление всех объектов Metadata на уровне БД

metadata.create_all(engine)
metadata.drop_all(engine)

INSERT

ins = db.cookies.insert().values(
    cookie_name="chocolate chip",
    cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
    cookie_sku="CC01",
    quantity="12",
    unit_cost="0.50"
)

result = db.engine.execute(ins)
print(result.inserted_primary_key)

ins = insert(db.cookies).values(
    cookie_name="chocolate chip",
    cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
    cookie_sku="CC01",
    quantity="12",
    unit_cost="0.50"
)

result = db.engine.execute(ins)
print(result.inserted_primary_key)

ins = db.cookies.insert()
result = db.engine.execute(
    ins,
    cookie_name="chocolate chip",
    cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
    cookie_sku="CC01",
    quantity="12",
    unit_cost="0.50"
)

result = db.engine.execute(ins)
print(result.inserted_primary_key)

inventory_list = [
    {
        'cookie_name': 'peanut butter',
        'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html',
        'cookie_sku': 'PB01',
        'quantity': '24',
        'unit_cost': '0.25'
    },
    {
        'cookie_name': 'oatmeal raisin',
        'cookie_recipe_url': 'http://some.okay.me/cookie/raisin.html',
        'cookie_sku': 'EWW01',
        'quantity': '100',
        'unit_cost': '1.00'
    }
]
result = db.engine.execute(ins, inventory_list)

SELECT

execute вернет объект ResultProxy
Если получены не все строки, то лучше явно закрыть соединения методом close().
ResultProxy имеет итератор и чаще всего самое эффективное использовать именно его, но есть и другие методы:
fetchall()
fetchmany(size=None)
fetchone()
first()
scalar()

print('-' * 10)
s = select([db.cookies])
rp = db.engine.execute(s)
for row in rp:
    print(row)

print('-' * 10)
s = db.cookies.select()
rp = db.engine.execute(s)
results = rp.fetchall()
for row in results:
    print(row)

print('-' * 10)
first_row = results[0]
print(first_row)
print(first_row[1])
print(first_row.cookie_name)
print(first_row[db.cookies.c.cookie_name])

Ordering

print('-' * 10)
# ORDER BY
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
rp = engine.execute(s)
for cookie in rp:
    print('{} - {}'.format(cookie.quantity, cookie.cookie_name))

print('-' * 10)
# ORDER BY DESC
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(desc(cookies.c.quantity))
rp = engine.execute(s)
for cookie in rp:
    print('{} - {}'.format(cookie.quantity, cookie.cookie_name))

Limiting

print('-' * 10)
# LIMIT 2 OFFSET 0
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
s = s.limit(2)
rp = engine.execute(s)
print([result.cookie_name for result in rp])

SQL functions

    print('-' * 10)
    # SUM()
    s = select([func.sum(cookies.c.quantity)])
    rp = engine.execute(s)
    print(rp.scalar())

    print('-' * 10)
    # COUNT()
    s = select([func.count(cookies.c.cookie_name)])
    rp = engine.execute(s)
    record = rp.first()
    print(record.keys())
    print(record.count_1)

    print('-' * 10)
    # COUNT() as
    s = select([func.count(cookies.c.cookie_name).label('inventory_count')])
    rp = engine.execute(s)
    record = rp.first()
    print(record.keys())
    print(record.inventory_count)

Filtering

    print('-' * 10)
    # WHERE name = ''
    s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')
    rp = engine.execute(s)
    record = rp.first()
    print(record.items())

    print('-' * 10)
    # LIKE '%%'
    s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%'))
    rp = engine.execute(s)
    for record in rp.fetchall():
        print(record.cookie_name)

Conjunctions

print('-' * 10)
# quantity > 23 AND cookies.unit_cost < 0.40
s = select([cookies]).where(
    and_(
        cookies.c.quantity > 23,
        cookies.c.unit_cost < 0.40
    )
)
for row in engine.execute(s):
    print(row.cookie_name)

print('-' * 10)
#  BETWEEN 10 AND 50 OR (cookie_name LIKE '%chip%')
s = select([cookies]).where(
    or_(
        cookies.c.quantity.between(10, 50),
        cookies.c.cookie_name.contains('chip')
    )
)
for row in engine.execute(s):
    print(row.cookie_name)

Update

print('-' * 10)
# SET quantity = quantity + 120 WHERE cookie_name == 'chocolate chip'
u = update(cookies).where(cookies.c.cookie_name == "chocolate chip")
u = u.values(quantity=(cookies.c.quantity + 120))
result = engine.execute(u)
print(result.rowcount)  # кол-во измененных строк
s = select([cookies]).where(cookies.c.cookie_name == "chocolate chip")
result = engine.execute(s).first()
for key in result.keys():
    print('{:>20}: {}'.format(key, result[key]))

Delete

u = delete(cookies).where(cookies.c.cookie_name == "dark chocolate chip")
result = engine.execute(u)
print(result.rowcount)
s = select([cookies]).where(cookies.c.cookie_name == "dark chocolate chip")
result = engine.execute(s).fetchall()
print(len(result))