SQLAlchemy

Wprowadzenie do pythonowego ORMa baz danych SQLAlchemy obsługującego wszystkie popularne relacyjne bazy danych (Postgresql, SQLite, MySQL, Oracle, Firebird czy MS-SQL)

SQLAlchemy to pythonowe narzędzie do pracy z bazami danych a także ORM udostępniający programistom aplikacji całą funkcjonalność SQL bez konieczności pisania surowych zapytań. Tam gdzie nie ma Django istnieje spora szansa na znalezienia SQLAlchemy (np. często korzysta się z tego komponentu w aplikacjach opartych o Pyramid czy Pylons, czy Python-Camelot).

Najnowszą wersję pobierzemy ze strony projektu, choć można użyć także pip:

pip install SQLAlchemy

SQLAlchemy składa się z dwóch części - Core i ORM. Rdzeń (core) daje ujednolicone Pythonowe API dostępowe do różnych typów baz danych. Pozwala także na wygodne stosowanie Pythonowych wyrażeń w zapytaniach SQL. ORM to opcjonalny komponent pozwalający opisać za pomocą klas tabele w bazie danych. Pozwala także na manipulowanie danymi poprzez operacje na obiektach (bez pisania zapytań SQL przez samego programistę). Na chwilę obecną wspierane są bazy takie jak: SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird czy Sybase.

Podstawy SQLAlchemy

By połączyć się z wybraną bazą danych musimy użyć create_engine:

from sqlalchemy import create_engine

sqlite_db = create_engine('sqlite:///:memory:')  # in RAM


#sqlite_db = create_engine('sqlite:////absolute/path/to/database.foo', echo=True) # absolute path for SQLite
#sqlite_db = create_engine('sqlite:///db.sqlite', echo=True) # relative path

#mysql_db = create_engine('mysql://localhost/database_name', echo=True)
#pg_db = create_engine('postgres://login:password@localhost:5432/mydatabase', echo=True)

Flaga echo włącza wyświetlanie/logowanie czynności, zapytań jakie SQLAlchemy będzie wykonywać na danej bazie danych. Przydatne przy pisaniu kodu, jak i przy nauce. Powyższy przykład prezentuje różne sposoby łączenia się z bazami danych. Bazy plikowe SQLite wymagają podania ścieżki, można także tworzyć ulotne bazy w pamięci RAM (znikają po zakończeniu działania skryptu). Dla baz takich jak MySQL czy Postgresql podajemy host i nazwę bazy danych oraz opcjonalnie inne parametry.

create_engine zwraca instancję klasy Engine, która reprezentuje tą "rdzeniową" funkcjonalność SQLAlchemy - bez ORMa. Samo użycie create_engine nie powoduje połączenia się z bazą danych. Dzieje się to przy pierwszej próbie wykonania na niej jakiś operacji, np.:

mysql_db = create_engine('mysql://login:hasło@localhost/nazwa_bazy', echo=True)
s = mysql_db.execute("SHOW TABLES;")
print list(s)

Podstawy ORMa

W przypadku użytkowania ORMa każdą tabelę, z którą chcemy pracować musimy opisać klasą. Oto przykład:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

# baza dla klas tabel
Base = declarative_base()

# przykładowa klasa mapująca tabelę z bazy danych
class User(Base):
  __tablename__ = 'users'
  
  # pola i ich typy
  id = Column(Integer, primary_key=True)
  name = Column(String)
  fullname = Column(String)
  password = Column(String)
  
  def __init__(self, name, fullname, password):
      self.name = name
      self.fullname = fullname
      self.password = password

  def __repr__(self):
     return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

Na początku podajemy nazwę tabeli w bazie danych za pomocą __tablename__. Następnie listujemy kolumny tabeli po ich nazwach. Klasa Column przyjmuje wiele argumentów, w tym typy pól, np. tekstowe to String, a liczbowe to Integer. Dla np. MySQL przełoży się to na pola VARCHAR i INTEGER. Opcjonalnie możemy definiować metody takie jak np. __ini__ czy __repr__.

