使用sqlalchemy

1. 使用docker中的mysql

自己使用vs code + docker的本地开发环境，除了费电，其它一切良好，不用考虑过多本机环境切换的问题。

连接docker中的mysql，host需要设置为该docker的ip，可进入容器后使用cat /etc/hosts进行查看。若是设置了hostname,也可以使用hostname进行连接

2. 密码中有特殊字符的连接方式

可用python的format方式进行填充，示例：

# 初始化数据库连接:
engine = create_engine(
    f'mysql+pymysql://{name}:{password}@{host}:{port}/{db_name}')

3. 外键的使用

mysql中的外键描述

mysql中的外键，是为了保证数据的一致性。

主键：如user表中，一般使用id作为主键，不可重复

外键：如user_info表中，用户的详细信息。

只有user表中新增了用户 z的信息，user_info表中才可以新增用户z的详细信息

可约束为只有user_info表中没有用户 z的信息了，才可以删除user表中的用户z

这样保证了数据的一致性

sqlalchemy中使用外键

sqlalchemy中可以设置外键，需要用ForeignKey字段标识。同时可设置关联查询及反向关联查询，类似于mysql中的join字段

示例说明：

给用户设定角色，角色对应相应的权限

角色分为 admin,leader,user

admin可以查看所有页面，即有 admin,leader,user的权限

leader可以查看自己及组员的页面，即leader,user的权限

user只能查看自己的页面，即user的权限

关系图如下：

image.png

示例：

权限对应表authority的sql(sqlalchemy自动生成的)

CREATE TABLE `authority` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `authority` varchar(20) DEFAULT NULL COMMENT '权限名称', 
  `role` int(11) DEFAULT NULL COMMENT '角色id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间', 
  PRIMARY KEY (`id`), 
  KEY `ix_authority_role` (`role`), 
  KEY `ix_authority_authority` (`authority`), 
  CONSTRAINT `authority_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3

对应的类

from sqlalchemy import String, Column, Integer, create_engine, DateTime, Boolean, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()


class UserAuthority(Base):
    __tablename__ = 'authority'

    id = Column(Integer, primary_key=True, autoincrement=True)
    authority = Column(String(20), index=True, comment="权限名称")
    role = Column(Integer, ForeignKey(
        "role.id", ondelete="SET NULL"), index=True, comment="角色id")
    create_time = Column(DateTime, default=datetime.now(), comment="创建时间")

    # 反向查询外键，可在 UserRole 实例对象中调用 UserAuthority 的属性，如 UserRole.UserAuthority
    user = relationship('UserRole', backref="UserAuthority")

    def __repr__(self):
        return "<user (id='%s', role='%s')>" % (self.id, self.role)

角色role表的sql(sqlalchemy自动生成的)

CREATE TABLE `role` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `role` varchar(20) DEFAULT NULL COMMENT '角色名', 
  `create_time` datetime DEFAULT NULL COMMENT '创建时间', 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `ix_role_role` (`role`) ) 
  ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3

对应的类

class UserRole(Base):
    __tablename__ = 'role'

    id = Column(Integer, primary_key=True, autoincrement=True)
    role = Column(String(20), index=True, unique=True, comment="角色名")
    create_time = Column(DateTime, default=datetime.now(), comment="创建时间")

    # 反向查询外键，可在 User 实例对象中调用 UserRole 的属性，如 User.UserRole
    user = relationship('User', backref="UserRole")

    def __repr__(self):
        return "<user (id='%s', role='%s')>" % (self.id, self.role)

用户user表的sql(sqlalchemy自动生成的)

CREATE TABLE `user` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(20) DEFAULT NULL COMMENT '用户名', 
  `password` varchar(128) DEFAULT NULL COMMENT '密码', 
  `role` int(11) DEFAULT NULL COMMENT '角色id', 
  `is_delete` tinyint(1) DEFAULT NULL COMMENT '删除标记，为True时表示可用', 
  `create_time` datetime DEFAULT NULL COMMENT '创建时间', 
  `last_login` datetime NOT NULL COMMENT '最后登陆时间', 
  `update_time` datetime NOT NULL COMMENT '更新时间', 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `ix_user_name` (`name`), 
  KEY `ix_user_is_delete` (`is_delete`), 
  KEY `ix_user_role` (`role`) ) 
  ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3

对应的类

class User(Base):
    # 表名
    __tablename__ = 'user'

    # 表结构
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), index=True, unique=True, comment="用户名")
    password = Column(String(128), comment="密码")
    role = Column(Integer, ForeignKey(
        "role.id", ondelete="SET NULL"), index=True, comment="角色id")
    is_delete = Column(Boolean, default=True, index=True,
                       comment="删除标记, 为True时表示可用")
    create_time = Column(DateTime, default=datetime.now(), comment="创建时间")
    last_login = Column(DateTime, default=datetime.now(),
                        nullable=False, comment="最后登陆时间")
    update_time = Column(DateTime, default=datetime.now(),
                         nullable=False, comment="更新时间")

    def __repr__(self):
        return "<user (id='%s', name='%s')>" % (self.id, self.name)

在用户中查询对应的角色：

e1 = session.query(User).filter_by(id=3).first()
# 在 User 实例对象中调用 UserRole 的属性
print(e1.name, e1.UserRole.role)

在用户中查询对应的权限

e1 = session.query(User).filter_by(id=7).first()

# 在用户的实例对象中查询对应的权限。因为一个用户可能有多个权限，因此返回一个list
print([x.authority for x in e1.UserRole.UserAuthority])