Flask-SQLAlchemy多个数据库并绑定 [英] Flask-SQLAlchemy multiple databases and binds
问题描述
URI:
SQLALCHEMY_DATABASE_URI =mssql + pymssql:// user:password123@127.0 .0.1 / DbOne
SQLALCHEMY_BINDS = {
sql_server:mysql + pymysql:// user:password123@127.0.0.1/DbTwo
}
Models.py
$ b $ (db.Model):
__tablename__ =crim_mappings
id = db.Column(db.Integer,primary_key = True)$ b
class CrimMappings $ b date_mapped = db.Column(db.Date)
county_id = db.Column(db.Integer)
state = db.Column(db.String(20))
county = db .Column(db.String(100))
AgentID = db.Column(db.String(100),unique = True)
CollectionID = db.Column(db.String(100))
ViewID = db.Column(db.String(100))
$ b $ class LicenseType(db.Model):
__bind_key__ =sql_server
__table__ = db.Model.metadata.tables [sbm_agents]
但是它会抛出 KeyErro r
表示找不到'sbm_agents'表,它应该在那里,因为我已经指定绑定键指向sql_server绑定。
__ init __。py
from os.path import join,dirname,abspath
from flask_admin从project.apps.flask_apps.user_app.forms导入Admin
从flask_admin.contrib.sqla导入UserAdminForm
从项目导入导入ModelView
app_factory,db
from project.apps.flask_apps.admin_own.views从project.apps.flask_apps.admin_own.models导入AdminHome,Utilities
从project.apps.flask_apps.admin_own导入CredentCheckMappings,AllAgents,LicenseTypes
.forms从project.apps.flask_apps.user_app.models中导入MasterAgentForm,AgentMappingsModelView,AllLicenseForm
import用户
$ b def create_application():
config_path = join(dirname (abspath(__ file__)),config,project_config.py)
app = app_factory(config_ path = config_path)
admin = Admin(app,template_mode =bootstrap3,base_template =base_templates / admin_base.html,
index_view = AdminHome())
with app.app_context :
db.create_all()
db.Model.metadata.reflect(db.engine)
admin.add_view(MasterAgentForm(AllAgents,db.session))
admin.add_view (UserAdminForm(Users,db.session))
admin.add_view(Utilities(name =Utilities,endpoint =utilities))
admin.add_view(AgentMappingsModelView(CredentCheckMappings,db.session))
admin.add_view(AllLicenseForm(LicenseTypes,db.session))
返回应用程序
application = create_application()
if __name__ ==__main__:
application.run(host =0.0.0.0,port = 5000,debug = True)
我在这里错过了什么?
我已经试过这个:
现有数据库周围的示例flask sqlalchemy示例
但即时得到的 KeyError
做了一些改变或缺少一个步骤?
编辑: 对于想知道我如何解决这个问题的人来说,
我直接进入了SqlAlchemy,并从sqlalchemy导入了create_engine,元数据
从sqlalchemy.orm中获得了
pre $从sqlalchemy.ext.automap import import scoped_session,sessionmaker
从urllib.parse导入automap_base
import quote_plus
engine = create_engine(mssql + pyodbc:///?odbc_connect = + quote_plus(DRIVER = {FreeTDS}; SERVER = 172.1.1.1; PORT = 1433; DATABASE = YourDB; UID = user; PWD = Password))
metadata =元数据(引擎)
Session = scoped_session(sessionmaker(bind = engine))
$ b LicenseType = Table(sbm_agents,metadata,autoload = True)
这种方式我不反映整个数据库,只选择反映某些表。因为事实证明,反映整个数据库是缓慢的。
虽然这种方法有点棘手,因为你必须配置 FREETDS
但它的可执行性刚开始有点繁琐和混乱
db.reflect )
而不是 db.Model.metadata.reflect(db.engine)
。 URI:
SQLALCHEMY_DATABASE_URI = "mssql+pymssql://user:password123@127.0.0.1/DbOne"
SQLALCHEMY_BINDS = {
"sql_server": "mysql+pymysql://user:password123@127.0.0.1/DbTwo"
}
Models.py
class CrimMappings(db.Model):
__tablename__ = "crim_mappings"
id = db.Column(db.Integer, primary_key=True)
date_mapped = db.Column(db.Date)
county_id = db.Column(db.Integer)
state = db.Column(db.String(20))
county = db.Column(db.String(100))
AgentID = db.Column(db.String(100), unique=True)
CollectionID = db.Column(db.String(100))
ViewID = db.Column(db.String(100))
class LicenseType(db.Model):
__bind_key__ = "sql_server"
__table__ = db.Model.metadata.tables["sbm_agents"]
however it throws me a KeyError
saying the 'sbm_agents' table is not found which it should be there because I've specified the bind key to point to the sql_server bind.
__init__.py
from os.path import join,dirname,abspath
from flask_admin import Admin
from project.apps.flask_apps.user_app.forms import UserAdminForm
from flask_admin.contrib.sqla import ModelView
from project import app_factory, db
from project.apps.flask_apps.admin_own.views import AdminHome, Utilities
from project.apps.flask_apps.admin_own.models import CredentCheckMappings, AllAgents, LicenseTypes
from project.apps.flask_apps.admin_own.forms import MasterAgentForm, AgentMappingsModelView, AllLicenseForm
from project.apps.flask_apps.user_app.models import Users
def create_application():
config_path = join(dirname(abspath(__file__)), "config", "project_config.py")
app = app_factory(config_path=config_path)
admin = Admin(app, template_mode="bootstrap3", base_template="base_templates/admin_base.html",
index_view=AdminHome())
with app.app_context():
db.create_all()
db.Model.metadata.reflect(db.engine)
admin.add_view(MasterAgentForm(AllAgents, db.session))
admin.add_view(UserAdminForm(Users, db.session))
admin.add_view(Utilities(name="Utilities", endpoint="utilities"))
admin.add_view(AgentMappingsModelView(CredentCheckMappings, db.session))
admin.add_view(AllLicenseForm(LicenseTypes, db.session))
return app
application = create_application()
if __name__ == "__main__":
application.run(host="0.0.0.0", port=5000, debug=True)
what am I missing here? I've tried this: flask sqlalchemy example around existing database
but im getting the KeyError
did something change or im missing a step?
Edit:
For people wanting to know how I got around this. I went straight to SqlAlchemy and did this
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.automap import automap_base
from urllib.parse import quote_plus
engine = create_engine("mssql+pyodbc:///?odbc_connect="+ quote_plus("DRIVER={FreeTDS};SERVER=172.1.1.1;PORT=1433;DATABASE=YourDB;UID=user;PWD=Password"))
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine))
LicenseType= Table("sbm_agents", metadata, autoload=True)
that way im not reflecting the entire database and only choose to reflect certain tables. because it turns out that reflecting the entire database is slow.
although this approach is a little tougher because you have to configure FREETDS
but its doable just a little tedious and confusing at first
Use db.reflect()
instead of db.Model.metadata.reflect(db.engine)
.
这篇关于Flask-SQLAlchemy多个数据库并绑定的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!