Use a for loop to set columns of a table

Question

I currently define the tables and columns of a PostgreSQL database like this:

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Float, Integer
Base = declarative_base()
class MyTable(Base):
    __tablename__ = "my_table"
    col_1 = Column(Integer, primary_key=True)
    col_2 = Column(Float)
    col_3 = Column(Float)
    col_4 = Column(Float)
    col_5 = Column(Float)
    # ....
    col_24 = Column(Float)
    col_25 = Column(Float)

Instead of writing the same identical lines multiple times, I would like to use a for loop that sets similar columns:

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Float, Integer
Base = declarative_base()
class MyTable(Base):
    __tablename__ = "my_table"
    col_1 = Column(Integer, primary_key=True)
    for ind in range(2, 26):
        # set col_$ind
        ????

What should I do put inside the for loop to set the attributes col_$ind ?

Does this answer your question? [Database on the fly with scripting languages](https://stackoverflow.com/questions/2580497/database-on-the-fly-with-scripting-languages) — rfkortekaas, May 31 '21 at 14:21
as per [this](https://stackoverflow.com/questions/2519807/setting-a-class-attribute-with-a-given-name-in-python-while-defining-the-class/20608050#20608050) comment, `vars()` may be the solution... — dada, May 31 '21 at 14:30

score 1 · Answer 1 · edited Jun 01 '21 at 05:53

Totally reasonable aim – as a software developer, your goal is to be efficient with your code. However, as you interact more deeply with relational databases, you may find value in explicit (though redundant) definitions.

Notwithstanding, here is how you may achieve loop-wise table definition:

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Float, Integer

Base = declarative_base()

# Define your class here
class MyTable(Base):
    __tablename__ = "my_table"
    
    # define your primary key
    col_1 = Column(Integer, primary_key=True, comment='primary key')


# Create columns with a loop
for i in range(2, 26):
    setattr(MyTable, f'col_{i}', Column(Float))

score 0 · Accepted Answer · answered Jun 01 '21 at 10:37

I ended up using vars():

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Float, Integer
Base = declarative_base()
class MyTable(Base):
    __tablename__ = "my_table"
    col_1 = Column(Integer, primary_key=True)
    for ind in range(2, 26):
        # set col_$ind
        vars()[f"col_{ind}"] = Column(Float)

Use a for loop to set columns of a table

2 Answers2