[python] sqlalchemy 閱讀筆記

Visits: 8

SQLAlchemy 閱讀筆記

sqlalchemy 是一個 python 操作資料庫的工具,有三大元件:DBAPI, SQLAlchemy CoreSQLAlchemy Object Relation Mapping (ORM)。 ORM 可以想成是一張 python 產生的資料庫地圖,用於與資料庫溝通、操作的工具,本文主要是紀錄學習筆記。,官方文件在此

前言

sqlalchemy 是一個操作資料庫的工具,透過定義好的 Core (Engine, Schema)產生 mapper,讓 python 程式透過 DBAPI (e.g. psycopg2, cx_oracle, mysql … etc) 與傳統的 RDBMS 進行溝通與操作。 mapper 會把資料庫的 table, column 等架構轉換為 python 物件,透過 method 與 attribute 進行資料庫的 CRUD 操作。

下圖為 sqlalchemy 的架構圖
alt

本文會分成三大段落,個別紀錄 DBAPIsqlalchemy coresqlalchemy ORM。官方建議的學習方式可以從內往外、或是從外往內學皆可

file

DBAPI

file

SQLAlchemy CORE

metadata, DDL

  • Describe the structure of database, i.e. tables, columns, constraints, in terms of data structures in python: 把資料庫結構轉成 python 的資料結構的描述句
  • Can generate to a schema
  • Can be generated from a schema (called reflection.)
    有一些操作資料庫物件的方法,要透過 from sqlalchemy import MetaData, Table, Column, Integer, String, create_engine, String, Numeric, DateTime, Enum, ForeginKey, Unicode… 等

SQL expression

根據 metadata ,產出 SQL Expressin 作為 python objects 與資料庫溝通的工具

SQLAlchemy ORM

以 ORM 操作資料庫可分為三個部分

  • Table: 代表資料庫的資料表
  • python class: 與資料表對映的
  • mapper: 將1與2對映的物件
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine

# 宣告對映
Base = declarative_base()

class User(Base):  # 此為 2. python class  -> 通常會放在 models.py 裡面
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(60), nullable=False)

# 連結SQLite3資料庫example.db
engine = create_engine('sqlite:///example.db')

# 建立Schema
Base.metadata.create_all(engine)  # 相當於Create Table

The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a system that transparently synchronizes all changes in state between objects and their related rows, called a unit of work, as well as a system for expressing database queries in terms of the user defined classes and their defined relationships between each other.

sqlalchemy ORM 提供一種方法來關聯使用者自定義的 python class 與資料庫裡面的資料表,開發者用操作 python class 的方式(argument, method) 來操作資料庫。

The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the database, which is then used to emit the SQL. When using the ORM, we typically don’t use the Engine directly once created; instead, it’s used behind the scenes by the ORM as we’ll see shortly.

在 ORM 的使用情境中,通常不會執行 engine.execute() 或 engine.connect() -> 這是在 SQL Expression Language 的情境上使用的。

When using the ORM, the configurational process starts by describing the database tables we’ll be dealing with, and then by defining our own classes which will be mapped to those tables. In modern SQLAlchemy, these two tasks are usually performed together, using a system known as Declarative, which allows us to create classes that include directives to describe the actual database table they will be mapped to.

通常在操作 ORM 的第一步,是把資料庫的資料表描述/宣告(declarative)為 python class ,也就是透過 sqlalchemy.ext.declarative.declarative_base() 把資料表的 schema 轉為 python class。

Declare a Mapping

Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base – this is known as the declarative base class. Our application will usually have just one instance of this base in a commonly imported module. We create the base class using the declarative_base() function, as follows:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()  # Create a delclarative_base() class named Base

class User(Base):  # 創建 User 這張資料表,繼承自 Base 這個 declarative_base() class
    __tablename__ = 'users'  # 設定資料表名稱

    id = Column(Integer, primary_key=True)  # 設定資料表 schema
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def __repr__(self):
    return f"<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>"

A class using Declarative at a minimum needs a __tablename__ attribute, and at least one Column which is part of a primary key 1. SQLAlchemy never makes any assumptions by itself about the table to which a class refers, including that it has no built-in conventions for names, datatypes, or constraints. But this doesn’t mean boilerplate is required; instead, you’re encouraged to create your own automated conventions using helper functions and mixin classes, which is described in detail at Mixin and Custom Base Classes.

一張資料表(python class) 最少要有 __tablename__ 這個屬性以及至少一欄有 PK 的欄位

When our class is constructed, Declarative replaces all the Column objects with special Python accessors known as descriptors; this is a process known as instrumentation. The “instrumented” mapped class will provide us with the means to refer to our table in a SQL context as well as to persist and load the values of columns from the database.
The MetaData is a registry which includes the ability to emit a limited set of schema generation commands to the database. As our SQLite database does not actually have a users table present, we can use MetaData to issue CREATE TABLE statements to the database for all tables that don’t yet exist. Below, we call the MetaData.create_all() method, passing in our Engine as a source of database connectivity. We will see that special commands are first emitted to check for the presence of the users table, and following that the actual CREATE TABLE statement:

可以透過 Base.metadata.create_all(目標engine),把目標 engine 建立你現在 Base 裡面有的 class,換句話說,就是透過這樣的指令完成 create table schema…的指令。

Create a Schema

>>> Base.metadata.create_all(engine)
SELECT ...
PRAGMA main.table_info("users")
()
PRAGMA temp.table_info("users")
()
CREATE TABLE users (
    id INTEGER NOT NULL, name VARCHAR,
    fullname VARCHAR,
    nickname VARCHAR,
    PRIMARY KEY (id)
)
()
COMMIT

Create an Instance of the Mapped Class

根據已經建好的 mapping object User ,直接創建實體 (init instance) :

