Efficient way to build a MySQL update query in Python

Question

I have a class variable called attributes which lists the instance variables I want to update in a database:

attributes = ['id', 'first_name', 'last_name', 'name', 'name_url',
              'email', 'password', 'password_salt', 'picture_id']

Each of the class attributes are updated upon instantiation.

I would like to loop through each of the attributes and build a MySQL update query in the form of:

UPDATE members SET id = self._id, first_name = self._first name ...

Thanks.

Answer 1

First question: will all the variables in attributes be used? If so the easiest way is probably to use the DBAPI's execute method.

assuming your cursor is instantiated as csr:

sql="UPDATE mytable SET phone=? where username=?"
variables = ("a phone number","a username")
csr.execute(sql,variables)

There are additional ways of doing it, such as using dictionaries, positional indicators, etc.. see DBAPI FAQ for more details.

Answer 2

class Ic(object):
  attributes = ['id', 'first_name', 'last_name', 'name', 'name_url',
              'email', 'password', 'password_salt', 'picture_id']

  def __init__(self): ...

  # and other methods that set all the attributes on self

  def updated(self):
    sqlbase = 'UPDATE members SET %s WHERE whateveryouwanthere'
    setpieces = []
    values = []
    for atr in self.attributes:
      setpieces.append('%s = ?' % atr)
      values.append(getattr(self, atr, None))
    return sqlbase % ', '.join(setpieces), values

The caller needs to build up theobj appropriately, then do

sql, values = theobj.updater()

and lastly call mycursor.execute(sql, values) on whatever DB API cursor it has to the database which needs to be updated (I have no idea about the WHERE conditions you want to use to identify the sepcific record to update, which is why I put a whatreveryouwanthere placeholder there;-).