一個超方便使用 SQL 的 Python 神器

語言: CN / TW / HK

其實一開始用的是pymysql,但是發現維護比較麻煩,還存在程式碼注入的風險,所以就乾脆直接用ORM框架。

ORM即Object Relational Mapper,可以簡單理解為資料庫表和Python類之間的對映,通過操作Python類,可以間接操作資料庫。

Python的ORM框架比較出名的是SQLAlchemy和Peewee,這裡不做比較,只是單純講解個人對SQLAlchemy的一些使用,希望能給各位朋友帶來幫助。

  • sqlalchemy版本: 1.3.15
  • pymysql版本: 0.9.3
  • mysql版本: 5.7

初始化工作
一般使用ORM框架,都會有一些初始化工作,比如資料庫連線,定義基礎對映等。

以MySQL為例,建立資料庫連線只需要傳入DSN字串即可。其中echo表示是否輸出對應的sql語句,對除錯比較有幫助。

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://$user:[email protected]$host:$port/$db?charset=utf8mb4', echo=True)

個人設計
對於我個人而言,引進ORM框架時,我的專案會參考MVC模式做以下設計。其中model儲存的是一些資料庫模型,即資料庫表對映的Python類;model_op儲存的是每個模型對應的操作,即增刪查改;呼叫方(如main.py)執行資料庫操作時,只需要呼叫model_op層,並不用關心model層,從而實現解耦。

├── main.py
├── model
│   ├── __init__.py
│   ├── base_model.py
│   ├── ddl.sql
│   └── py_orm_model.py
└── model_op
    ├── __init__.py
    └── py_orm_model_op.py

對映宣告(Model介紹)
舉個栗子,如果我們有這樣一張測試表

create table py_orm (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一id',
    `name` varchar(255) NOT NULL DEFAULT '' COMMENT '名稱',
    `attr` JSON NOT NULL COMMENT '屬性',
    `ct` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `ut` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB COMMENT '測試表';

在ORM框架中,對映的結果就是下文這個Python類

# py_orm_model.py
from .base_model import Base
from sqlalchemy import Column, Integer, String, TIMESTAMP, text, JSON


class PyOrmModel(Base):
    __tablename__ = 'py_orm'

    id = Column(Integer, autoincrement=True, 
                primary_key=True, comment='唯一id')
    name = Column(String(255), nullable=False, 
                  default='', comment='名稱')
    attr = Column(JSON, nullable=False, comment='屬性')
    ct = Column(TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='建立時間')
    ut = Column(TIMESTAMP, nullable=False, 
                server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
                comment='更新時間')

首先

我們可以看到PyOrmModel繼承了Base類,該類是sqlalchemy提供的一個基類,會對我們宣告的Python類做一些檢查,我將其放在base_model中。

# base_model.py
# 一般base_model做的都是一些初始化的工作

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine("mysql+pymysql://root:[email protected]:33306/orm_test?charset=utf8mb4", echo=False)

其次

每個Python類都必須包含__tablename__屬性,不然無法找到對應的表。

第三

關於資料表的建立有兩種方式,第一種當然是手動在MySQL中建立,只要你的Python類定義沒有問題,就可以正常操作;第二種是通過orm框架建立,比如下面

# main.py
# 注意這裡的匯入路徑,Base建立表時會尋找繼承它的子類,如果路徑不對,則無法建立成功

from sqlachlemy_lab import Base, engine

if __name__ == '__main__':
    Base.metadata.create_all(engine)

建立效果:

...
2020-04-04 10:12:53,974 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE py_orm (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(255) NOT NULL DEFAULT '' COMMENT '名稱', 
    attr JSON NOT NULL COMMENT '屬性', 
    ct TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    ut TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (id)
)

第四

關於欄位屬性

1.primary_key和autoincrement比較好理解,就是MySQL的主鍵和遞增屬性。
2.如果是int型別,不需要指定長度,而如果是varchar型別,則必須指定。
3.nullable對應的就是MySQL中的NULL 和 NOT NULL
4.關於default和server_default: default代表的是ORM框架層面的預設值,即插入的時候如果該欄位未賦值,則會使用我們定義的預設值;server_default代表的是資料庫層面的預設值,即DDL語句中的default關鍵字。

Session介紹
在SQLAlchemy的文件中提到,資料庫的增刪查改是通過session來執行的。

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

session = Session()
orm = PyOrmModel(id=1, name='test', attr={})
session.add(orm)

session.commit()
session.close()

如上,我們可以看到,對於每一次操作,我們都需要對session進行獲取,提交和釋放。這樣未免過於冗餘和麻煩,所以我們一般會進行一層封裝。

1.採用上下文管理器的方式
處理session的異常回滾和關閉,這部分與所參考的文章是幾乎一致的。

# base_model.py
from contextlib import contextmanager
from sqlalchemy.orm import sessionmaker, scoped_session

def _get_session():
    """獲取session"""
    return scoped_session(sessionmaker(bind=engine, expire_on_commit=False))()

# 在這裡對session進行統一管理,包括獲取,提交,回滾和關閉
@contextmanager
def db_session(commit=True):
    session = _get_session()
    try:
        yield session
        if commit:
            session.commit()
    except Exception as e:
        session.rollback()
        raise e
    finally:
        if session:
            session.close()

2.model和dict轉換
在PyOrmModel中增加兩個方法,用於model和dict之間的轉換

class PyOrmModel(Base):
    ...

    @staticmethod
    def fields():
        return ['id', 'name', 'attr']

    @staticmethod
    def to_json(model):
        fields = PyOrmModel.fields()
        json_data = {}
        for field in fields:
            json_data[field] = model.__getattribute__(field)
        return json_data

    @staticmethod
    def from_json(data: dict):
        fields = PyOrmModel.fields()

        model = PyOrmModel()
        for field in fields:
            if field in data:
                model.__setattr__(field, data[field])
        return model

3.資料庫操作的封裝
與參考的文章不同,我是直接呼叫了session,從而使呼叫方不需要關注model層,減少耦合。

# py_orm_model_op.py
from sqlachlemy_lab.model import db_session
from sqlachlemy_lab.model import PyOrmModel


class PyOrmModelOp:
    def __init__(self):
        pass

    @staticmethod
    def save_data(data: dict):
        with db_session() as session:
            model = PyOrmModel.from_json(data)
            session.add(model)

    # 查詢操作,不需要commit
    @staticmethod
    def query_data(pid: int):
        data_list = []
        with db_session(commit=False) as session:
            data = session.query(PyOrmModel).filter(PyOrmModel.id == pid)
            for d in data:
                data_list.append(PyOrmModel.to_json(d))

            return data_list

4.呼叫方

# main.py
from sqlachlemy_lab.model_op import PyOrmModelOp


if __name__ == '__main__':
    PyOrmModelOp.save_data({'id': 1, 'name': 'test', 'attr': {}})

以上就是本次分享的所有內容,想要了解更多歡迎前往公眾號:Python 程式設計學習圈,每日干貨分享