How to Update SQLalchemy Row Entry

How to update SQLAlchemy row entry?

user.no_of_logins += 1
session.commit()

Flask-SQLalchemy update a row's information

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.

Updating a row using SQLAlchemy ORM

I believe you are looking for something like this for your update query:

session.query(FoobarModel).filter(FoobarModel.id == foobar_id).update({'name': 'New Foobar Name!'})

Since update() belongs to Query, and filter() does return a Query object, this will work, contrary to trying to call update() on your FoobarModel object (which does not have such a function) returned by Query.get(), see also here.

As for looping over your properties and assigning them by name, you could do this with setattr and a dict, like this:

foobar = session.query(FoobarModel).get(foobar_id)

props = {'name': 'my new name'}

for key, value in props.items():
    setattr(foobar, key, value)

session.commit()
session.flush()

This is obviously a little pointless with just one property, but maybe it will come in handy at some point.

SQLAlchemy how do I update only certain fields of my record

You need to get the record you want to update and then set the attributes:

def post(self, comp_id):
    form = self.form_class()

    if form.validate_on_submit():
        buyers_list = form.buyer_id.data.id

        company_info = Company.query.get(comp_id)
        company_info.email = form.email.data
        ....
        db.session.commit()

If you need to ignore blank fields you might be able to do something like:

 company_info = Company.query.get(comp_id)
 #Ignore form fields not in data model
 form_data = {i: form.data[i] for i in form.data if i not in ["csrf_token", "submit"]}
 #Remove empty fields
 new_data = {k: v for k, v in form_data.items() if v is not None}
 #Unpack into Company model
 company_info.update(**new_data)
 db.session.commit()

Updating specific row in SQLAlchemy

ormically, you don't use update(), you set attributes:

a_user = session.query(User).filter(User.id == 3).one()
a_user.name = "user"
session.commit()

easier method to update SQLAlchemy object from form data

You might want to take a look at flask-wtf's populate_obj function. Furthermore, the given example shows you how to fill the form by passing the requested database object to your form using the obj argument.

@app.route('/edit/<int:post_id>', methods=['GET', 'POST'])
def edit_post(post_id):
    post = BlogPost.query.get_or_404(post_id)
    form = CreatePostForm(request.form, obj=post)
    if form.validate_on_submit():
        form.populate_obj(post)
        db.session.commit()
        return redirect(url_for('show_post', index=post_id))
    return render_template('make-post.html', **locals())

How to update specific row in Sqlalchemy ORM for a non unique primary key

Figured it out, you can import the update function from sqlalchemy if you have an update that is more complicated than just using the primary key

    stmt = update(CategoryTable).where(CategoryTable.category_key == cat.category_key,
    CategoryTable.current_flag == '1', CategoryTable.effective_from == cat.effective_from).values(
        effective_to=effective_date, current_flag='0').execution_options(synchronize_session="fetch")
    db.execute(stmt)

Updating row in SqlAlchemy ORM

I assume that your intention is to use Object-Relational API.
So to update row in db you'll need to do this by loading mapped object from the table record and updating object's property.

Please see code example below.
Please note I've added example code for creating new mapped object and creating first record in table also there is commented out code at the end for deleting the record.

from sqlalchemy import Column, DateTime, Integer, String, Table, MetaData
from sqlalchemy.orm import mapper
from sqlalchemy import create_engine, orm

metadata = MetaData()

product = Table('product', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(1024), nullable=False, unique=True),

)

class Product(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name
    def __repr__(self):
        return "%s(%r,%r)" % (self.__class__.name,self.id,self.name)

mapper(Product, product)

db = create_engine('sqlite:////temp/test123.db')
metadata.create_all(db)

sm = orm.sessionmaker(bind=db, autoflush=True, autocommit=True, expire_on_commit=True)
session = orm.scoped_session(sm)

#create new Product record:
if session.query(Product).filter(Product.id==1).count()==0:

    new_prod = Product("1","Product1")
    print "Creating new product: %r" % new_prod
    session.add(new_prod)
    session.flush()
else:
    print "product with id 1 already exists: %r" % session.query(Product).filter(Product.id==1).one()

print "loading Product with id=1"
prod = session.query(Product).filter(Product.id==1).one()
print "current name: %s" % prod.name
prod.name = "new name"

print prod

prod.name = 'test'

session.add(prod)
session.flush()

print prod

#session.delete(prod)
#session.flush()

PS SQLAlchemy also provides SQL Expression API that allows to work with table records directly without creating mapped objects. In my practice we are using Object-Relation API in most of the applications, sometimes we use SQL Expressions API when we need to perform low level db operations efficiently such as inserting or updating thousands of records with one query.

Direct links to SQLAlchemy documentation:

• Object Relational Tutorial
• SQL Expression Language Tutorial

---

Related Topics