Tukukkk

I'm trying to create two separate databases in a single file using SQLAlchemy. Here's the code I have so far:

from sqlalchemy import create_engine, Column, String, Integer, inspect

from sqlalchemy.ext.declarative import declarative_base



Base = declarative_base()



class Table1(Base):

    __tablename__ = 'table_1'

    id = Column(Integer, primary_key=True)

    name = Column(String)



class Table2(Base):

    __tablename__ = 'table_2'

    id = Column(Integer, primary_key=True)

    name = Column(String)





engine1 = create_engine('sqlite:///db1.db')

engine2 = create_engine('sqlite:///db2.db')

Base.metadata.drop_all(bind=engine1)

Base.metadata.drop_all(bind=engine2)

Base.metadata.create_all(bind=engine1)

Base.metadata.create_all(bind=engine2)



print(inspect(engine1).get_table_names())

# ['table_1', 'table_2']

print(inspect(engine2).get_table_names())

# ['table_1', 'table_2']

I want to create only Table1 in db1 and only Table2 in db2; however, I'm getting both tables in both databases.

Is there anyway to resolve this or do I need to create the databases in two separate files.

Your problem isn't caused by trying to create the two databases in the same module. Rather, you are calling create_all() on the same metadata object that has mapped both of the tables. E.g.

print(Base.metadata.tables)

result:

dict_keys(['table_1', 'table_2'])

From the docs about MetaData.create_all():

This method will issue queries that first check for the existence of
each individual table, and if not found will issue the CREATE
statements...

Key point is it checks for the existence of each table. So here:

Base.metadata.create_all(bind=engine1)

Base.metadata.create_all(bind=engine2)

... it first checks for both tables in the db referenced by engine1, doesn't find them and creates them. Then, it checks for both tables in the db referenced by engine2, doesn't find them, and creates them.

There's a couple of options.

Have different Base objects (i.e. a distinct MetaData instance) for each database:

Base1 = declarative_base()

Base2 = declarative_base()





class Table1(Base1):

    __tablename__ = 'table_1'

    id = Column(Integer, primary_key=True)

    name = Column(String)



class Table2(Base2):

    __tablename__ = 'table_2'

    id = Column(Integer, primary_key=True)

    name = Column(String)



engine1 = create_engine('sqlite:///db1.db')

engine2 = create_engine('sqlite:///db2.db')

Base1.metadata.drop_all(bind=engine1)

Base2.metadata.drop_all(bind=engine2)

Base1.metadata.create_all(bind=engine1)

Base2.metadata.create_all(bind=engine2)



print(inspect(engine1).get_table_names())

# ['table_1']

print(inspect(engine2).get_table_names())

# ['table_2']

Or, selectively create the tables while binding to the desired engine:

Base = declarative_base()



class Table1(Base):

    __tablename__ = 'table_1'

    id = Column(Integer, primary_key=True)

    name = Column(String)



class Table2(Base):

    __tablename__ = 'table_2'

    id = Column(Integer, primary_key=True)

    name = Column(String)





engine1 = create_engine('sqlite:///db1.db')

engine2 = create_engine('sqlite:///db2.db')

Base.metadata.drop_all(bind=engine1)

Base.metadata.drop_all(bind=engine2)

Base.metadata.tables['table_1'].create(bind=engine1)

Base.metadata.tables['table_2'].create(bind=engine2)



print(inspect(engine1).get_table_names())

# ['table_1']

print(inspect(engine2).get_table_names())

# ['table_2']