Defining class sql-alchemy: loop to assign column types

Question

Is it possible to avoid typing out names and datatypes of columns when defining a class for sql alchemy, for example suppose you have:

from sqlalchemy import Column, Date, Integer, String, Numeric 
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Fruit(Base):

   __tablename__ = 'Fruits'

    # names and datatypes of columns
    date_of_record = Column(Date, primary_key=True)
    number_of_apples = Column(Integer)
    number_of_pears = Column(Integer)

Is it possible to recreate this last section using a loop? For example if you wanted to have the column names and types as an input:

column_names = ['date_of_record', 'number_of_apples', 'number_of_pears']
column_types = [Date, Integer, Integer]

class Fruit(Base):

  __tablename__ = 'Fruits'

  def __init__(self, column_names, column_types):
        for index, (name, type) in enumerate(zip(column_names, column_types)):
            if index == 0:   
                setattr(self, name, Column(type, primary_key = True))
            else:
                setattr(self, name, Column(type))

However this throws an ArgumentError: Mapper Mapper|Fruit|Fruits could not assemble any primary key columns for mapped table 'Fruits'

Is anyone able to provide a working example of how you can use column names and types as variables in class definition when using sql alchemy ?

This will not work - the framework expects columns definitions to be declared as class attributes in the class statement block (you can check SQLAlchemy source code to find out why), AND this is a very bad idea anyway wrt/ readabilty / maintainability. — bruno desthuilliers
This approach might create a number of unexpected challenges. Any libraries that rely on model definition. Automatic migration creation too. That doesn't sould like a good approach. What do you want to achieve ? — Andrew_Lvov
Technically you can do this using either a class decorator or a metaclass but I haven't thought how that'd impact inspection tools... — Jon Clements
So if you are uploading data using sql alchemy, do you have to manually tell sql alchemy the name of columns and its attributes within the class definition? Can you not do this via a loop? what if you wanted to one off upload hundreds of tables? — Mike Tauber
The ORM side is ill suited anyway for bulk jobs. Using Core you can create "one off" Table objects easily, or even the light weight table() constructs. — Ilja Everilä

diduk001 diduk001 · Accepted Answer · 2020-06-20T20:57:26

Actually yes, you can do this, but you need to create your database with Table() and sqlalchemy.orm.mapper.

You need to do something like this:

from sqlalchemy import create_engine, MetaData, Table, Column
from sqlalchemy import Integer, Date # Insert your types here
from sqlalchemy.orm import mapper

engine = create_engine('sqlite:///fruits.db') # Here you need to insert your own path to db

column_names = ['date_of_record', 'number_of_apples', 'number_of_pears']
column_types = [Date, Integer, Integer]

# Adding columns

columns = list()
for index, (name, type) in enumerate(zip(column_names, column_types)):
            if index == 0:   
                columns.append(Column(name, type, primary_key = True))
            else:
                columns.append(Column(name, type))

metadata = MetaData()
fruits_table  = Table('Fruits', metadata, *columns)
metadata.create_all(engine) # Create table

class Fruit(Base):
    # You can make initialization method here
    pass

print(mapper(Fruit, fruits_table)) # Create a Mapper and print it

Now, to make a session, you need to do this:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine) # Use an engine with your database
session = Session()

test_fruit = Fruit() # You can initialize it, if you will make __init__ method

test_fruit.number_of_apples = 2
test_fruit.number_of_pears = 3

session.add(test_fruit)
session.commit()

I hope, my answer helps you!

Defining class sql-alchemy: loop to assign column types

1 Answers