2019年3月7日 星期四

[ 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: 
  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 
  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: 
  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: 
  1. stmt = students.delete()  
In case of students table, the above line of code constructs a SQL expression as following: 
  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: 
  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: 
  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 
  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

沒有留言:

張貼留言

[Git 常見問題] error: The following untracked working tree files would be overwritten by merge

  Source From  Here 方案1: // x -----删除忽略文件已经对 git 来说不识别的文件 // d -----删除未被添加到 git 的路径中的文件 // f -----强制运行 #   git clean -d -fx 方案2: 今天在服务器上  gi...