CODEWITHSUNDEEP
pythonpython-3.xdatabasesqlalchemy
ikreb
ikreb

Reputation: 2775

Generic database functions for serveral tables with sqlalchemy

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?

Upvotes: 1

Views: 846

Answers (2)

Amit Nanaware
Amit Nanaware

Reputation: 3356

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()

Upvotes: 1

van
van

Reputation: 77012

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

Then do you even need separate function for this?

Upvotes: 2

Related Questions