In general you do not want to instantiate your object in Python to have the side effect of issuing a query to the database - The "default" way would be to have a __init__ function that receives the parameters - it can be like a dictionary, and set the values -
And another part of the code that queries the bank. This other part of the code may even be part of the class of your object, as a classmethod - but then it is clear that it will query the database:
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 you will not normally want to be repeating yourself by typing the attributes of each table, so you can use Python's introspection ways to fetch both the name of the attributes in a for loop - and this can be generic for more than a template.
But until then, you can go a long way in adapting your relational model to a Python object - you can put as much work as you want on top of it - but the most common thing is to use an existing Relational Object (ORM) adapter.
In the case of Python, the most popular one is 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 what would be the basis for your models using Python introspectivity - 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"
