SQLAlchemy, Declarative, PostgreSQL: cannot create tables

Question

I created a number of classes I want to persist to the database. I would like to be able to run the app with a specific flag to take all those classes and create the corresponding tables in my db. To that end, I'm trying to import the classes and then call the Base.metadata.create_all(engine) method. However, that doesn't work. At the same time, when I call that from the file with each of those actual classes, the tables are created.

How do I go about the task of creating those tables? Should I try to create them from a single file/script, or do I add that line to each of those classes?

Here is an example of my code:

1) Item.py

from sqlalchemy import Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __repr__(self):
        return "<~ {} ~> name: {}".format(self.id, self.name)

2) main.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def main():
    from sqlalchemy.ext.declarative import declarative_base
    Base = declarative_base()
    engine = create_engine('postgresql+psycopg2://me:my_passl@localhost/my_first_database', echo=True)
    from Item import Item
    print(Item)
    print(Item.__table__)
    Base.metadata.create_all(engine)

main()

Sorry for the weird order of imports, but I read somewhere that the order of importing your classes and the Base matters, so I tried to play about with it.

One of the things I have found is that if I say import Item, and then Item.__base__.metadata.create_all(engine) everything works. But it looks like a slightly dirty hack. I thought that Base would be the same every time I create it. — Ibolit
– Ibolit, Commented Jul 5, 2016 at 14:37

Iron Fist · Accepted Answer · 2016-07-05 15:32:15Z

4

You already created Base in Item.py, just import it in main.py:

If main.py and Item.py are on the same folder, then in main.py:

from Item import Base, Item

And remove all imports inside main function, so main.py will look like:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from Item import Base, Item

def main():
    engine = create_engine('postgresql+psycopg2://me:my_passl@localhost/my_first_database', echo=True)
    print(Item)
    print(Item.__table__)
    Base.metadata.create_all(engine)

main()

answered Jul 5, 2016 at 15:32

Iron Fist

11k2 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ibolit Over a year ago

And what if I have a number of such DAOs? Do I create the Base elsewhere and then import it in each of these classes?

Iron Fist Over a year ago

Why don't you group them in one file..like models.py and have Base in this same file..?

Ibolit Over a year ago

It's a thought. Coming from Java, having several "public" classes in one file is not what comes into mind first :)

Iron Fist Over a year ago

Wel...if you had to separate them into multiple files, then gather them in one folder called for example DataAccessLayer and make it as a package folder putting an __init__.py file...then inside this file put all the necessary import statement to make it easier for your to just import it in your main app file by simply import DataAccessLayer as DB for example

DarkMath Over a year ago

Finally I found this! This solves my problem thank you very much!

Collectives™ on Stack Overflow

SQLAlchemy, Declarative, PostgreSQL: cannot create tables

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related