Sqlalchemy

Установка

Ядро

pip install sqlalchemy

Драйвер для postgres, mysql

pip install psycopg2

pip  install  pymysql

Подключение

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://username:password@localhost:5432/mydb')
engine = create_engine('mysql+pymysql://cookiem:chip@mysql01.com/cookies', pool_recycle=3600)
engine = create_engine('sqlite:///cookies.db')
engine2 = create_engine('sqlite:///:memory:')

Доп. ключи через запятую:

echo	булево. Лог запросов. По-умолчанию False.
encoding	строка. По-умолчанию utf-8
isolation_level
pool_recycle	число секунд для переподключения, желательно выставлять 3600. При работе с mysql соединение может быть активным до 4 часов.

Создание engine не создает фактического соединения с БД.

connection = engine.connect()

Представление структуры данных

Категории типов:

Generic

SQLAlchemy Python SQL

BigInteger int BIGINT

Boolean bool BOOLEAN or SMALLINT

Date datetime.date DATE (SQLite: STRING)

DateTime datetime.datetime DATETIME (SQLite: STRING)

Enum str ENUM or VARCHAR

Float float or Decimal FLOAT or REAL

Integer int INTEGER

Interval datetime.timedelta INTERVAL or DATE from epoch

LargeBinary byte BLOB or BYTEA

Numeric decimal.Decimal NUMERIC or DECIMAL

Unicode unicode UNICODE or VARCHAR

Text str CLOB or TEXT

Time datetime.time DATETIME

SQL standard

Vendor specific

Расположены в модуле sqlalchemy.dialects

from sqlalchemy.dialects.postgresql import JSON

User defined

Метаданные - хранят структуру таблиц

from sqlalchemy import MetaData

metadata = MetaData()

metadata.create_all(engine)

Пример создания таблицы

from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey

cookies = Table('cookies', metadata,

Column('cookie_id', Integer(), primary_key=True),

Column('cookie_name', String(50), index=True),

Column('cookie_recipe_url', String(255)),

Column('cookie_sku', String(55)),

Column('quantity', Integer()),

Column('unit_cost', Numeric(12, 2))

)

Ключи и ограничения:

from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint

PrimaryKeyConstraint('user_id', name='user_pk') - позволяет добавлять на созданную таблицу характеристику для столбца primary key

UniqueConstraint('username', name='uix_username') - добавляет свойство уникальных записей в пределах столбца

CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive') - дополнительные ограничения на значения

Индексы:

from sqlalchemy import Index

Index('ix_cookies_cookie_name', 'cookie_name') - индексирует несколько столбцов

Внешние связи:

from sqlalchemy import ForeignKey

orders = Table('orders', metadata,

Column('order_id', Integer(), primary_key=True),

Column('user_id', ForeignKey('users.user_id')),

Column('shipped', Boolean(), default=False)

)

line_items = Table('line_items', metadata,

Column('line_items_id', Integer(), primary_key=True),

Column('order_id', ForeignKey('orders.order_id')),

Column('cookie_id', ForeignKey('cookies.cookie_id')),

Column('quantity', Integer()),

Column('extended_cost', Numeric(12, 2))

)

Связь для поля order_id:

ForeignKeyConstraint(['order_id'], ['orders.order_id'])

============================================================Работа с данными=============================================================

INSERT

Вариант 1:

ins = 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 = connection.execute(ins)

print(result.inserted_primary_key)

Вариант 2:

from sqlalchemy import insert

ins = insert(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"

)

Вариант 3

ins = cookies.insert()

result = connection.execute(

ins,

cookie_name='dark chocolate chip',

cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',

cookie_sku='CC02',

quantity='1',

unit_cost='0.75'

)

result.inserted_primary_key

Вариант 4

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 = connection.execute(ins, inventory_list)

SELECT

Вариант 1

from sqlalchemy.sql import select

s = select([cookies])

rp = connection.execute(s)

results = rp.fetchall()

Вариант 2

s = cookies.select()

rp = connection.execute(s)

results = rp.fetchall()

Вариант 3

s = cookies.select()

rp = connection.execute(s)

for record in rp:

print(record.cookie_name)

SELECT определенных столбцов

s = select([cookies.c.cookie_name, cookies.c.quantity])

ORDERING

s = select([cookies.c.cookie_name, cookies.c.quantity])

s = s.order_by(cookies.c.quantity) - прямая сортировка

или s = s.order_by(desc(cookies.c.quantity)) - обратная сортировка

rp = connection.execute(s)

LIMITING

s = select([cookies.c.cookie_name, cookies.c.quantity])

s = s.order_by(cookies.c.quantity)

s = s.limit(2)

rp = connection.execute(s)

Встроенные функции SQL

Сумма: func.sum

from sqlalchemy.sql import func

s = select([func.sum(cookies.c.quantity)])

