2019年3月28日 星期四

[ Python 常見問題 ] SQLAlchemy equivalent to SQL “LIKE” statement?

Source From Here 
Question 
How do I carry out below query in SQLAlchemty
  1. testdb=# SELECT * FROM students;  
  2. id | name  | lastname  
  3. ----+-------+----------  
  4.   1 | Ravi  | Kapoor  
  5.   2 | Rajiv | Khanna  
  6.   3 | Komal | Bhandari  
  7.   4 | Abdul | Sattar  
  8.   5 | Priya | Rajhans  
  9. (5 rows)  
  10.   
  11. testdb=# SELECT * FROM students WHERE name LIKE 'R%';  
  12. id | name  | lastname  
  13. ----+-------+----------  
  14.   1 | Ravi  | Kapoor  
  15.   2 | Rajiv | Khanna  
  16. (2 rows)  
How-To 
Consider we have base.py
  1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
  2. from sqlalchemy import select, join, and_, or_  
  3.   
  4. db_string = "postgresql://postgres:password@localhost/testdb"  
  5. engine = create_engine(db_string, echo = True)  
  6. meta = MetaData(bind=engine)  
  7. meta.reflect()  
  8.   
  9. conn = engine.connect()  
  10. students = meta.tables['students']  
  11. addresses = meta.tables['addresses']  
  12. employee = meta.tables['employee']  
Then you can achieve by: 
>>> from base import *
>>> from sqlalchemy.sql import select
>>> stmt = select([students]).where(students.c.name.like('R%'))
>>> rst = conn.execute(stmt)
2019-03-28 20:04:04,731 INFO sqlalchemy.engine.base.Engine SELECT students.id, students.name, students.lastname
FROM students
WHERE students.name LIKE %(name_1)s
2019-03-28 20:04:04,731 INFO sqlalchemy.engine.base.Engine {'name_1': 'R%'}

>>> for r in rst:
... print(r)
...
(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')


Supplement 
SQLAlchemy doc - Query API 
SQLAlchemy ColumnOperators like

沒有留言:

張貼留言

[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...