Create a class. A class corresponds to a table in a database, and the data attributes of the class correspond to the field names in the table. This class is called mapping class.

1, Proactively create mappings

Classes mapped using the Declarative system are defined according to the base class. In other words, each mapped class needs to inherit this base class. We use Declarative_ The base () function can create this base class,

As follows:

Step 1: create a base class (O in all ORM, super parent class of object model)

from sqlalchemy.ext.declarative import declarative_base

#Create database engine
engine=create_engine(DB_URI)
#Create base class
Base=declarative_base(engine)

Step 2: define the mapping between python classes and tables

Note 1: use this Base class as the Base class to write your own ORM class. To define__ tablename__ Class attribute to specify the table name that this model maps to the database.

class Person(Base):
    __tablename__='t_person1' #Table name
    id=Column(name='id',type_=Integer,primary_key=True)    #Primary key
    name=Column(name='name',type_=String(255))
    age=Column(name='age',type_=Integer)
    address=Column(name='address',type_=String(255))

Note 2: create attributes to map to the fields in the table. All attributes that need to be mapped to the table should be of Column type:
Note 3: create some attributes in this ORM model to map the fields in the table one by one. These attributes must be the data type provided by sqlalchemy

Step 3: create a table

Base.metadata.create_all()

Note 5: once base is used metadata. create_ After all () maps the model to the database, even if the fields of the model are changed, it will not be remapped.

If a new field needs to be remapped into the table, for example, the newly added fields are city and country

Step 4: delete the table and create a new table again

class Person(Base):
    __tablename__='t_person'  #Create a table name, preferably t_ start
    id=Column(name='id',type_=Integer,primary_key=True,autoincrement=True)
    name=Column(name='name',type_=String(255))
    age=Column(name='age',type_=Integer)
    address=Column(name='address',type_=String(255))
    country=Column(name='country',type_=String(50))  #New fields added after table creation
    city=Column(name='city',type_=String(50))        #New fields added after table creation


Base.metadata.drop_all()   #Delete the table structure. For newly added fields after creating a table, you need to delete the table structure first

#Then create the table
Base.metadata.create_all()

2, SQLAlchemy common data types

1. Integer: integer, which is mapped to the database and is of type int.
2. Float: floating point type, which is mapped to the database. He occupies 32 places.
3. Double: double precision floating-point type, which is mapped to the database and occupies 64 bits (not in SQLALCHEMY).
4. String: variable character type, which is mapped to the database as varchar type
5. Boolean: Boolean type. tinyint type is mapped to the database.
6. DECIMAL: fixed point type. It is designed to solve the problem of precision loss of floating-point type. It is recommended that you use this data type when storing money related fields. When this type is used, two parameters need to be passed. The first parameter is used to mark how many numbers this field can always store, and the second parameter indicates how many digits there are after the decimal point.
For example: sal = Column(name='sal', type_=DECIMAL(10, 2))**
7. Enum: enumeration type. A specified field can only be several values specified in the enumeration, and cannot be other values. In the ORM model, enum is used as an enumeration.
8. Date: storage time, which can only store year, month and day. Mapped to the database is of type date. In Python code, you can use datetime Date.
9. DateTime: storage time, which can store year, month, day, hour, minute, second, millisecond, etc. Mapping to the database is also a datetime type. In Python code, you can use datetime Datetime.
10. Time: storage time, which can store hours, minutes and seconds. Mapping to the database is also a time type. In Python code, you can use datetime Time to create the value.
10. Text: store long string. Generally, it can store 6W multiple characters. If this range is exceeded, you can use the LONGTEXT type. Mapping to the database is the text type.
11. LONGTEXT: long text type, which is mapped to the database as long text type. This type belongs to Mysql dialect

Create a model class for the above SQLAlchemy common data types

#Define an enumeration class
class TagEnum(enum.Enum):
    python="PYTHON"
    flask="FLASK"
    django="DJANGO"

#Create an ORM model to explain the common field types mapped to mysql database based on sqlalchemy
Base=declarative_base(engine)

#Create model class
class News(Base):
    __tablename__='t_news'
    id=Column(type_=Integer,primary_key=True,autoincrement=True)
    price1=Column(type_=Float)                      #There is a loss of accuracy when storing data
    price2=Column(type_=DECIMAL(10,4))
    title=Column(type_=String(50))
    is_delete=Column(type_=Boolean)
    tag1=Column(enum.Enum('PYTHON','FLASK','DJANGO'))    #Enumeration general writing
    tag2=Column(Enum(TagEnum))                      #Another way to write enumeration
    create_time1=Column(type_=Date)
    create_time2=Column(type_=DateTime)
    create_time3=Column(type_=Time)
    content1=Column(type_=Text)
    content2=Column(type_=LONGTEXT)

#Generate data table according to model class
Base.metadata.create_all()

Insert data question insert data question insert data question insert data question insert data question enumeration class

Insert code slice here

3, Column common parameters

1. primary_key: True sets a field as the primary key.
2. autoincrement: True sets this field to auto increment.
3. default: set the default value of a field. These fields are often used in publishing time.
4. nullable: Specifies whether a field is empty. The default value is True, which means it can be empty.
5. unique: Specifies whether the value of a field is unique. The default is False.
6. onupdate: the value or function specified by this parameter will be called during data update. When inserting this data for the first time, the value of onupdate will not be used, but the value of default will be used. Common update_time field (the value of this field should be updated every time the data is updated).
7. name: Specifies that an attribute in the ORM model is mapped to the field name in the table. If not specified, the name of this property will be used as the field name. If specified, the specified value will be used as the table field name. This parameter can also be used as a location parameter and specified in the first parameter.

case

import enum
from sqlalchemy import create_engine,Column,Integer,String,TEXT,Boolean,Float,DECIMAL,DATE,Date,DateTime,Time,Text
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
# Configuration variables for database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = 'root'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)

#Create database engine
engine=create_engine(DB_URI)


#Create base class
Base=declarative_base(engine)

class New(Base):
    __tablename__='t_news1'
    id = Column(Integer, primary_key=True, autoincrement=True) #Auto increment primary key
    create_time = Column(DateTime,default=11)                  #Set the default value of a field. These fields are often used in publishing time.
    title = Column(String(50), name='my_title', nullable=False) #Specifies whether a field is empty. nullable=False, indicating that it cannot be empty
    telephone = Column(String(11), unique=True)                 #Specifies whether the value of a field is unique. unique=True: unique
    update_time = Column(DateTime, onupdate=datetime.now, default=datetime.now) #When the data is updated, the value or function specified by this parameter will be called. When inserting this data for the first time

Base.metadata.create_all()
#Base.metadata.drop_all()