CODEWITHSUNDEEP
pythonpython-2.7sqlalchemyflask-sqlalchemy
user3527975
user3527975

Reputation: 1773

Updating multiple columns in Sqlalchemy

I have an app that runs on flask and uses sqlalchemy to interact with the data base. I want to update the columns of a table with the user specified values. The query that I am using is 

def update_table(value1, value2, value3):
     query = update(Table).where(Table.column1 == value1).values(Table.column2 = value2, Table.column3 = value3)

I am not sure if the way I am passing values is correct. Also the Table.column2/3 i=gives error saying Can't assign to function call . Where column2/3 are not functions, they're field names. So, how do I update multiple values and why is it giving error here?

PS: I referred the sqlalchemy doc

Upvotes: 5

Views: 22618

Answers (3)

gomeslhlima
gomeslhlima

Reputation: 141

Using your previous structure:

def update_table(value1, value2, value3):
     query = update(Table).where(Table.column1 == value1).values( { Table.column2 : value2, Table.column3 : value3 } )

Upvotes: 0

user3527975
user3527975

Reputation: 1773

Multiple columns can be updated using the Session as:

def update_table(session, val1, val2, val3):
  session.query(Table).filter_by(col=val1).update(dict(col2=val2,col3=val3))
  session.commit()

Upvotes: 10

MOCKBA
MOCKBA

Reputation: 1790

you can write more generic function which will accept your table object, WHERE criteria and the actual update values. Something like

def get_update_query(table_name, where_vals, update_vals):
  query = table_name.update()
  for k, v in where_vals.iteritems():
    query = query.where(getattr(table_name.c, k) == v)
  return query.values(**update_vals)

table = YourTable
where_vals = {'col1': 'foo', 'col2': 'bar'}
update_vals = {'col1': 'foo_updated', 'col2': 'bar_updated'}
res = get_update_query(YourTable, where_vals, update_vals)

# UPDATE "YourTable" SET col1=:col1, col2=:col2 WHERE "YourTable".col1 = :col1_1 AND "YourTable".col2 = :col2_1

Upvotes: 5

Related Questions