ed_user = User(name='ed', fullname='ED Jones', nickname='edsnickname')
print(ed_user.name)  # ed
print(ed_user.nickname)  # edsnickname
print(ed_user.id)  # None

可以發現,即使沒有指派 id 的值,依然會根據 mapped object 的 schema 給定 id=None

Creating a Session

有了 ORM 的物件後,可以開始與 DB 進行溝通了, ORM 與 DB 的溝通是透過 Session 這個類別來進行的。

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)  # 把 DB engine 與 session 綁在一起
session = Session()  # 實體化 Session 這個 class

透過上述指令,可起始化 engine 的 Session ,讓接下來的 connection, execute, 等操作包在一個 session 內,當要實際 connect to DB 的時候,會從 engine 內的 connection pool 抓一個出來建立連線,直到 commit 或 session 關掉為止。

Adding and Updating Objects

新增資料庫:透過 session.add() 來完成

session.add(User(name='ed', fullname='ED Jones', nickname='edsnickname'))
our_user = session.query(User).filter_by(name='ed').first()
print('the result of our_user is: ', our_user)  # the result of our_user is:  <User(name=ed, fullname=ED Jones, nickname=edsnickname)>

session.add(User(name='ed2', fullname='EDJ2', nickname='edn2'))
our_user = session.query(User).filter_by(name='ed2').first()
print('the result of our_user is: ', our_user)  # the result of our_user is:  <User(name=ed2, fullname=EDJ2, nickname=edn2)>

那如果我想要一次新增多筆資料呢?可透過 session.add_all() 來達成:

session.add_all(
    [
        User(name='wendy', fullname='Wendy Williams', nickname='windy'),
        User(name='mary', fullname='Mary Contrary', nickname='mary'),
        User(name='fred', fullname='Fred Flintstone', nickname='freddy')
    ]
)

前面有提到,當 session 尚未 commit ,就不會更動到資料庫,假設我現在想要把這些變動寫到資料庫中,那我就把上述指令通通完成後,多一行 session.commit() 就可以更動資料庫了。而在 commit 前,任何的更動都可透過 session.rollback() 來還原變更。

ed_user = User(name='ed', fullname='ED Jones', nickname='edsnickname')
session.add(ed_user)
our_user = session.query(User).filter_by(name='ed').first()
print('the result of our_user is: ', our_user)

session.add_all([
    User(name='wendy', fullname='Wendy Williams', nickname='windy'),
    User(name='mary', fullname='Mary Contrary', nickname='mary'),
    User(name='fred', fullname='Fred Flintstone', nickname='freddy')
])
print('the result of user is before commit(): ', session.query(User).all())  # the result of user is before commit():  [<User(id=1, name=ed, fullname=ED Jones, nickname=edsnickname)>, <User(id=2, name=wendy, fullname=Wendy Williams, nickname=windy)>, <User(id=3, name=mary, fullname=Mary Contrary, nickname=mary)>, <User(id=4, name=fred, fullname=Fred Flintstone, nickname=freddy)>]

session.rollback()
print('the result of user is after rollback(): ', session.query(User).all())  # the result of user is after rollback():  []

session.commit()
print('the result of user is after commit(): ', session.query(User).all())  # the result of user is after commit():  []

Querying

The Query object is fully generative, meaning that most method calls return a new Query object upon which further criteria may be added. For example, to query for users named “ed” with a full name of “Ed Jones”, you can call filter() twice, which joins criteria using AND:

query object 是 generative 的,意思是每次的 query 後回傳的都是另一個 query object ,可以被持續的增加 filter 條件

for user in session.query(User).filter(User.name == 'ed').filter(User.fullname == 'ED Jones'):
    print(user)  # <User(id=1, name=ed, fullname=ED Jones, nickname=edsnickname)>

Common Filter Operators

query.filter() 中,可使用下列操作子

  • ==: 等於
  • !=: 不等於
  • .like(): 支援萬用字元的 like
  • .ilike(): 忽略大小寫的 like
  • .in_() / .notin_(): 同 SQL 的 in / not in 用法
  • .is_() / .isnot(): 同 SQL 的 is null / is not null 用法
  • and_(): 同 SQL 的 AND ,但要 from sqlalchemy imnport and_
  • or_(): 同 SQL 的 OR ,但要 from sqlalchemy imnport or_

Returning Lists and Scalars

這邊說明 query object 的回傳方式與回傳的資料型別

  • Query.all(): 回傳 list ,要注意的是若有重複值(例如 id=7 有兩筆),只會回傳一次
  • Query.first(): 回傳 class
  • Query.one(): 只會回傳只有一列的資料,若有多列或沒有資料都會 raise error
  • Query.one_or_none(): 類似 .one() 的 funciton ,但只有遇到多列資料的情況會 raise error
session.add_all([
    User(name='wendy', fullname='Wendy Williams', nickname='windy'),
    User(name='mary', fullname='Mary Contrary', nickname='mary'),
    User(name='fred', fullname='Fred Flintstone', nickname='freddy')
])
print('type of User is: ', type(session.query(User).first()))  # type of User is:  <class '__main__.User'>
print('type of User is: ', type(session.query(User).all()))  # type of User is:  <class 'list'>
print('type of User is: ', type(session.query(User).one()))
  # Traceback (most recent call last):
  ...
  # sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one_or_none()

Building a Relationship

現在要練習的是跟剛剛建立的 User class 作關聯:以 Address class 為示範,考慮建立一個使用者與電子信箱地址的一對多關係:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

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", back_populates="addresses")  # Address.user 可與 User.address 彼此關聯

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

User.addresses = relationship(
    "Address", order_by=Address.id, back_populates="user")  # 在 User class 新增屬性 address: User.address 可關聯到 Address.user

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

You may also like these