One SQL script defined by sqlalchemy from the model class to the database table automatically generates logs

One SQL script defined by sqlalchemy from the model class to the database table automatically generates logs

CREATE TABLE session (session_id VARCHAR(128) NOT NULL, atime TIMESTAMP NULL, data TEXT, PRIMARY KEY (session_id))

A python model class is defined as follows:

class Group(Base):    __tablename__ = 'group'        id = Column(Integer, primary_key=True)    name = Column(String(64), nullable=False)    users = relationship("User", secondary=group_user_asso_table, backref=backref('groups'))

The SQL statement generated by the model group is as follows:

CREATE TABLE `group` (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, PRIMARY KEY (id))

Bytes -----------------------------------------------------------------------------------------------

class User(Base):    __tablename__ = 'user'        id = Column(Integer, primary_key=True)    username = Column(String(32), nullable=False)    password = Column(String(64), nullable=False)    email = Column(String(64), nullable=False)    is_active = Column(Boolean, nullable=False, default=False)    is_superuser = Column(Boolean, nullable=False, default=False)    last_login = Column(TIMESTAMP, default=utcnow())    joined_time = Column(TIMESTAMP, default=utcnow())

The SQL statement generated by the model user is as follows:

CREATE TABLE user (id INTEGER NOT NULL AUTO_INCREMENT,     -- id = Column(Integer, primary_key=True)username VARCHAR(32) NOT NULL,          -- username = Column(String(32), nullable=False)password VARCHAR(64) NOT NULL,          -- password = Column(String(64), nullable=False)email VARCHAR(64) NOT NULL,             -- email = Column(String(64), nullable=False)is_active BOOL NOT NULL,                -- is_active = Column(Boolean, nullable=False, default=False)is_superuser BOOL NOT NULL,             -- is_superuser = Column(Boolean, nullable=False, default=False)last_login TIMESTAMP NULL,              -- last_login = Column(TIMESTAMP, default=utcnow())joined_time TIMESTAMP NULL,             -- last_login = Column(TIMESTAMP, default=utcnow())PRIMARY KEY (id), CHECK (is_active IN (0, 1)), CHECK (is_superuser IN (0, 1)))

Bytes -----------------------------------------------------------------------------------------------

Class Permission (Base): _ tablename _ = 'permission' id = Column (Integer, primary_key = True) name = Column (String (64), nullable = False) codename = Column (String (64), nullable = False) order = Column (SmallInteger) # Set parent_id = Column (Integer, ForeignKey ('permission. id ') # Use of relationship, backref parameter children = relationship ("Permission", backref = backref ('parent'), remote_side = [id]) # indicate that permission and group tables have many-to-many associations. They use the intermediate table permission_group_asso_table definition groups = relationship ("Group", secondary = permission_group_asso_table, backref = backref ('permission') # users = relationship ("User", secondary = permission_user_asso_table, backref = backref ('permission '))

Description of parameters of the relationship function:
Relationship ("Group", secondary = permission_group_asso_table, backref = backref ('permission '))
Or
Relationship ("Permission", backref = backref ('parent'), remote_side = [id])

What are the associations between permission and group tables? For how to configure, see:

permission_group_asso_table = Table('permission_group_asso', Base.metadata,    Column('permission_id', Integer, ForeignKey('permission.id'), primary_key=True),    Column('group_id', Integer, ForeignKey('group.id'), primary_key=True)    )

This Code creates an intermediate table permission_group_asso. The foreign key is associated with the table permission and group, and the primary keys of the two tables are used as the joint primary keys. The SQL statement is as follows:

Create Table permission_group_asso (permission_id integer not null, -- column ('permission _ id', integer, foreignkey ('permission. id'), primary_key = true), group_id integer not null, -- column ('group _ id', integer, foreignkey ('group. id'), primary_key = true) primary key (permission_id, group_id), -- use the primary keys foreign key (permission_id) References permission (ID), foreign key (group_id) of the two tables) references 'group' (ID ))

# "Permission" corresponds to the argument Parameter
: Param argument:
Target Model class of the corresponding link

: Param secondary:
This parameter is used to specify the intermediate table for the response-to-sequence Association. This intermediate table needs to be defined using the table method, such as the permission_group_asso_table code.
It is usually mutually exclusive and can only be used in one model class.

: Param backref:
Indicates the string name of a property to be placed on the related
S er's class that will handle this relationship in the other
Direction. The other property will be created automatically
When the mappers are configured. Can also be passed as
: Func: 'backref 'object to control the configuration of
New relationship.

: Param remote_side:
Used in the relationship of Self-reference, such as the tree structure, the association between the table and itself

From the generated SQL scripts, you can see the following results

Create table permission (id integer not null AUTO_INCREMENT, -- id = Column (Integer, primary_key = True) name VARCHAR (64) not null, -- name = Column (String (64 ), nullable = False) codename VARCHAR (64) not null, -- codename = Column (String (64), nullable = False) 'order' SMALLINT, -- order = Column (SmallInteger) parent_id INTEGER, -- how to set the foreign key Association: parent_id = Column (Integer, ForeignKey ('permission. id') primary key (id), foreign key (parent_id) REFERENCES permission (id ))

Bytes -----------------------------------------------------------------------------------------------

CREATE TABLE permission_user_asso (permission_id INTEGER NOT NULL, user_id INTEGER NOT NULL, PRIMARY KEY (permission_id, user_id), FOREIGN KEY(permission_id) REFERENCES permission (id), FOREIGN KEY(user_id) REFERENCES user (id))CREATE TABLE group_user_asso (group_id INTEGER NOT NULL, user_id INTEGER NOT NULL, PRIMARY KEY (group_id, user_id), FOREIGN KEY(group_id) REFERENCES `group` (id), FOREIGN KEY(user_id) REFERENCES user (id))