Python与SQL深度融合实战案例：打造你的数据处理秘籍
今天，我们将通过一个实战案例，揭开Python与SQL在数据生成、入库、映射处理中的神秘面纱，让你的数据处理之路事半功倍！
一、用Python点燃数据魔法——生成随机数据与映射
首先，让我们借助Python的pandas和numpy库，快速生成一组随机数据，并通过字典映射为数据添加“魔法”值。
# 导入必要的库
import pandas as pd
import numpy as np
# 设定数据规模：生成20个随机整数
data_size = 20
column1_values = np.random.randint(1, 50, data_size) # 生成1到50之间的随机整数
# 用DataFrame存储数据，并命名列为 'Column1'
df = pd.DataFrame({'Column1': column1_values})
# 定义映射关系：特定数字映射到对应金额
price_mapping = {6: 30, 16: 30, 42: 30, 4: 20, 36: 20, 15: 10, 11: 100}
# 利用map方法，将映射结果赋值到新列 'Column2'；未匹配的自动填充为0
df['Column2'] = df['Column1'].map(price_mapping).fillna(0)
# 展示结果
print(df)
这段代码展示了如何通过简单几步，利用Python生成随机数据，并依据预设的映射关系为数据赋予全新的含义——这正是你掌控数据的第一步魔法！
二、轻松驾驭SQL——将数据稳稳入库
接下来，借助SQL语句，我们将数据“送进”数据库，让它们有个温暖的家。假设我们有两个表：
-
shuju 表的建表及数据插入 假设表结构如下：
CREATE TABLE shuju (
id INT AUTO_INCREMENT PRIMARY KEY,
Column1 INT
);
我们通过下面的INSERT语句，为表中填入示例数据：
INSERT INTO shuju (Column1) VALUES
(6),
(16),
(42),
(4),
(36),
(15),
(11),
(7),
(9),
(18),
(21),
(30),
(8),
(14),
(3),
(19),
(25),
(13),
(22),
(17);
数据库中执行上面脚本：
-
mapping 表的建表及数据插入 同样，创建映射表的结构如下：
CREATE TABLE mapping (
value INT PRIMARY KEY,
price INT
);
并插入相应的映射数据：
INSERT INTO mapping (value, price) VALUES
(6, 30),
(16, 30),
(42, 30),
(4, 20),
(36, 20),
(15, 10),
(11, 100);
数据库中执行上面脚本：
这两组SQL语句不仅展示了如何构建数据库表，更展示了如何将数据井然有序地存储到数据库中，为后续的数据映射操作打下坚实基础。
三、数据回流——Python读取数据库并精准映射
最后一步，我们再用Python将数据从数据库中读取回来，并根据数据库中的映射规则，将金额数据准确映射到原始数据上。操作如下：
import pandas as pd
import pymysql
from sqlalchemy import create_engine, text
# 数据库配置
db_config = {
'host': 'localhost',
'user': 'root',
'password': 'root',
'database': 'demo2',
'port': 3306,
'charset': 'utf8mb4'
}
try:
# 创建数据库连接引擎
engine = create_engine(
f"mysql+pymysql://{db_config['user']}:{db_config['password']}@"
f"{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
)
with engine.connect() as conn:
# 测试数据库连接是否成功（使用 text 包装 SQL 语句）
result = conn.execute(text("SELECT 1"))
print("测试连接结果：", result.fetchone())
# 使用 execute 获取 SHOW COLUMNS 信息，并手动构造 DataFrame
result_shuju = conn.execute(text("SHOW COLUMNS FROM shuju;"))
shuju_columns = pd.DataFrame(result_shuju.fetchall(), columns=result_shuju.keys())
result_mapping = conn.execute(text("SHOW COLUMNS FROM mapping;"))
mapping_columns = pd.DataFrame(result_mapping.fetchall(), columns=result_mapping.keys())
print("shuju 表字段：")
print(shuju_columns)
print("mapping 表字段：")
print(mapping_columns)
# 执行查询并获取结果
result_data = conn.execute(text("SELECT * FROM shuju;"))
result_mapping = conn.execute(text("SELECT value, price FROM mapping;"))
# 使用 fetchall 和 DataFrame 构造函数创建 DataFrame
df_data = pd.DataFrame(result_data.fetchall(), columns=result_data.keys())
df_mapping = pd.DataFrame(result_mapping.fetchall(), columns=result_mapping.keys())
# 检查 shuju 表中是否包含 'Column1' 列
if'Column1'notin df_data.columns:
raise KeyError("shuju 表中不存在 'Column1' 列，请检查表结构和列名。")
# 将 mapping 表数据转换为字典（如果 value 列有重复值，字典中只保留最后一条对应关系）
price_mapping = pd.Series(df_mapping['price'].values, index=df_mapping['value']).to_dict()
# 根据映射关系为 df_data 添加新列 'Column2'
df_data['Column2'] = df_data['Column1'].map(price_mapping).fillna(0)
# 输出最终结果
print("最终结果：")
print(df_data)
except Exception as e:
print("操作失败，错误信息：", str(e))
finally:
engine.dispose() # 关闭数据库连接
通过这一系列操作，我们不仅见证了数据从Python生成、存储到数据库，再回流至Python的全过程，更体验到了数据映射的高效与便捷。无论是数据清洗、报表生成还是复杂分析，Python与SQL的强强联合都能为你打开全新的大门！
