Python & SQLALchemy 学习笔记_查询

由於查询部分的篇幅相较於前几者较多，因此将查询的部分独立出来写
另外这边写的只有一些基础的操作，像是 group_by 等稍微进阶的操作
之後会另外写一篇文章来讲解

对於文章内容有疑问或是有更好的写法，欢迎留言讨论喔~

简易目录

一、建立表结构、资料

二、基本查询

• (一)、session.query(<表结构>).first()
• (二)、session.query(<表结构>).all()

三、过滤 filter_by

四、分页

五、排序

一、建立表结构、资料

由於查询的资料需要比较多，因此这边采用 政府资料开放平台 新创圆梦网-创业补给站 的 opendata 来当作操作的资料

(一)、建立表结构及资料表

这部份如果不是很清楚的话可以参考 这篇文章

• main.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import Integer, String, DATETIME, TEXT
from sqlalchemy.orm import sessionmaker


Base = declarative_base()
engine_url = "sqlite:///C:\\Users\\nick\\Desktop\\sqlalchemy_test\\test.db"
engine = create_engine(engine_url, echo=True)


class Test(Base):
    __tablename__ = "test"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(100))
    content = Column(TEXT)
    picture = Column(String(8182))  # Chrome 网址长度上限
    category = Column(String(100))
    youtube = Column(String(8182))  # Chrome 网址长度上限
    slideshare = Column(String(8182))  # Chrome 网址长度上限
    publish_time = Column(DATETIME)
    update_time = Column(DATETIME)


def create_table():
    Base.metadata.create_all(engine)


def drop_table():
    Base.metadata.drop_all(engine)


def create_session():
    Session = sessionmaker(bind=engine)
    session = Session()

    return session


if __name__ == '__main__':
    drop_table()
    create_table()

(二)、实际建立资料

• test.py

from main import create_session
from main import Test
import requests
import json
from datetime import datetime

url = "https://sme.moeasmea.gov.tw/startup/upload/opendata/gov_infopack_opendata.json"
res = requests.get(url)

datas = json.loads(res.text)

for item in datas:
    data_obj = {
        "title": item["标题"],
        "content": item["内容"],
        "picture": item["主图"],
        "category": item["分类"],
        "youtube": item["youtube嵌入代码"],
        "slideshare": item["slideshare嵌入代码"],
        "publish_time": datetime.strptime(item["建立时间"], "%Y%m%d%H%M%S"),
        "update_time": datetime.strptime(item["修改时间"], "%Y%m%d%H%M%S")
    }

    session = create_session()

    session.add(Test(**data_obj))
    session.commit()
    session.close()

二、基本查询

(一)、session.query(<表结构>).first()

• 语法: result = session.query(Test).first()
  注: first() 会回传第一笔资料，另外可使用 all() 来读取全部的资料，於第二段会讲解

• 利用栏位取得资料

from main import create_session
from main import Test

session = create_session()

result = session.query(Test).first()
print(result.id)
print(result.title)

output:

C:\Users\nick\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/nick/Desktop/sqlalchemy_test/test.py
1
test1133

Process finished with exit code 0

• 将读取回来的资料利用字典型态显示
  根据查到的资料，可以利用 dict 属性来将查询回来的资料存成字典的型态

from main import create_session
from main import Test
from pprint import pprint

session = create_session()

result = session.query(Test).first()

pprint(result.__dict__)  # pprint 用於美化输出

output:

C:\Users\nick\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/nick/Desktop/sqlalchemy_test/test.py
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x000001471DB84880>,
 'category': '活动专页',
 'content': '3213123213',
 'id': 1,
 'picture': 'http://sme.moeasmea.gov.tw/startup/upload/infopack/20200525112659agd.jpg',
 'publish_time': datetime.datetime(2020, 5, 25, 11, 26, 59),
 'slideshare': '',
 'title': 'test1133',
 'update_time': datetime.datetime(2020, 6, 9, 2, 46, 21),
 'youtube': ''}

Process finished with exit code 0

(二)、session.query(<表结构>).all()

此部分操作原理同 first()，只不过会将每一笔资料加到一个 list 当中
我们可以利用回圈进行拜访

from main import create_session
from main import Test

session = create_session()

result = session.query(Test).all()

for row in result:
    print(row.id, end=" ")
    print(row.title)

output:

