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!

Is it enough to learn Python for two hours a day? It depends on your goals and learning methods. 1) Develop a clear learning plan, 2) Select appropriate learning resources and methods, 3) Practice and review and consolidate hands-on practice and review and consolidate, and you can gradually master the basic knowledge and advanced functions of Python during this period.

Key applications of Python in web development include the use of Django and Flask frameworks, API development, data analysis and visualization, machine learning and AI, and performance optimization. 1. Django and Flask framework: Django is suitable for rapid development of complex applications, and Flask is suitable for small or highly customized projects. 2. API development: Use Flask or DjangoRESTFramework to build RESTfulAPI. 3. Data analysis and visualization: Use Python to process data and display it through the web interface. 4. Machine Learning and AI: Python is used to build intelligent web applications. 5. Performance optimization: optimized through asynchronous programming, caching and code

Python is better than C in development efficiency, but C is higher in execution performance. 1. Python's concise syntax and rich libraries improve development efficiency. 2.C's compilation-type characteristics and hardware control improve execution performance. When making a choice, you need to weigh the development speed and execution efficiency based on project needs.

Python's real-world applications include data analytics, web development, artificial intelligence and automation. 1) In data analysis, Python uses Pandas and Matplotlib to process and visualize data. 2) In web development, Django and Flask frameworks simplify the creation of web applications. 3) In the field of artificial intelligence, TensorFlow and PyTorch are used to build and train models. 4) In terms of automation, Python scripts can be used for tasks such as copying files.

Python is widely used in data science, web development and automation scripting fields. 1) In data science, Python simplifies data processing and analysis through libraries such as NumPy and Pandas. 2) In web development, the Django and Flask frameworks enable developers to quickly build applications. 3) In automated scripts, Python's simplicity and standard library make it ideal.

Python's flexibility is reflected in multi-paradigm support and dynamic type systems, while ease of use comes from a simple syntax and rich standard library. 1. Flexibility: Supports object-oriented, functional and procedural programming, and dynamic type systems improve development efficiency. 2. Ease of use: The grammar is close to natural language, the standard library covers a wide range of functions, and simplifies the development process.

Python is highly favored for its simplicity and power, suitable for all needs from beginners to advanced developers. Its versatility is reflected in: 1) Easy to learn and use, simple syntax; 2) Rich libraries and frameworks, such as NumPy, Pandas, etc.; 3) Cross-platform support, which can be run on a variety of operating systems; 4) Suitable for scripting and automation tasks to improve work efficiency.

Yes, learn Python in two hours a day. 1. Develop a reasonable study plan, 2. Select the right learning resources, 3. Consolidate the knowledge learned through practice. These steps can help you master Python in a short time.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Notepad++7.3.1
Easy-to-use and free code editor

WebStorm Mac version
Useful JavaScript development tools

Dreamweaver Mac version
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)