sqlalchemy:相同的表但不同的数据库引擎

bqujaahr  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(487)

我有一个使用sqlalchemy的项目,它连接到两个不同的数据库,并且都有相同的表名 sample_table . 以下是当前设置:


# In config/base.py

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

...
...
...

db1_engine = create_engine(app.config['DB1_URI'], echo = False, pool_recycle = 1800)

if not database_exists(db1_engine.url):
    create_database(db1_engine.url)

print(database_exists(db1_engine.url))

db2_engine = create_engine(app.config['DB2_URI'], echo = False, pool_recycle = 1800)

if not database_exists(db2_engine.url):
    create_database(db2_engine.url)

print(database_exists(db2_engine.url)
...
...
...

# In database1/sample_table.py

from sqlalchemy import Column, String, Integer
from config.base import Base

class SampleTable(Base):
   __tablename__ = 'sample_table'
   id = Column(Integer(), primary_key=True)
   ...
   ...

# In database2/sample_table.py

from sqlalchemy import Column, String, Integer
from config.base import Base

class SampleTable(Base):
   __tablename__ = 'sample_table'
   id = Column(Integer(), primary_key=True)
   ...
   ...

# In controllers/db1.py

from config.base import db1_engine
from database1.sample_table import SampleTable

generate_db1_tables():
   if not db1_engine.dialect.has_table(db1_engine.connect(), "sample_table"):
    SampleTable.__table__.create(db1_engine)

# In controllers/db2.py

from config.base import db2_engine
from database2.sample_table import SampleTable

generate_db2_tables():
   if not db2_engine.dialect.has_table(db2_engine.connect(), "sample_table"):
    SampleTable.__table__.create(db2_engine)

# In app/app.py

from controllers.db1 import generate_db1_tables
from controllers.db2 import generate_db2_tables
...
...
generate_db1_tables()
generate_db2_tables()
...
...

当我运行上述代码时,我得到:

sqlalchemy.exc.InvalidRequestError: Table 'sample_table' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

但当我加上 __table_args__ = {'extend_existing': True} 在每个模型中, sample_tabledatabase2 在中有相同的字段 sample_tabledatabase1 . 它们有不同的结构。
在这样的项目中,最佳实践是什么?
注意,创建数据库和表的目的是模拟localhost中的工作。生产中已经存在数据库和表。
谢谢!

oaxa6hgo

oaxa6hgo1#

如果有人遇到相同的情况,我通过更新以下内容修复了错误:


# In config/base.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import database_exists, create_database

...
...
...

db1_engine = create_engine(app.config['DB1_URI'], echo = False, pool_recycle = 1800)
if not database_exists(db1_engine.url):
    create_database(db1_engine.url)

print(database_exists(db1_engine.url))
...
...
...
Db1Base = declarative_base()
Db2Base = declarative_base()

# In database1/sample_table.py

from sqlalchemy import Column, String, Integer
from config.base import Db1Base

class SampleTable(Db1Base):
...
...
...

# In database2/sample_table.py

from sqlalchemy import Column, String, Integer
from config.base import Db2Base

class SampleTable(Db2Base):
...
...
...

相关问题