假设数据库中有以下数据:
code | name | color | capacity |
---|---|---|---|
0001 | iPhone13 Pro Max | 远峰蓝色 | 512 |
0002 | iPhone13 | 午夜色 | 128 |
0003 | iPhone13 mini | 星光色 | 256 |
我们希望从数据库中取出code为0001和0002两条数据的code和name字段,并转为如下格式
[
{
'code':'0001',
'name':'iPhone13 Pro Max'
},
{
'code':'0002',
'name':'iPhone13'
},
]
以下操作默认数据库为MySQL
创建session实例
>>> import urllib
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>> user = 'xxxx'
>>> password = 'xxxx'
...
>>> engine = create_engine(f'mysql+pymysql://{user}:{urllib.parse.quote_plus(password)}@{address}:{port}/{database}')
>>> Session = sessionmaker(engine)
>>> session = Session()
取数
SQL方式:
>>> my_query = session.execute("select code, name from material where code in ('0001', '0002')")
ORM方式:
>>> from sqlalchemy import MetaData, Table
>>> METADATA = MetaData(engine)
>>> table = Table('material', METADATA, autoload=True)
>>> my_query = session.query(table).with_entities(table.c.name).filter(table.c.code.in_(['0001','0002']))
转换
>>> [dict(zip(row.keys(), row)) for row in my_query.all()]
关于下面代码中的with_entities、in_
session.query(table).with_entities(table.c.name).filter(table.c.code.in_(['0001','0002']))
参考:
https://stackoverflow.com/a/8603129/7151777
https://stackoverflow.com/a/12054872/7151777
关于
[dict(zip(row.keys(), row)) for row in my_query.all()]