Mamy klasę, która nie jest jeszcze połączona z żadną realną tabelą w istniejącej bazie danych. Nasza klasa "User" to metadane tabeli (metadata) w terminologii SQLAlchemy. Na tych metadanych możemy operować przez Base.metadata. Np. jeżeli tabele nie istnieją to trzeba je stworzyć:

engine = create_engine('sqlite:///:memory:', echo=True)

Base.metadata.create_all(engine)

Mają to za sobą możemy przymierzyć się do dodawania rekordów, np:

ed_user = User('ed', 'Ed Jones', 'edspassword')
print ed_user

Mamy instancję klasy User z podanymi danymi. Jak widać na samym końcu - w zwróconych danych nie będzie ID, gdyż dane nie trafiły jeszcze do bazy danych. By zapisać rekord potrzebujemy obsługi sesji:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
  __tablename__ = 'users'
  
  id = Column(Integer, primary_key=True)
  name = Column(String)
  fullname = Column(String)
  password = Column(String)
  
  def __init__(self, name, fullname, password):
      self.name = name
      self.fullname = fullname
      self.password = password

  def __repr__(self):
     return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)


engine = create_engine('sqlite:///:memory:', echo=True)

Base.metadata.create_all(engine)

# tworzenie sesji dla danej bazy:
Session = sessionmaker(bind=engine)
session = Session()

ed_user = User('ed', 'Ed Jones', 'edspassword')
session.add(ed_user)
# wykonywanie operacji
session.commit()
print  ed_user.id

Zapytania wykonujemy na instancji sesji, np:

for instance in session.query(User).order_by(User.id):
  print instance

W ORMie SQLAlchemy można także tworzyć relacje między obiektami, które przełożone zostaną na relacje w bazie danych - np. jeden do wielu. Oto przykładowa klasa-tabela z relacją do klasy User:

class Address(Base):
  __tablename__ = 'addresses'
  id = Column(Integer, primary_key=True)
  email_address = Column(String, nullable=False)
  user_id = Column(Integer, ForeignKey('users.id'))
  user = relationship("User", backref=backref('addresses', order_by=id))

  def __init__(self, email_address):
      self.email_address = email_address

  def __repr__(self):
      return "<Address('%s')>" % self.email_address

Pole "user_id" ma zdefiowany typ liczbowy oraz dyrektywę ForeignKey ograniczającą wartości tylko do wartości ID klasy-tabeli User. Dyrektywa relationship mówi ORMowi że klasa Address powinna być połączona z klasą User za pomocą atrybutu Address.user. Dodatkowa dyrektywa backref opisuje relację wsteczną (tj. w tym przypadku adresy wywoływane z instancji klasy User). Oto pełen przykład:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

class User(Base):
  __tablename__ = 'users'
  
  id = Column(Integer, primary_key=True)
  name = Column(String)
  fullname = Column(String)
  password = Column(String)
  
  #addresses = relationship("Address", order_by="Address.id", backref="user")
  def __init__(self, name, fullname, password):
      self.name = name
      self.fullname = fullname
      self.password = password

  def __repr__(self):
     return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

class Address(Base):
  __tablename__ = 'addresses'
  id = Column(Integer, primary_key=True)
  email_address = Column(String, nullable=False)
  user_id = Column(Integer, ForeignKey('users.id'))
  user = relationship("User", backref=backref('addresses', order_by=id))

  def __init__(self, email_address):
      self.email_address = email_address

  def __repr__(self):
      return "<Address('%s')>" % self.email_address

engine = create_engine('sqlite:///:memory:', echo=True)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

ed_user = User('ed', 'Ed Jones', 'edspassword')
ed_user.addresses = [
 Address(email_address='jack@google.com'),
 Address(email_address='j25@yahoo.com')]
session.add_all([ed_user,])
session.commit()


for instance in session.query(User).order_by(User.id):
  print '%s - %s' % (instance.name, str(instance.addresses))

Więcej w tutorialu SQLAlchemy.

RkBlog

Programowanie Sieciowe, 14 July 2008, Piotr Maliński