Wstęp
SQLAlchemy jest zestawem pythonowych narzędzi SQL i ORM znacznie pomagającym programiście przy pracy z bazami danych.
Obsługuje następujące bazy danych: MySQL, SQLite, PostgreSQL, Oracle, MS-SQL i Firebird.
Instalacja
Jest kilka sposób instalacji, ze względu na prostotę polecam:
easy_install sqlalchemy
Jeżeli nie korzystasz z konta roota, prawdopodobnie będziesz musiał skorzystać z sudo.
Zastrzegam, że to nie jedyny sposób instalacji (;
ORM
ORM – ang. Object-Relational Mapping co w dosłownym tłumaczeniu znaczy Mapowanie Obiektowo-Relacyjne. Polega to mniej więcej na odwzorowaniu struktury obiektowej na relacyjnej bazie danych.
Pierwszy raz
Mniej więcej wyjaśniliśmy sobie kilka pojęć związanych z SQLAlchemy. Poznaliśmy prostą metodę instalacji i definicję ORM. Posiadając taką wiedzę czas na pokazanie i wytłumaczenie prostego przykładu „w kodzie”. Stwórz plik „przyklad.py„, zamieść w nim zawartość poniższego listingu i zapisz.
# -*- coding: utf-8 -*-
from sqlalchemy import *
db = create_engine('sqlite:///przyklad.db')
db.echo = False # Spróbuj zmienić na true (:. To taki odpowednik "verbose"
metadata = MetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('name', String(255)),
Column('job', Integer),
Column('pass', String(255)),
)
users.create() # Tworzymy tabelę
insert = users.insert()
insert.execute(name='Michal', job: 'Bezrobotny', pass='sekretne')
insert.execute({'name': 'Jan', 'job': 'Handlowiec', 'pass': 'tajne'},
{'name': 'Kuba', 'job': 'Wozny'},
{'name': 'Ola', 'job': 'Handlowiec'}) # Wkładamy dane
select = users.select()
result = select.execute()
wszystko = result.fetchall()
row = result.fetchone()
print 'Id:', row['user_id']
print 'Imie:', row['name']
print 'Praca:', row.job
print 'Haslo:', row['pass']
for row in wszystko:
print row.name, 'pracuje jako', row.job
Aby uruchomić nasz mały program wpisz w konsolę:
pyhton przyklad.py
Abyśmy mogli operwać na tabelach musieliśmy przypisać je do obiektu MetaData(). Teraz dopiero możemy stworzyć tabelę users. W przykładzie pokazałem dwa sposoby dodawania danych do bazy. Pierwszy pozwala na dodanie pojedyńczego obiektu, natomiast za pomocą drugiego sposobu podajemy listę słowników, żeby dodać wiele obiektów na raz. Jeżeli SQLAlchemy w naszych danych znajdzie się jakiś znak specjalny(np. apostrofę lub średnik) to SQLEngine automatycznie je zasleszuje. Dzięki takiej ochronie, atak SQL Injection staje się praktycznie niemożliwy. W SQLAlchemy pobieranie danych nie stanowi żadnego problemu, żeby pobrać jeden wiersz skorzystałem z funkcji fetchone(), natomiast żeby pobrać wszystkie rekordy na raz skorzystałem z fetchall() i wylistowałem je za pomocą pętli for, której obsługa w pythonie jest wyjątkowo prosta. Pewnie zwróciłeś uwagę to to, że korzystamy z sqlite. Jak już pewnie wiesz to nie jedyna baza obsługiwana przez SQLAlchemy. Poniżej przedstawiam sposoby łączenia się z innymi bazami danych:
db = create_engine('postgres://login:haslo@localhost:port/nazwabazydanych') # PostgreSQL
db = create_engine('mysql://login:haslo@localhost:port/nazwabazydanych') #MySQL
db = create_engine('oracle://login:haslo@localhost:port/nazwabazydanych') #Oracle
# Łączenie z bazami danych odbywa się za pomocą pewnego schematu:
# driver://login:haslo@host:port/nazwabazydanych
db = create_engine('sqlite:////bezwzgledna/sciezka/do/bazydanych.db')
db = create_engine('sqlite:///relatywna/sciezka/do/bazydanych.db')
db = create_engine('sqlite://') # baza w pamięci RAM
Pobieranie danych – szczegółowo
Poświęćmy trochę czasu, aby zbadać różne sposoby pobierania danych z bazy przy pomocy SQLAlchemy, które skracają czas pracy i pomagają programiście tworzyć zaawansowane zapytania z klauzulami WHERE, AND, OR itd. Na początek stwórzmy plik „pobieranie.py„.
# -*- coding: utf-8 -*-
from sqlalchemy import *
# Użyjmy tej samej bazy danych
db = create_engine('sqlite:///przyklad.db')
db.echo = True # Robimy true, żeby widzieć co się dzieje
metadata = MetaData(db)
# Tabela users istnieje, ładujemy ją z bazy danych za pomocą "autoload"
users = Table('users', metadata, autoload=True)
def run(stmt):
rs = stmt.execute()
for row in rs:
print row
# Zapytanie z warunkiem jednego pola
q = users.select(users.c.name == 'Ola')
run(q)
q = users.select(users.c.id > 1 )
run(q)
q = users.select(users.c.job == 'Handlowiec'
# AND, OR i NOT
q = users.select(and_(users.c.id < 3, users.c.name != 'Jan'))
run(q)
q = users.select(or_(users.c.id > 1 , users.c.name != 'Jan'))
run(q)
q = users.select(not_(users.c.name == 'Jan'))
run(q)
# równie dobrze możemy korzystać z &, | i ~
q = users.select((users.c.job == 'Handlowiec') & (users.c.name != 'Ola'))
run(q)
q = users.select((users.c.job == 'Handlowiec' | (users.c.name != 'Kuba'))
run(q)
q = users.select(~(users.c.name == 'Michal'))
run(q)
# dostępne są również funkcje "like", "startswith", "endswith"
q = users.select(users.c.name.startswith('K'))
run(q)
q = users.select(users.c.name.like('%a%'))
run(q)
q = users.select(users.c.name.endswith('a'))
run(q)
# ponadto dostępne są "in" i "between"
q = users.select(users.c.id.between(1,3))
run(q)
# aby zapobiec konfliktowi z pythonem dajemy in_
q = users.select(users.c.name.in_('Jan', 'Ola'))
run(q)
# do wywoływanie funkcji SQL służy "func"
q = users.select(func.substr(users.c.name, 3, 1) == 'b')
run(q)
# pobieranie określonych
q = select([users], users.c.job != 'Handlowiec')
run(q)
q = select([users.c.name, users.c.job], users.c.name != 'Michal')
run(q)
# korzystanie z funkcji SQL "count"
q = select([func.count(users.c.user_id)])
run(q)
# korzystanie z funkcji SQL "count", żeby zliczyć wszystko
q = select([func.count("*")], from_obj=[users])
run(q)
Order By, Limit i inne takie
Pobierając dane z bazy, zazwyczaj chcemy żeby były one odpowiednio posortowane. W takich przypadkach z pomocą przychodzą do nas Order by, Limit itp.
# -*- coding: utf-8 -*-
from sqlalchemy import *
# Użyjmy tej samej bazy danych
db = create_engine('sqlite:///przyklad.db')
db.echo = True # Robimy true, żeby widzieć co się dzieje
metadata = MetaData(db)
# Tabela users istnieje, ładujemy ją z bazy danych za pomocą "autoload"
users = Table('users', metadata, autoload=True)
def run(stmt):
rs = stmt.execute()
for row in rs:
print row
# proste wykorzystanie order_by
q = users.select(order_by=[users.c.name])
run(q)
# malejące sortowanie na podstawie id
q = users.select(order_by=[desc(users.c.user_id)])
run(q)
# rosnące sortowanie na podstawie id
q = users.select(order_by=[desc(users.c.user_id)])
run(q)
# sortowanie na wielu kolumnach
q = users.select(users.c.job>'A', order_by=[desc(users.c.user_id), asc(users.c.job)])
# pobieranie unikalnych danych
q = select([users.c.name], distinct=True)
run(q)
# wykorzystanie limit i offset
q = users.select(offset=2, limit=2)
run(q)
Delete
Czasami nie tylko chcemy pobierać dane, ale chcemy je usuwać. Tu z pomocą wkracza obiekt delete().
# -*- coding: utf-8 -*-
from sqlalchemy import *
# Użyjmy tej samej bazy danych
db = create_engine('sqlite:///przyklad.db')
db.echo = True # Robimy true, żeby widzieć co się dzieje
metadata = MetaData(db)
# Tabela users istnieje, ładujemy ją z bazy danych za pomocą "autoload"
users = Table('users', metadata, autoload=True)
def run(stmt):
stmt.execute()
# Usuwamy rekord o id równym 1
q = users.delete(users.c.user_id == 1)
run(q)
# Usuwamy rekordy gdzie zawodem jest "Handlowiec"
q = users.delete(users.c.job == 'Handlowiec')
run(q)
# Usuwanie rekordów dla wielu argumentów
users.delete().execute(
{'name': '%Jan%'},
{'name': '%Ola%'},
{'job': '%wozny%'}
)
Update
Oprócz usuwania często chcemy coś poprawić lub zamienić w naszych rekordach. Pomoże nam w tym obiekt update().
# -*- coding: utf-8 -*-
from sqlalchemy import *
# Użyjmy tej samej bazy danych
db = create_engine('sqlite:///przyklad.db')
db.echo = True # Robimy true, żeby widzieć co się dzieje
metadata = MetaData(db)
# Tabela users istnieje, ładujemy ją z bazy danych za pomocą "autoload"
users = Table('users', metadata, autoload=True)
# Podmieniamy "Kuba" na "Marcin"
users.update(users.c.user_name=='Kuba').execute(user_name='Marcin')
# Update dla wielu argumentów
users.update().execute(
{'user_id':1, 'user_name':'Helena', 'job':'wozna'},
{'user_id':2, 'user_name':'Zdzislaw', 'job':'ksiegowy'},
{'user_id':3, 'user_name':'Maciek', 'job':'sportowiec'},
)
JOIN czyli łączenie tabel
Wielokrotnie spotykamy się z problemem struktury bazy danych. Np. Trzymania w innej tabeli adresów zamieszkania naszych „userów”. Na początek stwórzmy sobie nowy plik „jointest.py” i wypełnij go poniższą zawartością:
# -*- coding: utf-8 -*-
from sqlalchemy import *
# Tworzymy nową bazę danych
db = create_engine('sqlite:///przykladjoin.db')
db.echo = True # Robimy true, żeby widzieć co się dzieje
metadata = MetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('name', String(255)),
Column('job', Integer),
Column('pass', String(255)),
)
users.create() # Tworzymy tabelę users
address = Table('addresses', metadata,
Column('address_id', Integer, primary_key=True),
Column('town', String),
Column('user_id', Integer, ForeignKey('users.user_id')),
)
address.create() # Tworzymy tabelę address
insert = users.insert()
insert.execute(
{'name': 'Jan', 'job': 'Handlowiec', 'pass': 'tajne'},
{'name': 'Kuba', 'job': 'Wozny', 'pass': 'tajne'},
{'name': 'Ola', 'job': 'Handlowiec', 'pass': 'tajne'},
{'name': 'Karolina', 'job': 'Sportowiec', 'pass': 'tajne'},
{'name': 'Marcin', 'job': 'Wozny', 'pass': 'tajne'}
) # Wkładamy dane
insert = address.insert()
insert.execute(
{'town': 'Warsaw', 'user_id': 1},
{'town': 'New York', 'user_id': 2},
{'town': 'London', 'user_id': 3},
{'town': 'Oslo', 'user_id': 4}
) # Wkładamy dane
def run(stmt):
rs = stmt.execute()
for row in rs:
print row
# Spróbujmy trochę nabroić i zróbmy pełny join (:
# ze względu na brak klauzuli WHERE
# program zwróci nam wszystkie możliwe kombinacje rekordów.
q = select([users, address])
run(q)
# Teraz spróbujemy z klauzulą WHERE
# SQL: bla bla WHERE addresses.user_id = users.user_id
q = select([users, address], address.c.user_id == users.c.user_id)
run(q)
# Pobieramy tylko kilka kolumn
q = select([users.c.name, address.c.town], address.c.user_id == users.c.user_id)
run(q)
# sqlalchemy oferuje "mądry" obiekt "join"
# który określa odpowiednie parametry łączenia
# na podstawie "foreign keys" tabel
q = join(users, address).select()
run(q)
# jeżeli chcemy pobrać wszystkich userów
# nawet jeżeli nie mają wpisanego miasta
# to korzystamy z outerjoin w takiej kolejności
q = outerjoin(users, address).select()
run(q)
# kolejność ma znaczenie
# domyślnie outerjoin jest "left outer join"
# to znaczy tabela po lewej i odpowiadające jej wartości z tabeli po prawej
# w ten sposób nie pobierzemy rekordu z "Marcinem"
# ponieważ nie ma jego id w tabeli addresses
q = outerjoin(address, users).select()
run(q)
i wykonaj takie polecenie:
python jointest.py
Obiekt bindparam()
W SQLAlchemy możesz sam „zbindować„(określić) inną nazwę dla istniejącej nazwy jakiejś kolumny i korzystać z takiego „binda” wielokrotnie w swoich zapytaniach do bazy. Brzmi to strasznie, ale wytłumaczenie tego słowami ( przynajmniej dla mnie ) jest stosunkowo trudne, dlatego zapraszam do zajrzenia do kodu:
q = users.select(users.c.name==bindparam('username'))
conn.execute(q, username = 'Jan').fetchall()
q = users.select(users.c.user_id==bindparam('id'))
conn.execute(q, id = 1 ).fetchall()
Mam nadzieję, że jakoś to pojęliście (: Dokumentacja
Union
Union służy do łączenia wyników selectów ( nie pokaże powtarzających się wierszy ):
# -*- coding: utf-8 -*-
from sqlalchemy import *
# Użyjmy tej samej bazy danych
db = create_engine('sqlite:///przykladjoin.db')
db.echo = True # Robimy true, żeby widzieć co się dzieje
metadata = MetaData(db)
# Tabela users istnieje, ładujemy ją z bazy danych za pomocą "autoload"
users = Table('users', metadata, autoload=True)
address = Table('addresses', metadata, autoload=True)
union(
address.select(address.c.street=='New York'),
address.select(address.c.street=='Oslo'),
address.select(address.c.street=='Warsaw'),
order_by=[addresses.c.street]
).execute()
users.select(
users.c.user_id==1
).union_all(
users.select(
users.c.user_id==3
),
order_by=[users.c.user_id]
).execute()
Podsumowanie
SQLAlchemy to narzędzie proste, szybkie i wygodne, przyspieszające pracę programisty, który nie musi się już zamartwiać długimi zapytaniami z JOIN itp. Zdecydowanie POLECAM!( zwłaszcza do pracy z Pylonsem )
Źródła wiedzy
- Dokumentacja SQLAlchemy
- Tutorial nr. 1
- Tutorial nr. 2
- Własne doświadczenia
