[ Python 文章收集 ] SQLAlchemy Core - Using UPDATE/DELETE Expression (4)

Source From Here 
Using UPDATE Expression 
The update() method on target table object constructs equivalent UPDATE SQL expression: 

view plaincopy to clipboardprint?

1. table.update().where(conditions).values(SET expressions)  

The values() method on the resultant update object is used to specify the SET conditions of the UPDATE. If left as None, the SET conditions are determined from those parameters passed to the statement during the execution and/or compilation of the statement; The where clause is an Optional expression describing the WHERE condition of the UPDATE statement. 

Following code snippet changes value of ‘lastname’ column from ‘Khanna’ to ‘Kapoor’ in students table: 

>>> stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor') 
>>> str(stmt) 
'UPDATE students SET lastname=%(lastname)s WHERE students.lastname = %(lastname_1)s'

The bound parameter lastname_1 will be substituted when execute() method is invoked. The complete update code is given below: 
- demo6.py 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import alias, select  
2. from sqlalchemy import text  
3. from sqlalchemy import create_engine, MetaData  
4.   
5. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
6. engine = create_engine(db_string, echo = True)  
7. meta = MetaData(bind=engine)  
8. meta.reflect()  
9. conn = engine.connect()  
10. students = meta.tables['students']  
11.   
12. update_stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname='Kapoor')  
13. conn.execute(update_stmt)  
14. select_stmt = students.select().where(students.c.lastname == 'Kapoor')  
15. result = conn.execute(select_stmt).fetchone()  
16. print(result)  

Execution result: 

... 
2019-03-08 13:23:39,631 INFO sqlalchemy.engine.base.Engine {'lastname_1': 'Kapoor'} 
(3, 'Ravi', 'Kapoor')

Note that similar functionality can also be achieved by using update() function in sqlalchemy.sql.expression module as shown below: 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql.expression import update  
2. stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')  

DELETE Expression 
In the previous chapter, we have understood what an Update expression does. The next expression that we are going to learn is Delete expression. The delete operation can be achieved by running delete() method on target table object as given in the following statement: 

view plaincopy to clipboardprint?

1. stmt = students.delete()  

In case of students table, the above line of code constructs a SQL expression as following: 

view plaincopy to clipboardprint?

1. 'DELETE FROM students'  

However, this will delete all rows in students table. Usually DELETE query is associated with a logical expression specified by WHERE clause. The following statement shows where parameter: 

view plaincopy to clipboardprint?

1. stmt = students.delete().where(students.c.id > 2)  

The resultant SQL expression will have a bound parameter which will be substituted at runtime when the statement is executed: 

view plaincopy to clipboardprint?

1. 'DELETE FROM students WHERE students.id > :id_1'  

Following code example will delete those rows from students table having lastname as ‘Khanna’: 
- demo7.py 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import alias, select  
2. from sqlalchemy import text  
3. from sqlalchemy import create_engine, MetaData  
4. from pprint import pprint  
5.   
6. db_string = "postgresql://postgres:<yourpassword≷@localhost/testdb"  
7. engine = create_engine(db_string, echo = True)  
8. meta = MetaData(bind=engine)  
9. meta.reflect()  
10. conn = engine.connect()  
11. students = meta.tables['students']  
12.   
13. delete_stmt = students.delete().where(students.c.lastname == 'Khanna')  
14. conn.execute(delete_stmt)  
15. s = students.select()  
16. result = conn.execute(s).fetchall()  
17. pprint(result)  

This message was edited 14 times. Last update was at 08/03/2019 13:40:57