A super convenient Python artifact for using SQL!

Background

In fact, I used pymysql at the beginning, but found that maintenance was troublesome and there was a risk of code injection, so I simply used the ORM framework directly.

ORM is Object Relational Mapper, which can be simply understood as the mapping between database tables and Python classes. By operating Python classes, you can indirectly operate the database.

The more famous Python ORM frameworks are SQLAlchemy and Peewee. I will not make a comparison here, but simply explain some of my personal use of SQLAlchemy. I hope it can be helpful to all my friends.

• sqlalchemy version: 1.3.15
• pymysql version: 0.9.3
• mysql version: 5.7

Initialization work

Generally, when using an ORM framework, there will be some initialization work, such as database connection, defining basic mapping, etc.

Take MySQL as an example. To create a database connection, you only need to pass in the DSN string. Echo indicates whether to output the corresponding SQL statement, which is helpful for debugging.

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://$user:$password@$host:$port/$db?charset=utf8mb4', echo=True)

Personal design

For me personally, when introducing the ORM framework, my project will refer to the MVC pattern for the following design. Among them, model stores some database models, that is, Python classes mapped to database tables; model_op stores the operations corresponding to each model, that is, adding, deleting, checking, and modifying; when the caller (such as main.py) performs database operations, it only needs to call model_op layer, you don’t need to care about the model layer to achieve decoupling.

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

Mapping Statement (Model Introduction)

For example, if we have such a test table.

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 '测试表';

In the ORM framework, the mapping result is the Python class below.

# 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='更新时间')

First of all, we can see that PyOrmModel inherits the Base class, which is a base class provided by sqlalchemy. It will do some checks on the Python class we declared, and I put it in 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:123456@127.0.0.1:33306/orm_test?charset=utf8mb4", echo=False)

Secondly, each Python class must contain the __tablename__ attribute, otherwise the corresponding table cannot be found.

Third, there are two ways to create a data table. The first is of course to create it manually in MySQL. As long as there is no problem with your Python class definition, it can operate normally; the second is to use ORM Frame creation, such as below.

# main.py
# 注意这里的导入路径，Base创建表时会寻找继承它的子类，如果路径不对，则无法创建成功
from sqlachlemy_lab import Base, engine
if __name__ == '__main__':
Base.metadata.create_all(engine)

Creation effect:

...
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)
)

Fourth, about field attributes:

1.primary_key and autoincrement are easier to understand, they are the primary key and incremental attributes of MySQL.

2. If it is an int type, you do not need to specify the length, but if it is a varchar type, you must specify it.

3.nullable corresponds to NULL and NOT NULL in MySQL

4.About default and server_default: default represents the default value at the ORM framework level, that is, if the field is inserted when inserting If no value is assigned, the default value we defined will be used; server_default represents the default value at the database level, which is the default keyword in the DDL statement.

Session Introduction

It is mentioned in the SQLAlchemy documentation that the addition, deletion, and modification of the database are performed through the 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()

As above, we can see that for each operation, we need to acquire, submit and release the session. This is too redundant and troublesome, so we usually perform a layer of encapsulation.

1. Use the context manager to handle abnormal rollback and closure of the session. This part is almost consistent with the referenced article.

# 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. Add two methods to PyOrmModel for conversion between model and 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. Encapsulation of database operations. Different from the reference article, I directly called the session, so that the caller does not need to pay attention to the model layer and reduces coupling.

# 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. Caller:

# main.py
from sqlachlemy_lab.model_op import PyOrmModelOp
if __name__ == '__main__':
PyOrmModelOp.save_data({'id': 1, 'name': 'test', 'attr': {}})

The above is the detailed content of A super convenient Python artifact for using SQL!. For more information, please follow other related articles on the PHP Chinese website!