CODEWITHSUNDEEP
sqlalchemy
kkkkkk
kkkkkk

Reputation: 692

sqlalchemy - how can I get update method result?

I execute update method:

# connect db to get `conn`
# get metadate which is table `account`
u = account.update().where(account.c.ID == 9).values(USERNAME='k9')
r = conn.execute(u)

How can I get the update is success or not? I checked the doc, but not found...

Or maybe I just do not care this??

r is a ResultProxy, update action do auto-commit and close.

THX:)

APPEND

THX @Lucas Kahlert's answer, good point!

rowcount satisfy my question's situation.

doc-rowcount

This attribute returns the number of rows matched, 
which is not necessarily the same as the number of rows that were actually modified

In [7]: u = account.update().\
        where( and_((account.c.ID == 4), (account.c.PWD =='4297f44b1'))).\
        values(PWD='hahahaha')

In [8]: print u
UPDATE `ACCOUNT` SET `PWD`=%s WHERE `ACCOUNT`.`ID` = %s AND `ACCOUNT`.`PWD` = %s

In [11]: rst = conn.execute(u)

In [12]: rst.rowcount  # found row and did update action
Out[12]: 1L

In [13]: rst = conn.execute(u)

In [14]: rst.rowcount  # found row but value is same, so do not do update action
Out[14]: 0L

Upvotes: 5

Views: 15735

Answers (1)

Lucas Kahlert
Lucas Kahlert

Reputation: 1237

You can check, how many rows are effected by your query using the rowcount attribute of the ResultProxy. The rowcount tells you, how many rows are matched by your WHERE condition. This is not the same as the affected rows (see this question), but should do the job in your case.

No row is affected

If no row is matched by your condition, the query will be executed regardless if data in the UPDATE statement is correct or not.

Constraint violation

If rows are matched and you try to update a row with an invalid set of data, SQLAlchemy will raise an constraint violation exception (eg. sqlalchemy.exc.IntegrityError). In this case you could catch the exception and use this for your "success" check.

If no constraint violation occured during your UPDATE, the statement silently succeeds.

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

class User(object):
    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

# create a connection to a in-memory sqlite database
# turn echo on to see the auto-generated SQL
engine = create_engine("sqlite://", echo=True)

# this is used to keep track of tables and their attributes
metadata = MetaData()
account = Table('account', metadata,
                Column('ID', Integer, primary_key=True),
                Column('USERNAME', String, unique=True))

# create the table and tell it to create it in the  database engine that is
# passed
metadata.create_all(engine)

# create a mapping between the account and the User class
mapper(User, account)

# populate database
engine.execute(account.insert().values(ID=9,  USERNAME='account-9'))
engine.execute(account.insert().values(ID=10, USERNAME='account-10'))

result = engine.execute(account.update().where(account.c.ID==9).values(USERNAME='k9'))
print('Matched rows:', result.rowcount) # 1

# This query will succeed, because there is not row with ID 20. It does not
# matter, if the USERNAME 'k9' is invalid (because there is already a 'k9')
result = engine.execute(account.update().where(account.c.ID==20).values(USERNAME='k9'))
print('Matched rows:', result.rowcount) # 0


# Constraint violation
# 
# will raise an:
#   sqlalchemy.exc.IntegrityError:
#       (IntegrityError) UNIQUE constraint failed: account.USERNAME
#       'UPDATE account SET "USERNAME"=? WHERE account."ID" = ?' ('k9', 10)
result = engine.execute(account.update().where(account.c.ID==10).values(USERNAME='k9'))

Upvotes: 6

Related Questions