Python Automation Development Learning 12-federated unique

Union unique

The Union is unique, that is, the combination of multiple fields in a table needs to be unique.

Using the MySQL statement to create

For example, create a hosts table, log the IP address of the login or hostname (the corresponding field host in the table) and the port number (the corresponding field in the table port), requiring that the combination of host and port must be unique:

> CREATE TABLE hosts1 (    -> id INT UNSIGNED AUTO_INCREMENT,    -> host CHAR(20) NOT NULL,    -> port TINYINT UNSIGNED DEFAULT 22,    -> description VARCHAR(20),    -> PRIMARY KEY (id)    -> );

If the table already exists, you need to modify the table structure to add a unique constraint:

> ALTER TABLE hosts1    -> ADD UNIQUE KEY host_port(host, port)

or directly one step, specify a unique constraint when creating a table:

> CREATE TABLE hosts2 (    -> id INT UNSIGNED AUTO_INCREMENT,    -> host CHAR(20) NOT NULL,    -> port TINYINT UNSIGNED DEFAULT 22,    -> description VARCHAR(20),    -> PRIMARY KEY (id),    -> UNIQUE KEY host_port(host, port)    -> );

Using the SQLAlchemy operation

The Union only needs to import additional modules and UniqueConstraint then use a variable __table_args__ to hold the union unique that you want to declare. In addition, if you want to declare the index, it is in this variable. The type of the variable is ganso.

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, CHARfrom sqlalchemy.dialects.mysql import INTEGER, TINYINTfrom sqlalchemy import UniqueConstraint  # 联合唯一，需要导入engine = create_engine("mysql+pymysql://admin:[email protected]/week12",                       encoding=‘utf-8‘, echo=True)  # 这里设置了echo参数，显示中间过程和SQL语句Base = declarative_base()  # 生成orm基类class Hosts3(Base):    __tablename__ = ‘hosts3‘  # 表名    id = Column(INTEGER(unsigned=True), primary_key=True)    host = Column(CHAR(20))    port = Column(TINYINT(unsigned=True), default=22)    description = Column(String(20))    __table_args__ = (UniqueConstraint(‘host‘, ‘port‘, name=‘host_port‘),)  # 这个变量名不能变，后面是个元祖    # 另外如果要做索引，也是写上面的元祖里。索引需要导入Index，然后加上这个: Index(‘host‘, ‘port‘)Base.metadata.create_all(engine)  # 创建表结构，这里是通过父类来调用子类

Python Automation Development Learning 12-federated unique