3

I'm just starting to learn SQL Alchemy and for my first project my goal is to create two separate SQLITE DB files, each with one table. On my first attempt I tried the following:

Base = declarative_base() 

class HostInfo(Base):
    __tablename__ = 'hosts'

    id = Column(Integer, primary_key = True)
    ...

host_info_engine = create_engine('sqlite:///hostinfo.db', echo=False)
Base.metadata.create_all(host_info_engine)


class RecoveryLogger(Base):
    __tablename__ = 'recovery_entries'

    id = Column(Integer, primary_key=True)
    ....

recovery_log_engine = create_engine('sqlite:///recovery_logger.db', echo=False)
Base.metadata.create_all(recovery_log_engine)

For the most part this worked, but one of the unintended side-effects was that it created two tables in hostinfo.db, and that's not what I wanted - I only wanted one table in each of the database files. After a bunch of head scratching, I was able to inspect Base and I found that it contained all of the columns from both of my class declarations. The way I went about fixing this was to insert another Base = declarative_base() statement before the second class declaration (RecoveryLogger) and that seemed to fix the issue.

It threw me for a loop because I didn't think that information from the child class would make its way back up to the parent class, but it seems like it does, or at least Base seem to be some kind of special object that grabs all the details from any class that is based off of it.

So, what is the right way to go about this? Was inserting a second Base = declarative_base() statement the correct way to go about it? Should I use a different name (instead of Base) for each declarative_base() assignment?

Thanks!

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Mike
  • 504
  • 7
  • 23
  • 1
    The declarative base class' [meta class](https://stackoverflow.com/questions/100003/what-are-metaclasses-in-python) allows it to do all kinds of "magic", such as keep a registry of all classes that inherit from it. – Ilja Everilä Jan 23 '20 at 05:38
  • So should I be creating a separate Base for each SQLITE DB that I'm using? – Mike Jan 23 '20 at 20:30
  • 1
    If they're not meant to use identical schemas, then I'd say yes. See https://stackoverflow.com/questions/36342716/when-connecting-to-multiple-databases-do-i-need-multiple-sqlalchemy-metadata-b. – Ilja Everilä Feb 06 '20 at 08:18
  • could you please explain why you want to get two database with only on table in each? It you just want to store two data type in two different files, SQL databases may not be the appropriate storage mode. – Tryph Feb 06 '20 at 15:23
  • @Ilja Everilä, thanks for confirming. In essence they'd be completely separate files that have tables that are unrelated. It just so happens that I'm creating both tables from within the same Python file, hence it seems to require multiple bases. – Mike Feb 06 '20 at 22:42
  • @Tryph because the tables are unrelated to each other, so it wouldn't make sense to put them in the same SQLITE file. And this is purely an example - what if I want 20 related tables in one file and 20 related tables in the other but I want to create them using just one Python script? Just because I'm using Python and/or running into some SQL Alchemy issue doesn't negate my need to use an SQL db. – Mike Feb 06 '20 at 22:45
  • @Mike IMO it is not a problem to put two unrelated tables in the same data base if they are used and/or produced by the same app. – Tryph Feb 07 '20 at 12:39
  • @Tryph I am aware of that. However, this is not a question asking for where the best place is to put my databases, it is a question of how to use SQL Alchemy in one Python file to create a different different database in two different files, nothing more. Further, I don't think this is an unreasonable question to ask, whether it be purely for educational reasons (to understand what the heck SQL Alchemy is doing), or because I have a legitimate use case (which I'm sure there are many). – Mike Feb 10 '20 at 00:47
  • @Mike this is why I posted comments and not an answer. – Tryph Feb 10 '20 at 06:54
  • 1
    Not sure if this aids your case, but will throw my 2 cents in here.. I learnt SQLAlchemy by using SQLACodeGen. https://pypi.org/project/sqlacodegen/ Have you tried seeing what that would generate off database metadata? Then compare that to your solution? – JGFMK Feb 10 '20 at 17:09
  • I'll take a look at that! Thanks – Mike Feb 11 '20 at 17:43

1 Answers1

1

Your method is fine. You are indeed supposed to declare separate Bases for individual databases. And it's probably a good idea to give the two Bases different names, e.g. HostBase and LoggerBase, to improve code extensibility and reusability. That way you can use the individual bases to add more tables to and drop from both databases (files) without confusion. Refactoring/reordering is also easier. The following is a reordering example.

HostBase = declarative_base()
LoggerBase = declarative_base() 

class HostInfo(HostBase):
    __tablename__ = 'hosts'

    id = Column(Integer, primary_key = True)
    ...

class RecoveryLogger(LoggerBase):
    __tablename__ = 'recovery_entries'

    id = Column(Integer, primary_key=True)
    ...

host_info_engine = create_engine(r'sqlite://hostinfo.db', echo=False)
HostBase.metadata.create_all(host_info_engine)

recovery_log_engine = create_engine(r'sqlite://recovery_logger.db', echo=False)
LoggerBase.metadata.create_all(recovery_log_engine)
Hurried-Helpful
  • 1,850
  • 5
  • 15