Python中如何编写SQL查询语句？
table 如下 表名就叫 x_table 吧
date | name | count
‘20181018’ | laowang | 100
’20181018’ | laoli | 120
’20181016’ | laoli | 1440
’20181016’ | laowang | 108
期望查询到的结果是这样的
date | count1(laowang 的) | count2(laoli 的) | count1+count2
’20181018’ | 100 | 120 | 220
写的有点简陋
Python中如何编写SQL查询语句？
在Python里写SQL查询，直接用字符串就行，但更推荐用参数化查询来防SQL注入。比如用sqlite3库：
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 用问号占位符
user_id = 5
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
# 或者用命名占位符
cursor.execute("SELECT * FROM users WHERE id = :id", {'id': user_id})
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
用ORM框架比如SQLAlchemy的话，查询会更Pythonic：
from sqlalchemy import create_engine, MetaData, Table
engine = create_engine('sqlite:///example.db')
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)
# 直接写SQL
result = engine.execute("SELECT * FROM users WHERE id = :id", id=5)
# 用SQLAlchemy的查询API
from sqlalchemy import select
stmt = select(users).where(users.c.id == 5)
result = engine.execute(stmt)
for row in result:
print(row)
核心就两点：简单查询直接写字符串，复杂或需要防注入就用参数化。
<br>SELECT<br> <a target="_blank" href="http://s.date" rel="nofollow noopener">s.date</a> ,<br> sum(s.zhangsan) ,<br> sum(s.lisi) ,<br> sum(s.zhangsan) + sum(s.lisi) 'count1+count2'<br>FROM<br> (<br> SELECT<br> date ,<br> CASE<br> WHEN NAME = 'zhangsan' THEN<br> count<br> ELSE<br> 0<br> END 'zhangsan' ,<br> CASE<br> WHEN NAME = 'lisi' THEN<br> count<br> ELSE<br> 0<br> END 'lisi'<br> FROM<br> x_table<br> ) s<br>GROUP BY<br> <a target="_blank" href="http://s.date" rel="nofollow noopener">s.date</a>;<br><br><br>
)
Select
Date date,
Sum(case when name=‘ laowang ’ then count else 0 end) count1,
Sum(…) count2,
Sum(…)+Sum(…) count1and2
From
X_table
Group by
Date
