sqlalchemy报错：No engine is bound to this Table‘s MetaData. Pass an engine to the Table via autoload_w

sqlalchemy报错：No engine is bound to this Table’s MetaData. Pass an engine to the Table via autoload_w

首先，借着这个报错，我们来研究一下如何在项目中相对优雅的使用sqlalchemy
1、数据异步是指跟数据库操作等待数据返回所需的时间消耗。这部分时间也应该还给cpu。
2、对数据库表的操作，请求和返回也要单独封装出异步，这段中间的时间可以返回给系统

封装链接断开数据库链接

from util.read_conf import Read_YAML
import traceback
import databases
from loguru import logger

content = Read_YAML().read()
db_config = content.get("MYSQL")
DATABASE_URL = "mysql://{user}:{password}@{host}:{port}/{database}?charset={charset}".format(**db_config)


# 返回可以with open的databases.Database(DATABASE_URL)
class MySQL:
    async def __aenter__(self):

        try:
            self.database = databases.Database(DATABASE_URL)
            await self.database.connect()
        except Exception as e:
            logger.error(e)
        return self.database

    async def __aexit__(self, exc_type, exc, tb):
        if exc:
            traceback.print_exc()
        await self.database.disconnect()

databases是什么，干什么的
事实上，aiomysql已经帮助我们实现了类似的功能，但很遗憾aiomysql不能和sqlalchemy配套使用，database是一个简单的异步的数据库驱动引擎，能执行sqlalchemy生成的sql。

正常的使用sqlalchemy

import time
import datetime

import databases
import sqlalchemy
import asyncio


class UserManager:
    def __init__(self):
        url = "mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}".format(**env)
        # 通过database链接数据库
        self.database = databases.Database(url)
        # 通过sqlalchemy获取数据库的元数据，元数据，说人话就是他能将你链接的数据库的所有的表的信息，都抽象出来
        self.metadata = sqlalchemy.MetaData()
        # 通过create_engine初始化数据库链接
        self.engine = sqlalchemy.create_engine(url, encoding='utf-8', max_overflow=0, pool_size=5,
                                               pool_timeout=30, pool_recycle=3600, )
        #  来创建所有 Base 派生类所对应的数据表,如果存在则忽略
        self.metadata.create_all(self.engine)

    def _user_cls(self):
        """
        初始化类user表的抽象信息,通过反射返回了table，下面的讲解有正常返回table是什么样的
        :return:
        """
        return sqlalchemy.Table(
            "m_user",
            self.metadata,
            autoload=True,
            autoload_with=self.engine,
        )

MetaData是什么

sqlalchemy中一个重要的元素是SQL Expression Language（用来生成sql语句的）。而它的实现就是用一些Python对象来表示数据库中的表和列等概念。这些Python对象就称为元数据。
最常用的sqlalchemy中的元数据是MetaData，Table和Colum类。

create_engine

就是创建链接的

metadata.create_all(self.engine)

metadata.create_all(engine) 方法会判断操作的表是否存在，如果不存在则创建，如果存在则不创建

注意：是创建数据库表的

Table

Table的参数:

• name 表名称
• metadata 该表所属的MetaData对象
• 其他参数: 通过Column指定一列数据

Table构造函数

Table(name, metadata[, *column_list][, **kwargs]) 

参数说明：

• name 表名

• metadata 元数据对象

• column_list 是列(Column或其他继承自SchemaItem的对象)列表

