Generic database functions for serveral tables with sqlalchemy

Question

I have a base table and serveral tables for professors, students, ... und use for this the single table inheritance from sqlalchemy.

from sqlalchemy import create_engine
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

base = declarative_base()


class TableBase(base):
    __tablename__ = 'base_table'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)


class Student(TableBase):
    __tablename__ = 'student_table'
    id = Column(Integer, ForeignKey(TableBase.id), primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity': 'student_table'
    }


class Professor(TableBase):
    __tablename__ = 'professor_table'
    id = Column(Integer, ForeignKey(TableBase.id), primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity': 'professor_table'
    }


class Database:
    def __init__(self):
         db_string = "postgresql+psycopg2://user:secret@localhost:5432/<database>"
         engine = create_engine(db_string)

         Session = sessionmaker(engine)
         self.session = Session()
         base.metadata.create_all(engine)

    def add_entry(self, data, table):
        table_entry = self.create_table_entry(data, table)
        self.session.add(table_entry)
        self.session.commit()

    def add_entries(self, data_list, table):

        entries = []
        for data in data_list:
            table_entry = self.create_table_entry(data, table.__class__.__name__)
            entries.append(table_entry)

        self.session.add_all(entries)
        self.session.commit()


    def create_table_entry(self, data, table):
        table_entry = table(
            id=data["id"],
            subject=data["subject"],
            email=data["email"]
        )
        return table_entry


def main():
    student_table = Student()
    professor_table = Professor()


    db = Database()
    data = {"id": 42, "name": "John", "email": ""}
    db.create_table_entry(data, student_table)
    db.create_table_entry(data, professor_table)



if __name__ == "__main__":
    main()

But I don't know how I could create the create_table_entry function with using different object classes.

What are the best practice way for this?

van · Accepted Answer · 2021-05-05 09:18:15Z

2

def create_table_entry(data, table):
    return table(**data)

Then do you even need separate function for this?

answered May 5, 2021 at 9:18

van

77.5k13 gold badges174 silver badges179 bronze badges

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

2 Comments

ikreb Over a year ago

I get 'Student' object is not callable.

van Over a year ago

you should not send and instance of Student (which you create in your code with student_table = Student()). You should send Student as it is: db.create_table_entry(data, Student)

Amit Nanaware · Accepted Answer · 2021-05-05 09:18:43Z

1

you can use sqlalchemy insert command like this:

from sqlalchemy import insert
def add_row(data, table):
    self.session.execute(insert(table), data)
    self.session.commit()

answered May 5, 2021 at 9:18

Amit Nanaware

3,3961 gold badge8 silver badges19 bronze badges

6 Comments

ikreb Over a year ago

What type has table?

Amit Nanaware Over a year ago

@ikreb table is your sqlalchemy table model i.e. Student

ikreb Over a year ago

I got

sqlalchemy.exc.ArgumentError: subject table for an INSERT, UPDATE or DELETE expected, got <__main__.NewTable object at 0x7f06ebad3220>

.

Amit Nanaware Over a year ago

How you are calling this function?

Amit Nanaware Over a year ago

add_row({"id": 0, "name": "John", "email": ""}, Student)

|

Collectives™ on Stack Overflow

Generic database functions for serveral tables with sqlalchemy

2 Answers 2

2 Comments

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Related