C:\Users\nick\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/nick/Desktop/sqlalchemy_test/test.py
1 test1133
2 1988纾困振兴专区
3 青创指挥部
4 第一次创业就上手
5 国发基金创业天使计画
6 工商登记大补帖
7 开店创业的眉角
8 创业计画书撰写
9 小型企业创新研发计画(SBIR)
10 SBIR计画审查重点说明
11 青年从农，返乡创业
12 闭锁性股份有限公司
13 创业税务大小事
14 新创事业奖
15 FINDIT ：发现趋势 看见未来
16 劳动保险雇主怎麽保
17 创柜板
18 青年创业及启动金贷款

Process finished with exit code 0

三、过滤filter_by

以下的范例透过表的栏位来进行简单的筛选，日後有机会会再补上
进阶的 filter_by 用法，此方法同样适用於 first() 以及 all()

from main import create_session
from main import Test
from pprint import pprint

session = create_session()

result = session.query(Test).filter_by(id=3).first()
pprint(result.__dict__)

output:

C:\Users\nick\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/nick/Desktop/sqlalchemy_test/test.py
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x000001F6393666A0>,
 'category': '活动专页',
 'content': '青创指挥部 相关创业资源介绍',
 'id': 3,
 'picture': 'http://sme.moeasmea.gov.tw/startup/upload/infopack/20171018125533cmp.jpg',
 'publish_time': datetime.datetime(2017, 10, 18, 12, 55, 33),
 'slideshare': '5n28zWztX1TN1P',
 'title': '青创指挥部',
 'update_time': datetime.datetime(2020, 5, 15, 10, 52, 23),
 'youtube': ''}

Process finished with exit code 0

四、分页

当资料量过大，一次性将资料吐回来给使用者可能会造成使用者电脑无法负荷
因此可以透过分页的功能来减少回传的资料，其实就是 SQL 当中的 offset、limit

• offset(): 会过滤指定笔数，ex: offset(10) 就代表会从第 11 笔开始回传
• limit(): 代表取多少笔资料，ex: limit(10) 代表会回传 10 笔资料

简单范例:

from main import create_session
from main import Test

session = create_session()
result = session.query(Test).filter_by(category="创业补给站").offset(2).limit(5).all()

# 显示取得几笔
print(f"总共取得 {len(result)} 笔资料")

# 利用回圈拜访资料
for row in result:
    print(row.id, end=" ")
    print(row.title)

output:

C:\Users\nick\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/nick/Desktop/sqlalchemy_test/test.py
总共取得 5 笔资料
6 工商登记大补帖
7 开店创业的眉角
8 创业计画书撰写
9 小型企业创新研发计画(SBIR)
10 SBIR计画审查重点说明

Process finished with exit code 0

五、排序

顾名思义就是按照指定栏位将资料进行排序并输出，此操作同样能搭配 first()、all()

简单范例:
注: desc() 为降幂、asc()为升幂
注: 若资料栏位为 DATETIME 型态，即可根据时间先後顺序进行排序

from main import create_session
from main import Test

session = create_session()
result = session.query(Test).order_by(Test.publish_time.desc()).all()

# 利用回圈拜访资料
for row in result:
    print(row.id, end=" ")
    print(row.title, end=" ")
    print(row.publish_time)

output:

C:\Users\nick\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/nick/Desktop/sqlalchemy_test/test.py
1 test1133 2020-05-25 11:26:59
2 1988纾困振兴专区 2020-05-20 11:26:28
3 青创指挥部 2017-10-18 12:55:33
17 创柜板 2017-08-24 09:45:02
16 劳动保险雇主怎麽保 2017-08-24 09:42:59
15 FINDIT ：发现趋势 看见未来 2017-08-24 09:41:07
14 新创事业奖 2017-08-24 09:39:07
13 创业税务大小事 2017-08-24 09:34:24
12 闭锁性股份有限公司 2017-08-24 09:31:24
11 青年从农，返乡创业 2017-08-24 09:26:19
10 SBIR计画审查重点说明 2017-08-24 09:15:49
9 小型企业创新研发计画(SBIR) 2017-08-24 09:13:38
18 青年创业及启动金贷款 2017-08-24 09:09:13
5 国发基金创业天使计画 2017-08-23 05:00:21
8 创业计画书撰写 2017-08-23 04:54:41
6 工商登记大补帖 2017-08-23 04:52:31
7 开店创业的眉角 2017-08-23 04:49:57
4 第一次创业就上手 2017-08-23 04:46:28

Process finished with exit code 0