Warm tip: This article is reproduced from serverfault.com, please click

mysql-使用外键创建不是由SQLAlchemy创建的表的模型

(mysql - Create a model with a foreign key to a table not created by SQLAlchemy)

发布于 2020-11-27 23:43:53

我的 Flask 应用程序具有我使用mysqlconnector创建的表:

import mysql.connector
from .config import host, user, passwd, db_name

connection_pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_size=8,
    host=host,
    user=user,
    password=passwd,
    database=db_name)
connection_object = connection_pool.get_connection()
cursor = connection_object.cursor(buffered=True)

cursor.execute(
            """CREATE TABLE IF NOT EXISTS MyTable (
                ID INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL UNIQUE
            )"""
connection_object.commit()

我要导入的库(authlib)使用SQL连接到同一数据库

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from authlib.integrations.sqla_oauth2 import OAuth2ClientMixin

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://user:pwd@host/db'
db = SQLAlchemy (app)

class OAuth2Client(db.Model, OAuth2ClientMixin):
    __tablename__ = 'oauth2_client'

    id = db.Column(db.Integer, primary_key=True)
    smthg_id = db.Column(
        db.Integer, db.ForeignKey('MyTable.id', ondelete='CASCADE'))
    smthg = db.relationship('MyTable')

client = OAuth2Client()
db.session.add (client)
db.session.commit()

在执行过程中,将引发以下错误:

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class OAuth2Client->oauth2_client, expression 'MyTable' failed to locate a name ("name 'MyTable' is not defined"). If this is a class name, consider adding this relationship() to the <class '__main__.OAuth2Client'> class after both dependent classes have been defined.

如何在SQL Alchemy中创建包含此类外键的表?

Questioner
Brainless
Viewed
1
snakecharmerb 2020-12-12 23:41:04

你可以使用SQLAlchemy的反射功能为现有类创建Table实例,然后将该实例分配给模型的__table__属性:

import sqlalchemy as sa

...

# Load the existing table into the db object's metadata
mytable = sa.Table('MyTable', db.metadata, autoload_with=db.engine)


# Create a model over the table.
class MyTable(db.Model):
    __table__ = mytable

现在MyTable可以从OAuth2Client模型内部成功引用