The replace() method on target desk object constructs equal UPDATE SQL expression.
table.update().where(conditions).values(SET expressions)
The values() technique on the resultant replace item is used to specify the SET conditions of the UPDATE. If left as None, the SET conditions are determined from those parameters exceeded to the statement throughout the execution and/or compilation of the announcement.
The where clause is an Optional expression describing the WHERE situation of the UPDATE announcement.
Following code snippet modifications cost of ‘lastname’ column from ‘Khanna’ to ‘Kapoor’ in college students desk −
stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')
The stmt item is an update object that translates to −
'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'
The certain parameter lastname_1 could be substituted whilst execute() approach is invoked. The whole update code is given underneath −
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students',
meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
conn = engine.connect()
stmt=students.update().where(students.c.lastname=='Khanna').values(lastname='Kapoor')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()
The above code presentations following output with second row displaying impact of replace operation as within the screenshot given −
[
(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Kapoor'),
(3, 'Komal', 'Bhandari'),
(4, 'Abdul', 'Sattar'),
(5, 'Priya', 'Rajhans')
]
Note that similar capability can also be performed with the aid of the use of update() characteristic in sqlalchemy.Square.Expression module as proven under −
from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')
or')