Creating Python objects from the database

Question

I’m learning Python now and want to create an object from information I picked up from the database, just passing the user ID.

I am using SQL Azure and the pyodbc driver. It is working well, but I wonder if this is the right way to this type of situation, or if there is already some "standard" defined.

User-class:

import pyodbc
from modulos import db

class User:
    def __init__(self, idusuario):
        query = """SELECT IDUSUARIO, CPF, EMAIL, NOME, SOBRENOME, DATA_NASCIMENTO
                FROM USUARIO WHERE IDUSUARIO = ?;"""

        cursor = db.connection.cursor()
        row = cursor.execute(query, (idusuario)).fetchone()

        self.idusuario = row.IDUSUARIO
        self.cpf = row.CPF
        self.email = row.EMAIL
        self.nome = row.NOME
        self.sobrenome = row.SOBRENOME
        self.data_nascimento = row.DATA_NASCIMENTO

    def __repr__ (self):
        return "idusuario: {}, cpf: {}, email: {}, nome: {}, sobrenome: {}, data de nascimento: {}.".format(self.idusuario, self.cpf, self.email, self.nome, self.sobrenome, self.data_nascimento)

App class:

from user import User

usuario = User(1)

print(usuario)

Answer 1

In general you don’t want instantiating your object in Python to have the side effect of issuing a query to the database - The "default" way would then be to have a function __init__ which receives the parameters - it can be like a dictionary, and configure the values -

And another part of the code that query the bank. This other part of the code can even be part of the class of your object, as a "classmethod" - but then it is clear that it will consult the bank:

class User:
    def __init__(self, idusuario=None, cpf=None, email=None, nome=None, sobrenome=None, data_nascimento=None):

        self.idusuario = idusuario
        self.cpf = cpf
        self.email = email
        self.nome = nome
        self.sobrenome = sobrenome
        self.data_nascimento = data_nascimento

    @classmethod
    def from_db(cls, id):
        query = """SELECT IDUSUARIO, CPF, EMAIL, NOME, SOBRENOME, DATA_NASCIMENTO
                FROM USUARIO WHERE IDUSUARIO = ?;"""

        cursor = db.connection.cursor()
        row = cursor.execute(query, (idusuario)).fetchone()
        return User(idusuario = row.IDUSUARIO, cpf=row.CPF, email=row.EMAIL sobrenome=row.SOBRENOME, data_nascimento=row.DATA_NASCIMENTO)

Of course, normally you don’t want to keep repeating yourself so much, typing the attributes of each table, so you can use Python’s introspective ways to search for both the name of the attributes in a loop for - and this can be generic for more than one model.

But until then, you can advance your adaptation of the relational model to a Python object - you can put as much work as you want on top of this - only the most usual is to use an existing Relational Object (ORM) adapter.

In the case of Python, the most popular is the Sqlalchemy - it really is a very powerful and complete ORM framework - and recommended.

The recommendation is to use Sqlalchemy, but just to give you an idea of how it would be a basis for your models using Python’s introspective capability - the following code is equivalent to the example I posted. Each new "model" with the same capabilities could be defined in 4 lines of code from here:

class Base:
    _fields = []
    _table = ""
    _idcol = ""

    def __init__(self, **kwargs):
        for key, value in kwargs.items():
            setattr(self, key, value)

    @classmethod
    def from_db(cls):
        query = """SELECT {fields} FROM {table} WHERE {idcol} = ?;""".format(
            fields=", ".join(upper(field) for field in cls._fields),
            table = cls._table,
            idcol=cls._idcol
        )
        cursor = db.connection.cursor()
        row = cursor.execute(query, (idusuario)).fetchone()
        return cls(**{field:getattr(row, field.upper()) for field in cls._fields})

class User(Base):
    _fields = "IDUSUARIO CPF EMAIL NOME SOBRENOME DATA_NASCIMENTO".lower().split()
    _idcol = "ideusuario"
    _table = "USUARIO"