到目前为止，我还不能让Flask-MySQL反射正常工作

Question

我一直在搜索StackOverflow问题和阅读SQLAlchemy和Flask-SQLAlchemy文档，但仍然没有弄清楚如何让反射工作(仍然是SQLAlchemy的新手)。

当我尝试使用引擎映射表时，我得到错误"sqlalchemy.exc.ArgumentError: Mapper Mapper|User|user无法为映射表‘user’组装任何主键列“。尽管如此，user将列'id‘作为数据库中的主键。我不确定是不是还有什么事需要我先做。我曾想，如果我可以反射，它将自动为我的用户模型提供以数据库列命名的类属性，而我不必手动定义它们。

以下是我到目前为止拼凑的代码：

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import mapper

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://" + dbUser + ":" + dbPass + "@" + dbAddress + ":3306/" + dbName

app.config["SQLALCHEMY_ECHO"] = True

db = SQLAlchemy(app)
engine = db.engine
meta = db.metadata

现在，我知道db会给我一个很好的数据库会话。我还不确定我在引擎和meta上做错了什么。我已经看到引擎被用来以不同的方式创建上下文，但我想我是用下面这一行(从上面)创建的：

db = SQLAlchemy(app)

这里是我试图反映一个模型类的地方：

class User(db.Model):
     try:
         self = db.Model.metadata.tables('user', metadata)
         #self = Tadb.Model.metadata.tables('user', meta, autoload=True, autoload_with=engine, extend_existing=True)
         #Table('user', metadata, autoload_with=engine, extend_existing=True)
         #self = Table('user', meta, autoload_with=engine, extend_existing=True)
         #self.metadata.reflect(extend_existing=True, only=['user'])
    except Exception as e:
         print("In init User(): failed to map table user - " + str(e))

我在这一行得到了映射器错误(来自上面)：

self = db.Model.metadata.tables('user', metadata) 

我也尝试过其他行，但它似乎不知道表是什么……我有Flask-SQLAlchemy 2.3.2。

我在这里犯了什么明显的错误吗？

Answer 1

您可以尝试在代码中反映id字段(或其在数据库中的真实名称)和所有需要的字段：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), nullable=False)

Answer 2

这花了我几天的时间，阅读了30多个Flask Q/A，深入了SQLAlchemy和StackOverflow -SQLAlchemy文档中的一些小洞，尝试并丢弃了相当多的代码片段。这是我拼凑出来的。注意:这是Flask-MySQL版本2.3.2。

我认为Flask-SQLAlchemy在应用程序初始化后提供引擎和元数据是相当不寻常的，但他们页面上的文档让他们从SQLAlchemy导入不同的引擎和元数据模块。不仅如此，他们还导入了一个单独的会话引擎，我刚刚使用Flask初始化的数据库上下文来获取一个会话，它可以工作。

不仅如此，这还完成了StackOverflow状态下99%的Q/As不可能完成的事情-- Flask-SQLAlchemy --数据库表的自动映射/反射。也就是说，我根本不需要在类上声明属性，这些属性直接来自数据库。

代码如下：

import requests
import json
from flask import Flask, render_template, request, redirect, jsonify
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
#The file I am importing below is named config and is in the same folder as app.py.
#It has json formatted text and looks like this (without the '#' signs, and remove the <> signs that surround
#the places you need to insert the db address, username, password, and database name (instance name - 
#database servers can have multiple databases on them, each one is called an instance / has a different name))
#{
#"dbAddress" = "<some IP or URL to your database server>",
#"dbName" = "<database name>",
#"dbUser" = "<database user name>",
#"dbPass" = "<dbPass>"
#}
with open('config') as data_file:
    data = json.load(data_file)
    dbAddress = data["dbAddress"]
    dbName = data["dbName"]
    dbUser = data["dbUser"]
    dbPass = data["dbPass"]
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://" + dbUser + ":" + dbPass + "@" + dbAddress + ":3306/" + dbName
app.config["SQLALCHEMY_ECHO"] = True


db = SQLAlchemy(app)
db.Model.metadata.reflect(bind=db.engine)


class User(db.Model):
    __tablename__ = 'user'

    def __init__(self, db, username, password, email):
        try:
            self = db.session.query(User).filter(User.username==username) #.one()
        except Exception as e:
            print("In init User():  Failed to load an existing user into the model for user '" + username + "' " + str(e))
        self.username=username
        self.password=password
        self.emailAddress=emailAddress
        try:
            db.session.add(self)
            db.session.commit()
            print("In init User():  Inserted or updated user '" + username + "'")
            return True
        except Exception as e:
            print("In init User(): insert or update exception on user '" + username + "': " + str(e))
            return False


    def delete(db, userid):
        try:
            self = db.session.query(User).filter(User.id == userid).one()
            db.session.delete(self)
            db.session.commit()
        except Exception as e:
            print("In User.delete(): failed to delete userid '" + self.id + "', username '" + self.username + "': " + str(e))


    def getAllUsers(db):
        return db.session.query(User).all()


    #Setting this to blank would "logout" the user.
    #This is because csrf_protect prevents POST requests from going through other
    #than login and signup.
    def updateSessionToken(db, userid, token):
        try:
            self = db.session.query(User).filter(User.id == userid).one()
            self.sessionToken = token
            db.session.add(self)
            db.session.commit()
            print("In User.updateSessionToken(): Successfully updated the token for userid '" + userid + "'.")
            return True
        except Exception as e:
            print("In User.updateSessionToken() failed to update token: " + str(e))
            return False


    def checkSessionToken(db, userid, givenToken):
        try:
            user = db.session.query(User).filter(User.id == userid).one()
        except Exception as e:
            print("In checkSessionToken(): issue looking up userid: " + userid + ": " + str(e))
        if user:
            if user.sessionToken == givenToken:
                print("In User.checkSessionToken():  token match confirmed for userid '" + user.id + "', username '" + user.username + "'.")
                return True
            else:
                print("In checkSessionToken() - token and given token do not match")
        else:
            print("In checkSessionToken() - user by given id '" + userid + "' not found.")
        return False