Related information/Official website address
1. Install sqlalchemy and check the version
Execute the following statement under the command line to install:

pip install SQLAlchemy

View version after installation:

import sqlalchemy
print(sqlalchemy.__version__)

In addition, PyMySQL needs to be installed, otherwise an error of modulenotfounderror: no module named 'MySQL db' will be reported during use.

pip install PyMySQL

2. Connect to the database

engine=create_engine("mysql+pymysql://User name: password @ HOST / database name ", encoding='UTF-8', echo=True)
//perhaps
pymysql.install_as_MySQLdb()
engine=create_engine("mysql://User name: password @ HOST / database name ", encoding='UTF-8', echo=True)
//Or (PyMySQL does not need to be installed in this way)
engine = create_engine("mysql+mysqlconnector://root:root@localhost/tb_test", encoding='UTF-8', echo=False)

3. Create entity class
In SQLAlchemy, the two tasks of defining entity class and mapping entity class are performed by a system called Declarative. The classes mapped with Declarative system are defined according to the base class, so it is necessary to define a base class of BaseEntity, as follows:

from sqlalchemy.ext.declarative.api import declarative_base
BaseEntity = declarative_base()

Define User entity class

from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import Integer, DateTime, String
from sqlalchemy.ext.declarative.api import declarative_base

Base=declarative_base()
class User(Base):
    __tablename__='user' 
    
    id=Column(Integer, primary_key=True)
    name=Column(String)
    phone=Column(String)
    birthday=Column(Date)
    
    def __repr__(self):
        return "<User(id='%s', name='%s', phone='%s', birthday='%s')>" % (
            self.id, self.name, self.phone, self.birthday)

4. Single table query

from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from helloworld.entity.User import User

engine = create_engine("mysql+mysqlconnector://root:root@localhost / table name ", encoding='UTF-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session();

result = session.query(User)
for row in result:
    print(row.name)

Query specified fields

result = session.query(User.name, User.phone)
for row in result:
    print(row.name)

field alias

result = session.query(User.name.label("userName"))
for row in result:
    print(row.userName)

Filter
Regarding the filtering operation of query results, SQLAlchemy provides two functions, filter "by" and filter.
Filter by provides a simple column name query and does not support comparison operators. The rules are as follows:

result = session.query(User).filter_by(birthday=None)
for row in result:
    print(row)

filter has more powerful functions. The rules are as follows:

filters = {
    User.col_birthday != None
}
result = session.query(User).filter(*filters)
for row in result:
    print(row)

5. New record
Add a single record

newUser = User()
newUser.name = 'New UserName'
newUser.phone = 'New phone'
newUser.birthday = 'New birthday'
try:
    session.add(newUser)
    session.commit()
    print(newUser.id)
except Exception as e:
    session.rollback()
    print(e)

Add multiple records

session.add_all([user1, user2])
session.commit()
print(user1.id)
print(user2.id)

When the commit succeeds, the id will write the primary key of the new record.

6. Update records

try:
    ret = session.query(User).filter_by(id=40).update({"name": "jane"})
    session.commit()
except Exception as e:
    session.rollback()
    print(e)

7. Deleting records

session.query(User).filter_by(id=41).delete()
session.commit()

8. Joint table query

from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, DateTime, String
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship

Base=declarative_base()
class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    user_name = Column(String)
    phone = Column(String)
    birthday = Column(DateTime)
    role = relationship("Role", back_populates="user") #Back ﹐ populates the user in the Role class should be written here

    def __repr__(self):
        return "<User(id='%s', user_name='%s', phone='%s', birthday='%s')>" % (
            self.id, self.user_name, self.phone, self.birthday)

class Role(Base):
    __tablename__ = 'role'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))  #ForeignKey('table name. Field name ')
    role_name = Column(String)
    user = relationship("User", back_populates="role")  #Back ﹐ populates what should be written here is the role in the User class

    def __repr__(self):
        return "<SelectTb(id=%s, user_id=%s, role_name=%s)>" % (
            self.id, self.user_id, self.role_name)

call

ret = session.query(User)
for row in ret1:
    print(row.id)
    print(row.role)

8. Automatically generate entity class

#Command line installation entity class generation tool
pip install sqlacodegen
#Command line generate entity class
sqlacodegen mysql+mysqlconnector://User name: password @ Host: port / database name > models.py