My flask app has a table that I created using 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()
A library that I'm importing (authlib) connects to the same DB using 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()
During execution, it raises this error:
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.
How can I create a table in SQL Alchemy that contains such a foreign key?
You can use SQLAlchemy's reflection capabilities to create a Table instance for the existing class, and then assign the instance to a model's __table__
attribute:
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
Now MyTable
can be successfully referenced from within the OAuth2Client
model.
I'm pretty sure that
autoload=True
is now considered obsolete, and justautoload_with=db.engine
is sufficient.@GordThompson you're quite right,
autoload_with
is enough, andautoload
is deprecated in 1.4 (although it still appears in the example in the docsThanks for the heads-up. I have submitted a patch to update the documentation.