Вступление
SQLAlchemy-это глубокая и мощная вещь, состоящая из многих слоев. Эта шпаргалка прилипает к частям слоя ORM (Object Relational Mapper) и предназначена для использования в качестве ссылки,а не учебника. Тем не менее, если вы знакомы с SQL, то эта шпаргалка должна помочь вам хорошо понять SQLAlchemy.
Основные модели
Одна модель используется для описания одной таблицы базы данных. Например:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session,sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy import (
Column,
Integer,
String,
Boolean,
ForeignKey,
DateTime,
Sequence,
Float
)
import datetime
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
class Book(Base): #<-------------------------
__tablename__ = "books" #matches the name of the actual database table
id = Column(Integer,Sequence('book_seq'),primary_key=True) # plays nice with all major database engines
name = Column(String(50)) # string column need lengths
author_id = Column(Integer,ForeignKey('authors.id')) # assumes there is a table in the database called 'authors' that has an 'id' column
price = Column(Float)
date_added = Column(DateTime, default=datetime.datetime.now) # defaults can be specified as functions
promote = Column(Boolean,default=False) # or as values
Запросы и взаимодействия
Выбор и фильтрация
#fetch everything
lBooks = DBSession.query(Book) #returns a Query object.
for oBook in lBooks:
print oBook.name
#simple filters
lBooks = DBSession.query(Book).filter_by(author_id=1) #returns all the books for a specific author
#more complex filters
lBooks = DBSession.query(Book).filter(Book.price<20) #returns all the books with price <20. Note we use filter, not filter_by
#filters can be combined
lBooks = DBSession.query(Book).filter_by(author_id=1).filter(Book.price<20) #all books by a specific author, with price<20
#logical operations can be used in filters
from sqlalchemy import or_
lBooks = DBSession.query(Book).filter(or_(Book.price<20,promote==True)) # returns all books that cost less than 20 OR are being promoted
#ordering
from sqlalchemy import desc
DBSession.query(Book).order_by(Book.price) #get all books ordered by price
DBSession.query(Book).order_by(desc(Book.price)) #get all books ordered by price descending
#other useful things
DBSession.query(Book).count() #returns the number of books
DBSession.query(Book).offset(5) #offset the result by 5
DBSession.query(Book).limit(5) # return at most 5 books
DBSession.query(Book).first() #return the first book only or None
DBSession.query(Book).get(8) #return the Book with primary key = 8, or None
Отношения
Отношения между таблицами SQL описываются в терминах отношений внешних ключей. В приведенном примере таблица books имеет поле внешнего ключа, указывающее на поле id таблицы authors. SQLAlchemy делает использование и изучение этих отношений довольно простым.
Отношения один ко многим
Предположим, мы следим за книгами разных авторов. Один автор может иметь много книг.
class Book(Base):
__tablename__ = "books" #matches the name of the actual database table
id = Column(Integer,Sequence('book_seq'),primary_key=True)
name = Column(String(50))
author_id = Column(Integer,ForeignKey('authors.id'))
author = relationship("Author",backref="books") # <-----------------------
class Author(Base):
__tablename__ = "books" #matches the name of the actual database table
id = Column(Integer,Sequence('book_seq'),primary_key=True)
name = Column(String(50))
Отмеченная линия настраивает отношения между моделями. Обратите внимание, что "Автор" – это строка. Это не обязательно должно быть, это также может быть класс. Использование строки здесь устраняет возможность определенной ошибки имени. Обратите внимание, что связь настроена в обоих направлениях в одной строке. Автор книги доступен через атрибут author , а книги автора доступны через атрибут books автора.
Вот несколько способов, которыми вы можете использовать отношения после их настройки:
oBook = DBSession.query(Book).filter_by(name="Harry Potter and the methods of rationality").first()
oAuthor = oBook.author # oAuthor is now an Author instance. oAuthor.id == oBook.author_id
#it works the other way as well
oAuthor = DBSession.query(Author).filter_by(name="Orsan Scott Card")
for oBook in oAuthor.books:
print oBook.name
#adding a new book
oNewBook = Book()
oBook.name = "Ender's Game"
oBook.author = oAuthor
#adding a new book in a different way...
oNewBook = Book()
oBook.name = "Ender's Shadow"
oAuthor.books.append(oBook)
Отношения один к одному
class Parent(Base):
__tablename__ = 'parent'
id = ColumnColumn(Integer,Sequence('p_seq'),primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", backref=backref("parent", uselist=False)) # <------
class Child(Base):
__tablename__ = 'child'
id = Column(Integer,Sequence('c_seq'),primary_key=True)
Обратите внимание, что строка, которая настраивает связь, содержит выражение, а не просто строку для аргумента backref. Если бы использовалась строка "родитель" , то это было бы нормальное отношение “много к одному”. Мы можем использовать это так:
oChild = DBSession.query(Child).get(1) oParent = oChild.parent oParent2 = Parent() oParent.child = Child()
Многие ко многим отношениям
Отношение “многие ко многим” требует дополнительной таблицы для создания сопоставлений между строками. Есть два способа сделать это:
Во-первых, просто использование моделей:
class Category(Base):
__tablename__ = 'categories'
id = Column(Integer,Sequence('cat_seq'),primary_key=True)
name = Column(String(20))
class Product(Base):
__tablename__ = 'products'
id = Column(Integer,Sequence('prod_seq'),primary_key=True)
name = Column(String(20))
class Map(Base):
__tablename__ = 'map'
id = Column(Integer,Sequence('map_seq'),primary_key=True)
cat_id = Column(Integer,ForeignKey('categories.id'))
prod_id = Column(Integer,ForeignKey('products.id'))
Здесь вы можете указать отношения в классе Map . Преимущество этого подхода заключается в том, что вы можете создать экземпляр класса Map , это полезно, если вы хотите взаимодействовать с объектами Map любым нетривиальным способом.
Этот следующий подход лучше, если ваша таблица карт является только таблицей карт и не требует сложных взаимодействий:
map_table = Table('maps', Base.metadata,
Column('cat_id', Integer, ForeignKey('categories.id')),
Column('prod_id', Integer, ForeignKey('products.id'))
)
class Category(Base):
__tablename__ = 'categories'
id = Column(Integer,Sequence('cat_seq'),primary_key=True)
name = Column(String(20))
products = relationship("Product",
secondary=map_table, # you can also use the string name of the table, "maps", as the secondary
backref="categories")
class Product(Base):
__tablename__ = 'products'
id = Column(Integer,Sequence('prod_seq'),primary_key=True)
name = Column(String(20))
Вы можете использовать эти отношения следующим образом:
#construct a category and add some products to it
oCat = Category()
oCat.name = "Books"
oProduct = Product()
oProduct.name = "Ender's Game - Orsan Scott Card"
oCat.products.append(oProduct)
oProduct = Product()
oProduct.name = "Harry Potter and the methods of Rationality"
oProduct.categories.append(oCat)
# interact with products from an existing category
for oProduct in oCat.products:
print oProduct.name
#interact with categories of an existing product
oProduct = DBSession.query(Product).filter_by(name="")
for oCat in oProduct.categories:
print oCat.name
Самореферентные отношения
Иногда у вас есть таблица с внешним ключом, указывающим на ту же таблицу. Например, предположим, что у нас есть куча узлов в направленном дереве. Узел может иметь много дочерних узлов, но не более одного родительского
class TreeNode(Base):
__tablename__ = 'nodes'
id = Column(Integer,Sequence('node_seq'),primary_key=True)
parent_id = Column(Integer,ForeignKey('nodes.id'))
name = Column(String(20))
children = relationship("TreeNode",
backref=backref('parent', remote_side=[id])
)
Вы можете использовать эти отношения, как и любые отношения “многие к одному”:
#fetch the root node (assume there is one node with no parents)
oRootNode = DBSession.query(TreeNode).filter_by(parent_id=None).first()
#interact with children of existing node
for oChild in oRootNode.children:
print oChild.name
#create new relationships
oParent = TreeNode()
oParent.name = "parent"
oRootNode.children.append(oParent)
oChild = TreeNode()
oChild.name = "Child"
oChild.parent = oParent
Несколько связей с одной и той же таблицей
class WikiPost(Base):
__tablename__ = 'posts'
id = Column(Integer,Sequence('post_seq'),primary_key=True)
name = Column(String(20))
author_id = Column(Integer,ForeignKey('users.id'))
editor_id = Column(Integer,ForeignKey('users.id'))
editor = relationship("User", primaryjoin = "WikiPost.editor_id == User.id",backref="edited_posts")
author = relationship("User", primaryjoin = "WikiPost.author_id == User.id",backref="authored_posts")
class User(Base):
__tablename__ = 'users'
id = Column(Integer,Sequence('usr_seq'),primary_key=True)
name = Column(String(20))
Вы можете взаимодействовать с этим так же, как с двумя отношениями “много к одному”.
oAuthor = DBSession.query(User).filter_by(name="Sheena O'Connell")
#an author writes a post
oPost = WikiPost()
oPost.name = "Sqlalchemy Cheat Sheet"
oPost.author = oAuthor
#later on another user edits it
oEditor = DBSession.query(User).filter_by(name="Yi-Jirr Chen")
oEditor.edited_posts.append(oPost)
#interact with existing relationships
for oPost in oAuthor.authored_posts:
print oPost.name
for oPost in oEditor.edited_posts:
print oPost.name
Конфигурация двигателя
Строки подключения
#the general form of a connection string: `dialect+driver://username:password@host:port/database` #SQLITE: 'sqlite:///:memory:' #store everything in memory, data is lost when program exits 'sqlite:////absolute/path/to/project.db') #Unix/Mac 'sqlite:///C:\\path\\to\\project.db' #Windows r'sqlite:///C:\path\to\project.db' #Windows alternative #PostgreSQL 'postgresql://user:pass@localhost/mydatabase' 'postgresql+psycopg2://user:pass@localhost/mydatabase' 'postgresql+pg8000://user:pass@localhost/mydatabase' #Oracle 'oracle://user:pass@127.0.0.1:1521/sidname' 'oracle+cx_oracle://user:pass@tnsname' #Microsoft SQL Server 'mssql+pyodbc://user:pass@mydsn' 'mssql+pymssql://user:pass@hostname:port/dbname'
Двигатель, Сессия и база
#set up the engine engine = create_engine(sConnectionString, echo=True) #echo=True makes the sql commands issued by sqlalchemy get output to the console, useful for debugging #bind the dbsession to the engine DBSession.configure(bind=engine) #now you can interact with the database if it exists #import all your models then execute this to create any tables that don't yet exist. This does not handle migrations Base.metadata.create_all(engine)
Вывод
Существует гораздо больше способов настройки отношений, гораздо больше способов запроса к базе данных и многие темы, которые эта шпаргалка просто не охватывала. SQLAlchemy довольно огромен, но для многих приложений вам не нужно делать ничего более сложного, чем то, что показано здесь. Если вы пришли сюда, чтобы узнать, как использовать SQLAlchemy, и вы чувствуете, что находитесь в положении, когда вы можете использовать методы, которые я описал здесь, то я бы предложил в качестве следующего шага немного прочитать о том, как использовать сеанс для управления транзакциями.