• kwargs

  主要内容：

  • schema: (None)表的模式（一般默认是数据库名, 无需特别指定; Oracle中是owner, 当一个数据库由多个用户管理时，用户的默认数据库不是要连接的数据库时，需要指定此项）

  • autoload: (False)是否自动加载

  • autoload_replace: (True)是否自动用元数据中加载的列替换column_list中已经存在了的同名列

    • 为True时自动将column_list中已经存在了的列替换为从元数据中加载的同名列
    • 为False时会忽略元数据有，且column_list中已经存在了的列

  • autoload_with: 自动加载的引擎(Engine）或连接(Connection)对象为None时

    • autoload为True时, 会从传递的metadata中寻找引擎或连接对象不为None时

    • 当autoload不为True时, autoload会自动被修改为True

  • comment: 注释

  • extend_existing: (False)当表已经存在于元数据中时，如果元数据中存在与column_list中的列同名的列，column_list中同名的列会替换掉元数据中已经有的列

  • keep_existing: (False)当表已经存在于元数据中时，如果元数据中存在与column_list中的列同名的列，column_list中同名的列会被忽略

  • include_columns：(None)从元数据中只需加载的表的列名列表

  • mustexist: (False)表名是否一定需要存在于元数据中（不存在时引发异常）

Column的构造方法

Column([name, ]type_[, **kwargs])

参数说明：

• name 字段名

• type_ 字段数据类型，这里的数据类型包括：

  • SQLAlchemy中常用数据类型

    :

    • 整数: SmallInteger、Integer、BigInteger等
    • 浮点数: Float、Numeric等
    • 文本字符串: String、Text、Unicode、UnicodeText、CHAR、VARCHAR等
    • 二进制字符串: LargeBinary、BINARY、VARBINARY等
    • 日期时间: Date、DateTime、TIMESTAMP等

  • Constraint: 约束

  • ForeignKey: 外键

  • ColumnDefault: 列默认值

• **kwargs**主要内容

  ：

  • autoincrement: (False)是否是主键
  • default: (None)默认值
  • index: (None)索引
  • nullable: (True)是否可以为空(NULL)
  • primary_key: (False)是否是主键
  • server_default: (None)服务端(数据库中的函数)默认值
  • unique: (False)是否唯一
  • comment: (None)列注释

正常流程获取table

from sqlalchemy import engine, create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy.orm import sessionmaker
# 创建引擎
engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.207.252:3306/yanzi ')
# 创建session
DBsession = sessionmaker(bind=engine)
session = DBsession
# 创建表
metadata = MetaData(engine)
teachers = Table('teachers', metadata,
                 Column('id', Integer, primary_key=True),
                 Column('name', String(50)),
                 Column('age', Integer),
                 Column('sex', String(10)),
                 Column('name', String(50))
)
metadata.create_all(engine)

反射数据表

反射数据表就是根据数据库中已经存在的数据表，自动生成其对应的数据表模型类。

from sqlalchemy import MetaData,create_engine
metadata_obj = MetaData()
url="mysql+pymysql://root:123456@127.0.0.1:3306/testdb"
engine = create_engine(url)
some_table = Table("some_table", metadata_obj, autoload_with=engine)

autoload

是否自动加载

autoload_with

自动加载的引擎(Engine)或连接(Connection)对象

我们自己封装的反射数据表

数据库表

import asyncio

from sqlalchemy import MetaData, create_engine, Table, and_
import pymysql

from util.database_config import DATABASE_URL, MySQL
from util.logged import logged
# 使用pip install aiomysql 安装
pymysql.install_as_MySQLdb()


class MessageManage:
    def __init__(self):
        # MetaData，通过sqlalchemy获取数据库的元数据，元数据，说人话就是他能将你链接的数据库的所有的表的信息，都抽象出来
        self.metadata = MetaData()
        """
        通过create_engine初始化数据库链接，相当于创建链接
        pool_size： 是连接池的大小，默认为5个，0表示连接数无限制
        pool_recycle： MySQL 默认情况下如果一个连接8小时内容没有任何动作（查询请求）就会自动断开链接，
        出现 MySQL has gone away的错误。设置了 pool_recycle 后 SQLAlchemy 就会在指定时间内回收连接。如果设置为3600 就表示 1小时后该连接会被自动回收。
        pool_timeout: 定义超时时间
        max_overflow：超过连接池大小外最多创建的连接
        """
        self.engine = create_engine(DATABASE_URL, encoding='utf-8', max_overflow=0, pool_size=5, pool_timeout=30,
                                    pool_recycle=3600)
        self.metadata.create_all(self.engine)

    def message_cls(self):
        """
        初始化类tyt_verify_log表的抽象信息
        :return:
        """
        return Table(
            "tyt_verify_log",
            self.metadata,
            autoload=True,
            aotoload_with=self.engine
        )


message_cls = MessageManage().message_cls()


async def get_message_by_id(id: str) -> int or bool:
    """
    查询id对应的短信记录
    :param id: id
    :return: 如果有则返回验证码，没有的话就返回False
    其中，这里使用了async和await来使用协程
    这个举例是获取唯一一条记录的
    """
    async with MySQL() as cur:
        if id:
            # 查询条件
            sql = message_cls.select(). \
                where(and_(message_cls.c.id == id,
                           message_cls.c.status == 0))
            # 通过游标获取唯一一条数据，由于fetch_one也是async异步的（点进去看一下源码就可以了），所以我们可以在这里使用await
            res = await cur.fetch_one(sql)
            print(res)
            result = dict(zip(res.keys(), res))
            return [result]

我们逐段讲解这段代码的内容

报错：No engine is bound to this Table’s MetaData

C:\Users\EDY\PycharmProjects\airUI\database\message_database.py:47: SAWarning: Can't validate argument 'aotoload_with'; can't locate any SQLAlchemy dialect named 'aotoload'
  return Table(
Traceback (most recent call last):
  File "C:\Users\EDY\PycharmProjects\airUI\database\message_database.py", line 55, in <module>
    message_cls = MessageManage().message_cls()
  File "C:\Users\EDY\PycharmProjects\airUI\database\message_database.py", line 47, in message_cls
    return Table(
  File "<string>", line 2, in __new__
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\util\deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 607, in __new__
    metadata._remove_table(name, schema)
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 602, in __new__
    table._init(name, metadata, *args, **kw)
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 677, in _init
    self._autoload(
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 703, in _autoload
    autoload_with = _bind_or_error(
  File "C:\Users\EDY\PycharmProjects\airUI\venv\lib\site-packages\sqlalchemy\sql\base.py", line 1658, in _bind_or_error
    raise exc.UnboundExecutionError(msg)
sqlalchemy.exc.UnboundExecutionError: No engine is bound to this Table's MetaData. Pass an engine to the Table via autoload_with=<someengine_or_connection>

看起来就是没有给元数据绑定引擎，那怎么绑定引擎呢
然后我找到了这篇文章
问答

于是决定修改一下代码，在

class MessageManage:
    def __init__(self):
        # 通过sqlalchemy获取数据库的元数据，元数据，说人话就是他能将你链接的数据库的所有的表的信息，都抽象出来
        self.metadata = MetaData()
        # 通过database链接数据库
        # self.database = Database(DATABASE_URL)
        """
        通过create_engine初始化数据库链接，相当于创建链接
        pool_size： 是连接池的大小，默认为5个，0表示连接数无限制
        pool_recycle： MySQL 默认情况下如果一个连接8小时内容没有任何动作（查询请求）就会自动断开链接，
        出现 MySQL has gone away的错误。设置了 pool_recycle 后 SQLAlchemy 就会在指定时间内回收连接。如果设置为3600 就表示 1小时后该连接会被自动回收。
        pool_timeout: 定义超时时间
        max_overflow：超过连接池大小外最多创建的连接
        """
        self.engine = create_engine(DATABASE_URL, encoding='utf-8', max_overflow=0, pool_size=5, pool_timeout=30,
                                    pool_recycle=3600)
        # 绑定引擎，解决No engine is bound to this Table's MetaData.
        self.metadata.bind=self.engine
        self.metadata.create_all(self.engine)

    def message_cls(self):
        """
        初始化类tyt_verify_log表的抽象信息
        :return:
        """
        return Table(
            "tyt_verify_log",
            self.metadata,
            autoload=True,
            aotoload_with=self.engine
        )

问题解决了，但是又报了另一个错误

C:\Users\EDY\PycharmProjects\airUI\database\message_database.py:47: SAWarning: Can't validate argument 'aotoload_with'; can't locate any SQLAlchemy dialect named 'aotoload'
  return Table(
(5163, '0', 0, '17600733931', '212156', '0', datetime.datetime(2022, 11, 17, 14, 0, 51))

翻译过来就是

无法验证参数“aotoload_with”;无法定位任何名为“aotoload”的SQLAlchemy方言

那我就把这个aotoload_with的属性去掉，确实没必要了，因为为了解决上一个问题，已经给metadata绑定了引擎，这里就没必要了

class MessageManage:
    def __init__(self):
        self.metadata = MetaData()
        self.engine = create_engine(DATABASE_URL, encoding='utf-8', max_overflow=0, pool_size=5, pool_timeout=30,
                                    pool_recycle=3600)
        self.metadata.bind=self.engine
        self.metadata.create_all(self.engine)

    def message_cls(self):
        return Table(
            "tyt_verify_log",
            self.metadata,
            autoload=True,
            # 去掉下面这段aotoload_with，因为元数据已经绑定引擎了
            # aotoload_with=self.engine
        )

再次执行，不报错了

(5163, '0', 0, '17600733931', '212156', '0', datetime.datetime(2022, 11, 17, 14, 0, 51))

最终版本

import asyncio
from sqlalchemy import MetaData, create_engine, Table, and_
import pymysql
from util.database_config import DATABASE_URL, MySQL
from util.logged import logged

# 使用pip install aiomysql 安装
pymysql.install_as_MySQLdb()


class MessageManage:
    def __init__(self):
        # 通过sqlalchemy获取数据库的元数据，元数据，说人话就是他能将你链接的数据库的所有的表的信息，都抽象出来
        self.metadata = MetaData()
        """
        通过create_engine初始化数据库链接，相当于创建链接
        pool_size： 是连接池的大小，默认为5个，0表示连接数无限制
        pool_recycle： MySQL 默认情况下如果一个连接8小时内容没有任何动作（查询请求）就会自动断开链接，
        出现 MySQL has gone away的错误。设置了 pool_recycle 后 SQLAlchemy 就会在指定时间内回收连接。如果设置为3600 就表示 1小时后该连接会被自动回收。
        pool_timeout: 定义超时时间
        max_overflow：超过连接池大小外最多创建的连接
        """
        self.engine = create_engine(DATABASE_URL, encoding='utf-8', max_overflow=0, pool_size=5, pool_timeout=30,
                                    pool_recycle=3600)
        # 为这个元数据绑定到引擎上，要不然这个元数据只是进行了创建，但是没有内容
        self.metadata.bind = self.engine
        # create_all方法, 默认会在创建表之间检测一下表是否存在, 不存在时才创建.
        self.metadata.create_all(self.engine)

    def message_cls(self):
        """
        初始化类tyt_verify_log表的抽象信息
        :return:
        """
        return Table(
            # 表名称
            "tyt_verify_log",
            # 绑定在哪个元数据下
            self.metadata,
            # 自动加载，就是将这个表下的结构自动生成一个python的对象，就不用我们去手动创建一个每一个人元素都与数据库表一一对应的对象了
            autoload=True,
        )


message_cls = MessageManage().message_cls()


async def get_message_by_id(id: str) -> int or bool:
    """
    查询id对应的短信记录
    :param id: id
    :return: 如果有则返回验证码，没有的话就返回False
    其中，这里使用了async和await来使用协程
    这个举例是获取唯一一条记录的
    """
    async with MySQL() as cur:
        if id:
            # 查询条件
            sql = message_cls.select(). \
                where(and_(message_cls.c.id == id,
                           message_cls.c.status == 0))
            # 通过游标获取唯一一条数据，由于fetch_one也是async异步的（点进去看一下源码就可以了），所以我们可以在这里使用await
            res = await cur.fetch_one(sql)
            print(res)
            result = dict(zip(res.keys(), res))
            return [result]