Flask-SQLalchemy update a row s information

Question

How can I update a row s information    For example I d like to alter the name column of the row that has the id 5

User · Answer

There is a method update on BaseQuery object in SQLAlchemy  which is returned by filter by  num rows updated   User query filter by username  admin   update dict email  my new email example com     db session commit    The advantage of using update over changing the entity comes when there are many objects to be updated  If you want to give add user permission to all the admins  rows changed   User query filter by role  admin   update dict permission  add user    db session commit    Notice that filter by takes keyword arguments  use only one    as opposed to filter which takes an expression

User · Answer

Models py define the serializers  def default o      if isinstance o   date  datetime          return o isoformat    class User db Model        tablename    user     id   db Column db Integer  primary key True  autoincrement True                          def serializers self          dict val   quot id quot  self id  quot created by quot  self created by  quot created at quot  self created at  quot updated by quot  self updated by  quot updated at quot  self updated at         return json loads json dumps dict val default default    In RestApi  We can update the record dynamically by passing the json data into update query  class UpdateUserDetails Resource       auth token required    def post self         json data   request get json         user id   current user id       try           instance   User query filter User id  user id           data instance update dict json data            db session commit            updateddata instance first            msg   quot msg quot   quot User details updated successfully quot   quot data quot  updateddata serializers             code 200       except Exception as e           print e           msg     quot msg quot    quot Failed to update the userdetails  please contact your administartor  quot            code 500       return msg

User · Answer

This does not work if you modify a pickled attribute of the model   Pickled attributes should be replaced in order to trigger updates   from flask import Flask from flask ext sqlalchemy import SQLAlchemy from pprint import pprint  app   Flask   name    app config  SQLALCHEMY DATABASE URI      sqllite     tmp users db  db   SQLAlchemy app    class User db Model       id   db Column db Integer  primary key True      name   db Column db String 80   unique True      data   db Column db PickleType         def   init   self  name  data           self name   name         self data   data      def   repr   self           return   lt User  r gt     self username  db create all      Create a user  bob   User  Bob       db session add bob  db session commit      Retrieve the row by its name  bob   User query filter by name  Bob   first   pprint bob data           Modifying data is ignored  bob data  foo     123 db session commit   bob   User query filter by name  Bob   first   pprint bob data           Replacing data is respected  bob data     bar   321  db session commit   bob   User query filter by name  Bob   first   pprint bob data       bar   321     Modifying data is ignored  bob data  moo     789 db session commit   bob   User query filter by name  Bob   first   pprint bob data       bar   321

User · Answer

Just assigning the value and committing them will work for all the data types but JSON and Pickled attributes  Since pickled type is explained above I ll note down a slightly different but easy way to update JSONs    class User db Model       id   db Column db Integer  primary key True      name   db Column db String 80   unique True      data   db Column db JSON   def   init   self  name  data       self name   name     self data   data   Let s say the model is like above    user   User  Jon Dove     country   Sri Lanka    db session add user  db session flush   db session commit     This will add the user into the MySQL database with data   country   Sri Lanka    Modifying data will be ignored  My code that didn t work is as follows   user   User query   filter User name   Jon Dove   data   user data data  province      south  user data   data db session merge user  db session flush   db session commit     Instead of going through the painful work of copying the JSON to a new dict  not assigning it to a new variable as above   which should have worked I found a simple way to do that  There is a way to flag the system that JSONs have changed   Following is the working code   from sqlalchemy orm attributes import flag modified user   User query   filter User name   Jon Dove   data   user data data  province      south  user data   data flag modified user   data   db session merge user  db session flush   db session commit     This worked like a charm   There is another method proposed along with this method here Hope I ve helped some one

User · Answer

Retrieve an object using the tutorial shown in the Flask-SQLAlchemy documentation  Once you have the entity that you want to change  change the entity itself  Then  db session commit     For example   admin   User query filter by username  admin   first   admin email    my new email example com  db session commit    user   User query get 5  user name    New Name  db session commit     Flask-SQLAlchemy is based on SQLAlchemy  so be sure to check out the SQLAlchemy Docs as well

[python] Flask-SQLalchemy update a row's information

Examples related to python

Examples related to sqlalchemy

Examples related to flask-sqlalchemy