1. Introducing dependencies

#Introducing sql-dependent Akmi
import sqlalchemy
#Introducing various data types
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR,VARCHAR,Date
#Create a database connection
from sqlalchemy import create_engine
#Introduce?? rely on
from sqlalchemy.ext.declarative import declarative_base
#Introducing session dependency
from sqlalchemy.orm import sessionmaker

1. Connect to the database

# The parameters are (database type + database driver: //username: password @server address: port number/database)
#Establish remote connection with mysql database through pymysql and set the maximum number of connections:
# #engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/day63?charset=utf8", max_overflow=5)
engine = create_engine("mysql://root:root@127.0.0.1:3306/rank?charset=utf8",echo=True)

1. Instance base

# declarative_base() is a factory function that constructs base classes for declarative class definitions
Base = declarative_base()

1. Create a session

#Create a session
Session = sessionmaker(bind=engine)
session = Session()

1. Create tables

#Create tables
class actList2(Base):
    __tablename__ = "actList2"
    id = Column(Integer, primary_key=True)
    tag = Column(String(100))
    title = Column(String(100))
    date = Column(Date)

    def __repr__(self):
        return "<User(tag='{}',title='{}',date='{}')".format(self.tag,self.title,self.date)
# Create tables. This method queries whether tables exist or not before creating tables.
Base.metadata.create_all(engine)

About the basic types of fields:

• Integer: Shaping, mapping to a database is an int type.

• Float: Float type, which maps to the database is float type. He occupied 32 places.

• Double: Double precision floating point type, mapped to the database is double type, occupying 64 bits.

• String: Variable character type, mapped to the database is varchar type.

• Boolean: Boolean type, which maps to the database is the tinyint type.

• DECIMAL: Fixed-point type. It is specially designed to solve the problem of loss of accuracy of floating-point type. It is recommended that you use this data type when storing money-related fields. And when this type is used, two parameters need to be passed. The first parameter is to mark how many digits the field can always store, and the second parameter is to indicate how many digits there are after the decimal point.

• Enum: Enumeration type. Specifying a field can only be a few values specified in the enumeration, not other values. In the ORM model, Enum is used as an enumeration

• Date: Storage time, only year, month and day. Mapping to a database is a date type. In Python code, you can use datetime.date to specify

• DateTime: Storage time, can store years, months, days, minutes, seconds, milliseconds, etc. Mapping to a database is also a datetime type. In Python code, you can use datetime.datetime to specify. The sample code is as follows:

• Time: Storage time, you can store time and seconds. Mapping to a database is also a time type. In Python code, you can use datetime.time to come here and there.

• Text: Stores long strings. Generally, it can store more than 6W characters. If this is beyond the scope, you can use the LONGTEXT type. Mapping to a database is the text type.

• LONGTEXT: Long text type, mapped to the database is long text type.

1. Add data

arc0 = actList2(tag='activity',title='A New Event',date='2919-8-30')
arc1 = actList2(tag='activity',title='First activity',date='2919-8-30')
arc2 = actList2(tag='Journalism',title='A piece of news',date='2919-8-30')
arc3 = actList2(tag='Journalism',title='Second News',date='2919-8-30')
arc4 = actList2(tag='Colleagues',title='Peer Painting',date='2919-8-30')
# Add a new data
# session.add(arc1)
#Add all new data
session.add_all([arc0,arc1,arc2,arc3,arc4])
session.commit()

1. Query data

#Get all the data
rs =  session.query(actList2).all()
#Filtering data
rs2 =  session.query(actList2.tag).filter(actList2.id>1).all()
print(rs)
print(rs2)