rp = connection.execute(s)

print(rp.scalar())

Количество: func.count

s = select([func.count(cookies.c.cookie_name)])

rp = connection.execute(s)

record = rp.first()

print(record.keys()) - ключи могут быть разные.

print(record.count_1)

При помощи label можно определить название свойства

s = select([func.count(cookies.c.cookie_name).label('inventory_count')])

rp = connection.execute(s)

record = rp.first()

print(record.keys())

print(record.inventory_count)

WHERE

Их можно комбинировать, используется AND

s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')

rp = connection.execute(s)

Варианты модификаторов:

Метод Описание

between(cleft, cright) Значение столбца между cleft и cright

concat(column_two) Объединение column и column_two

distinct() Находит только уникальные значения в столбце

in_([list]) Только если значения столбца в списке

is_(None) Проверка на пустые значения

contains(string) Значение столбца содержит строку

endswith(string) Оканчивается строкой, зависит от больших букв

like(string) зависит от больших букв

startswith(string) зависит от больших букв

ilike(string) не зависит от больших букв

Есть отрицательные модификаторы not<method>, исключение - метод isnot()

Пример для LIKE

s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%'))

rp = connection.execute(s)

for record in rp.fetchall():

print(record.cookie_name)

Можно модифицировать полученные данные по шаблону

Вариант 1

s = select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku]) - добавит строку 'SKU-' к каждому значению столбца

Вариант 2 - через функцию cast

from sqlalchemy import cast

s = select([cookies.c.cookie_name,

cast((cookies.c.quantity * cookies.c.unit_cost),

Numeric(12,2)).label('inv_cost')])

for row in connection.execute(s):

print('{} - {}'.format(row.cookie_name, row.inv_cost))

Логические функции:

from sqlalchemy import and_, or_, not_

s = select([cookies]).where(

and_(

cookies.c.quantity > 23,

cookies.c.unit_cost < 0.40

)

извлечение данных

first_row = results[0]

first_row[1]

first_row.cookie_name

first_row[cookies.c.cookie_name]

Для rp есть следующие варианты, однако без limit() извлекаются все данные, затем одна строка:

first() - Первая запись и закрытие соединения

fetchone() - Одна запись и оставляет открытый курсор для последующих запросов

scalar() - Одно значение если запрос возвращает одно значение в одной строке

rp.keys() - список столбцов

Обновление данных

from sqlalchemy import update

u = update(cookies).where(cookies.c.cookie_name == "chocolate chip")

u = u.values(quantity=(cookies.c.quantity + 120))

result = connection.execute(u)

Удаление данных

from sqlalchemy import delete

u = delete(cookies).where(cookies.c.cookie_name == "dark chocolate chip")

result = connection.execute(u)

JOIN

columns = [orders.c.order_id, users.c.username, users.c.phone,

cookies.c.cookie_name, line_items.c.quantity,

line_items.c.extended_cost]

cookiemon_orders = select(columns)

cookiemon_orders = cookiemon_orders.select_from(orders.join(users).join(

line_items).join(cookies)).where(users.c.username ==

'cookiemon')

result = connection.execute(cookiemon_orders).fetchall()

OUTER JOIN:

Для получения обратной статистики

columns = [users.c.username, func.count(orders.c.order_id)]

all_orders = select(columns)

all_orders = all_orders.select_from(users.outerjoin(orders))

all_orders = all_orders.group_by(users.c.username)

result = connection.execute(all_orders).fetchall()

Есть поддержка ALIAS

Группировка данных:

columns = [users.c.username, func.count(orders.c.order_id)]

all_orders = select(columns)

all_orders = all_orders.select_from(users.outerjoin(orders))

all_orders = all_orders.group_by(users.c.username)

result = connection.execute(all_orders).fetchall()

Объединение запросов по условию

def get_orders_by_customer(cust_name, shipped=None, details=False):

columns = [orders.c.order_id, users.c.username, users.c.phone]

joins = users.join(orders)

if details:

columns.extend([cookies.c.cookie_name, line_items.c.quantity,

line_items.c.extended_cost])

joins = joins.join(line_items).join(cookies)

cust_orders = select(columns)

cust_orders = cust_orders.select_from(joins)

34 | Chapter 2: Working with Data via SQLAlchemy Core

cust_orders = cust_orders.where(users.c.username == cust_name)

if shipped is not None:

cust_orders = cust_orders.where(orders.c.shipped == shipped)

result = connection.execute(cust_orders).fetchall()

return result

get_orders_by_customer('cakeeater')

get_orders_by_customer('cakeeater', details=True)

get_orders_by_customer('cakeeater', shipped=True)

get_orders_by_customer('cakeeater', shipped=False)

get_orders_by_customer('cakeeater', shipped=False, details=True)

Сырые запросы

result = connection.execute("select * from orders").fetchall()

print(result)