Use of Data Persistence Technology (Python)

• Traditional database connection method: mysql (PyMySQL)
• ORM models: SQLAlchemy MyBatis, Hibernate

PyMySQL

Installation:

pip install pymysql

Easy to use

Use pymysql.connect to establish a database connection and execute SQL commands (the database needs to be built in advance):

import pymysqldb = pymysql.connect(# mysql addresshost='182.92.129.158',# Account number and passworduser='tmp',password='ceshiren.com',# databasedb='tmp',charset='utf8mb4')if __name__ == '__main__':with db.cursor() as cursor:# Check how many tables are in the databasesql = "show tables;"# execute sql statementcursor.execute(sql)# view all dataprint(cursor.fetchall())# Query data with name = aaaaaasql = "select * from test_case_table where name=%s"cursor.execute(sql, ["aaaaaa"])print(cursor.fetchall())

(('test_case_table',),)(('aaaaaa', 'new test case', 'test_hello.py', 'def test'),)

ORM

Object-relational mapping (object-relational mapping) uses language features to operate the database, such as the operation of Python objects, the operation content will be mapped to the database.
SQLALchemy is an ORM framework in the Python programming language. The framework is built on top of the database API and uses relational object mapping for database operations.

Installation

pip3 install SQLAlchemy

A database connection can be created after the installation is complete:

engine = create_engine("mysql+pymysql://tmp:[email protected]/tmp?charset=utf8",echo=True,)

1.echo: When set to True, the ORM statement will be converted into a SQL statement for printing, which is generally available for debugging.
2. Field explanation:
3. mysql+pymysql: connection mode, using pymysql.
4.tmp:ceshiren.com: username:password.
5.182.92.129.158/tmp: database address and database name.

Create database

from sqlalchemy import create_engine, Column, Integer, Stringfrom sqlalchemy.orm import declarative_baseengine = create_engine("mysql+pymysql://tmp:[email protected]/tmp?charset=utf8",echo=True,)# Its subclasses map Python classes to database table associationsBase = declarative_base()# inherit from Baseclass Users(Base):__tablename__ = "users"id = Column(Integer, primary_key=True)name = Column(String(64), unique=True)def __init__(self, name):self.name = nameif __name__ == '__main__':# Generate database tables, if there is this library, it will be ignoredBase.metadata.create_all(engine)

declarative_base() is a method encapsulated inside SQLALchemy, which allows its subclasses to map Python classes to database tables.

Add and check

SQLALchemy uses Session to create a session between the program and the database. The Session object can be used to add, delete, modify and query data.

from sqlalchemy.orm import sessionmaker# create sessionSession = sessionmaker(bind=engine)session = Session()# add new dataadd_user = Users("student1")# submitsession.add(add_user)session.commit()# Inquireresult = session.query(Users).filter_by(name="student1").first()print(result.id, result.name)

The above code adds data to query, and the result is as follows:

1 student1

The data persistence technology will be introduced here first. You can try to do some exercises.
We will talk about cross-platform API docking later, please continue to pay attention~