Вступление
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, и вы чувствуете, что находитесь в положении, когда вы можете использовать методы, которые я описал здесь, то я бы предложил в качестве следующего шага немного прочитать о том, как использовать сеанс для управления транзакциями.