What is the most efficient way to populate class attributes with a row from a database query?

Question

Looking to have a database query set all the instance variables in a class:

Example:

def populate(self, if):
    #Perform mysql query

    self._name = row['name']
    self._email = row['email']
    ...

What's the fastest way to do this? Or is this not recommended (with a better approach)?

Thanks.

Answer 1

• It makes your code the most readable and predictable to do it manually like this. That way you know exactly what attributes exist and what attributes do not pretty easily.

• You can use setattr to automate tons of these.

  One fairly nice way would be to define a list attributes = ['name', 'email'...] as a class attribute then to do

  for name in self.attributes:
      setattr(self, "_" + name, row[name])
  

  You also can get the attributes from the query itself, but this will change depending on your query (especially if you're using SELECT * or anything like that) and your changing your database.

• I notice these attributes all have leading underscore. If this is a purely internal thing, consider whether an attribute rather than the query result itself or storing a dict wouldn't better suit your needs. Generally, attributes are supposed to be fairly static things.

• I hear oursql is nicer the MySQLdb

Answer 2

Look at sqlalchemy it's the most popular database abstraction for python.
If you really like the active record pattern there is a layer you can put on top of it called elixir.

I use sqlalchemy because it's a nice database abstraction and allows me to switch out databases. I use sqlite in memory for my tests and I can wedge it in to my code using sqlalchemy.