0

How can I generate a class/model based on a preexisting table without creating the class/model by hand. In my case this is only used on flat tables without any relations. Assuming I have the following table in my postgres database:

id  |  brand_name  |  cloth_type
1      nike           t-shirt
2      adidas         t-shirt
3      nike           skirt

How can I get an object/instance of the first row (id 1) without to write a model by myself? Is there a way to create an abstract class that is generated on the fly or where the attributes are added based on the table?

I found this, but could not really grasp how to implement it. https://docs.sqlalchemy.org/en/13/orm/extensions/automap.html

What I would like to achieve could look something like this (rough code)

engine = db_connect(db_config_section)  # creates engine based on config 
file
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
abstract_class = ...  # somehow generate abstract class
obj = session.query(abstract_class).get(1)

Thanks for possible replies.

Edit: In the linked solution there is still a class defined with a fixed class name and table name. That is not what I am searching for. The code I want should be indipendent from one table. For example the code that works for the cloth tabel (as in my example) should also work for the follwing table (car):

   id  |  brand_name  |  car_type | doors
    1      bmw           suv        4
    2      ford          pickup     2
    3      fiat          cuv        2
Wikktor
  • 151
  • 1
  • 10
  • If you're interested in initially generating the models based on your existing tables, have a look at https://stackoverflow.com/questions/1425412/reverse-engineer-sqlalchemy-declarative-class-definition-from-existing-mysql-dat – Ilja Everilä May 28 '19 at 17:43
  • @IljaEverilä Thanks for the hint but unfortunately this doesn't solve my problem. – Wikktor May 29 '19 at 11:46
  • Did you check https://stackoverflow.com/a/48363732/2681632? – Ilja Everilä May 29 '19 at 11:50
  • @IljaEverilä Yes, but it did not solve my problem because it seems it is for a different case, even if it is slightly similar. As far as I understood, this solution relies still on writing your own model/class. The model MyClass is still fixed for one and only one table which is declared in the Tabel() call. So you would have to write such a model for every table inside the DB. That is what I don't want. Is there an even more abstract solution? E.g. use the type function to generate a class based on the table and than retrieve the instances with sqlalchemy? – Wikktor May 29 '19 at 12:29
  • That's pretty much what the linked answer does, using the automap extension. It generates the model classes for you, in runtime. You'd not write the class code. – Ilja Everilä May 29 '19 at 12:41
  • @IljaEverilä Ah I think I'm finally wrapping my head around it. Suppose I have created that Table(object?). How can I access the table via a session. Because this naturally won't work : session.query(table_object).get(1). And thanks for your help and patience. – Wikktor May 29 '19 at 13:05

1 Answers1

2

Ok I solved it. As IljaEverilä sad, the solution is mostly given by the stackoverflow post he linked. I simpy had to adjust it a bit. I was a bit slow in understanding it, sorry for that. Here is my solution:

def create_class_by_table(table_name, engine):
      table = Table(table_name, Base.metadata, autoload=True, autoload_with=engine)
      class_name = table_name.capitalize()
      attributes = {'__table__': table}
      new_class = type(class_name, (Base,), attributes)
      return new_class

new_class = create_class_by_table(table_name, engine)  # table_name could be e.g. 'car'
obj = session.query(new_class).get(obj_id)
Wikktor
  • 151
  • 1
  • 10