如何使用 Python 抓取媒体文件?

pythonserver side programmingprogramming更新于 2026/2/15 7:08:17

简介

在现实世界的企业业务环境中，大多数数据可能不会存储在文本或 Excel 文件中。基于 SQL 的关系数据库(例如 Oracle、SQL Server、PostgreSQL 和 MySQL)被广泛使用，并且许多替代数据库已经变得非常流行。

数据库的选择通常取决于应用程序的性能、数据完整性和可扩展性需求。

如何做到这一点..

在此示例中，我们将介绍如何创建 sqlite3 数据库。默认情况下，sqllite 随 python 安装一起安装，不需要任何进一步的安装。如果您不确定，请尝试以下操作。我们还将导入 Pandas。

将数据从 SQL 加载到 DataFrame 中相当简单，并且 pandas 有一些函数可以简化该过程。

import sqlite3
import pandas as pd
print(f"Output \n {sqlite3.version}")

输出

2.6.0

输出

# 连接对象
conn = sqlite3.connect("example.db")
# 客户数据
customers = pd.DataFrame({
"customerID" : ["a1", "b1", "c1", "d1"]
, "firstName" : ["Person1", "Person2", "Person3", "Person4"]
, "state" : ["VIC", "NSW", "QLD", "WA"]
})
print(f"Output \n *** Customers info -\n {customers}")

输出

*** Customers info -
customerID firstName state
0 a1 Person1 VIC
1 b1 Person2 NSW
2 c1 Person3 QLD
3 d1 Person4 WA

# orders data
orders = pd.DataFrame({
"customerID" : ["a1", "a1", "a1", "d1", "c1", "c1"]
, "productName" : ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})

print(f"Output \n *** orders info -\n {orders}")

输出

*** orders info -
customerID productName
0 a1 road bike
1 a1 mountain bike
2 a1 helmet
3 d1 gloves
4 c1 road bike
5 c1 glasses

# write to the db
customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)

输出

# 编写一个 SQL 来获取数据。
q = """
select orders.customerID, customers.firstName, count(*) as productQuantity
from orders
left join customers
on orders.customerID = customers.customerID
group by customers.firstName;
"""

输出

# run the sql.
pd.read_sql_query(q, con=conn)

示例

7.将所有内容放在一起。

import sqlite3
import pandas as pd
print(f"Output \n {sqlite3.version}")
# 连接对象
conn = sqlite3.connect("example.db")
# 客户数据
customers = pd.DataFrame({
"customerID" : ["a1", "b1", "c1", "d1"]
, "firstName" : ["Person1", "Person2", "Person3", "Person4"]
, "state" : ["VIC", "NSW", "QLD", "WA"]
})

print(f"*** Customers info -\n {customers}")

# 订单数据
orders = pd.DataFrame({
"customerID" : ["a1", "a1", "a1", "d1", "c1", "c1"]
, "productName" : ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})

print(f"*** orders info -\n {orders}")

# 写入数据库
customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)

# 编写一个 sql 来获取数据。
q = """
select orders.customerID, customers.firstName, count(*) as productQuantity
from orders
left join customers
on orders.customerID = customers.customerID
group by customers.firstName;

"""

# 运行 SQL。
pd.read_sql_query(q, con=conn)

输出

2.6.0
*** Customers info -
customerID firstName state
0 a1 Person1 VIC
1 b1 Person2 NSW
2 c1 Person3 QLD
3 d1 Person4 WA
*** orders info -
customerID productName
0 a1 road bike
1 a1 mountain bike
2 a1 helmet
3 d1 gloves
4 c1 road bike
5 c1 glasses
customerID firstName productQuantity
____________________________________
0      a1         Person1     3
1 c1 Person3 2
2 d1 Person4 1