CODEWITHSUNDEEP
pythonsqlalchemy
Mike Tauber
Mike Tauber

Reputation: 59

Defining class sql-alchemy: loop to assign column types

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 ?

Upvotes: 1

Views: 931

Answers (1)

diduk001
diduk001

Reputation: 218

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!

Upvotes: 1

Related Questions