使用sqlalchemy更新数据

1、使用sqlalchemy更新数据记录

from sqlalchemy import create_engine, MetaData, update
from sqlalchemy.schema import CreateTable
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Table
from rich import print


MYSQL_HOST = 'localhost'
MYSQL_USER = 'root'
MYSQL_PASSWORD = r'P@ssw0rd_202'
MYSQL_PORT = 3306
MYSQL_NAME = 'iron'

# 为MySQL创建引擎，返回一个引擎实例：
engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'
                       % (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_NAME), pool_recycle=3600)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# reflect 映射iron库下的表结构
metadata = MetaData(bind=engine)
metadata.reflect(schema='iron')

net_as = Table('network_accessswitch', metadata, autoload_with=engine)
net_as_desc = CreateTable(net_as).compile(engine)
print("**** 打印表结构的SQL语句 ****")
print(net_as_desc)

Net_AS = metadata.tables['network_accessswitch']
conf = '''!Software Version V200R011C10SPC600\n'''
upd = update(Net_AS)
val = upd.values({"last_conf": conf})
cond = val.where(Net_AS.c.id == 242)
engine.execute(cond)

session.commit()
session.close()

2、获取创建表结构的SQL语句：

CREATE TABLE network_accessswitch (
        id BIGINT(20) NOT NULL AUTO_INCREMENT,
        ip VARCHAR(128) NOT NULL,
        catalog VARCHAR(128) NOT NULL,
        location VARCHAR(128) NOT NULL,
        username VARCHAR(128) NOT NULL,
        password VARCHAR(128) NOT NULL,
        old_passwd VARCHAR(128),
        update_time DATETIME(6),
        created_time DATETIME(6),
        status INTEGER(11),
        last_conf LONGTEXT,
        manufacture VARCHAR(128),
        module VARCHAR(128),
        memo LONGTEXT,
        buy_date VARCHAR(128),
        PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

3、Django中定义的Model

# 接入交换机表
class AccessSwitch(models.Model):
    STATUS_ITEMS = [
        (0, '在线'),
        (1, '离线'),
        (2, '下架'),
    ]

    ip = models.CharField(
        max_length=128,
        unique=True,
        verbose_name='IP地址')

    catalog = models.CharField(max_length=128, verbose_name='分类/sheet')
    location = models.CharField(max_length=128, verbose_name='地点')
    username = models.CharField(max_length=128, verbose_name='用户名')
    password = models.CharField(max_length=128, verbose_name='密码')
    old_passwd = models.CharField(max_length=128, null=True, verbose_name='上次密码')
    manufacture = models.CharField(max_length=128, null=True, default='华为', verbose_name='生产厂家')
    module = models.CharField(max_length=128, null=True, default='', verbose_name='交换机型号')
    status = models.IntegerField(choices=STATUS_ITEMS, null=True, default=0, verbose_name='交换机状态')
    buy_date = models.CharField(max_length=128, null=True, default='', verbose_name='交换机出厂年限')
    last_conf = models.TextField(blank=True, null=True, verbose_name='最近更新的配置文件')
    memo = models.TextField(blank=True, null=True, verbose_name='备忘')
    update_time = models.DateTimeField(auto_now_add=True, null=True, editable=True, verbose_name='更新时间')
    created_time = models.DateTimeField(auto_now_add=True, null=True, editable=False, verbose_name='创建时间')

    def __str__(self):
        return '<Access Switch IP Address: {}>'.format(self.ip)

    class Meta:
        verbose_name = verbose_name_plural = "内网接入交换机信息表"