Рубрики
Без рубрики

Шпаргалка по SQLAlchemy

Эта шпаргалка поможет вам хорошо понять SQLAlchemy.

Автор оригинала: Sheena.

Вступление

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