CODEWITHSUNDEEP
pythonsqlalchemy
Serge Tahé
Serge Tahé

Reputation: 2079

sqlalchemy : how can i map an existing class without modifying it?

I have a certain number of python classes that i would like to map to tables in a database with python sqlalchemy. I saw examples where the mapped class is derived from an sqlalchemy base class. I don't want to do that. Is there any other way ?

For example, how to map this simple class ?

class Person:
    def __init__(self, firstname: str = "x", name: str = "y", age: int = 0):
        self.firstname = firstname
        self.name = name
        self.age = age

    def __str__(self) -> str:
        return f"[{self.__firstname},{self.__name},{self.__age}]"

    @property
    def id(self):
       return self.__id

    @property
    def firstname(self) -> str:
        return self.__firstname

    @property
    def name(self) -> str:
        return self.__name

    @property
    def age(self) -> int:
        return self.__age

    # setters

    @id.setter
    def id(self, id: int):
        if not isinstance(id,int) or id<=0:
            raise MyException(f"...")

    @firstname.setter
    def firstname(self, firstname: str):
        if Utils.is_string_ok(firstname):
            self.__firstname = firstname.strip()
        else:
            raise MyException("...")

    @name.setter
    def name(self, name: str):
        if Utils.is_string_ok(name):
            self.__name = name.strip()
        else:
            raise MyException("...")

    @age.setter
    def age(self, age: int):
        error = False
        if isinstance(age, int):
            if age >= 0:
                self.__age = age
            else:
                error = True
        else:
            error = True
        if error:
            raise MyException("...")

I want to map it to a table with columns (col1,col2,col3,col4) for example (arbitrary names different from class properties).

Upvotes: 3

Views: 4677

Answers (1)

Serge Tah&#233;
Serge Tah&#233;

Reputation: 2079

For anyone interested, i finally got it (below i changed the identifiers of my working code to match the original post):

# imports
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.orm import mapper, sessionmaker

from Person import Person

# mysql database
engine = create_engine("mysql+mysqlconnector://root@localhost/dbpersonnes")

# metadata
metadata = MetaData()

# table
persons_table = Table("persons", metadata,
                        Column('col1', Integer, primary_key=True),
                        Column('col2', String(30), nullable=False),
                        Column("col3", String(30), nullable=False),
                        Column("col4", Integer, nullable=False)
                        )

# mapping
mapper(Person, persons_table, properties={
    'id': persons_table.c.col1,
    'firstname': persons_table.c.col2,
    'name': persons_table.c.col3,
    'age': persons_table.c.col4,
})

# session factory
Session = sessionmaker()
Session.configure(bind=engine)

# session
session = Session()

# insert
session.add(Personne(67, "x", "y", 10))
session.commit()

# query
personnes = session.query(Personne).all()

# logs
for personne in personnes:
    print(personne)

Upvotes: 6

Related Questions