在机器学习项目中数据集往往不是天生就放在一个干净的 CSV 文件里等着我们去读取。更多时候数据散落在 API 接口返回的 JSON 字符串中或者静静地躺在数据库的某张表里。如果我们不能熟练地把 JSON 转换成可分析的结构化数据或者写不出高效的 SQL 查询去提取特征那么后续的建模工作就会寸步难行。这是 100 天机器学习挑战的第 16 天本文我们聚焦数据处理环节中两个最常用的数据格式与技术JSON 与 SQL。我会用通俗的语言解释它们是什么并通过 Python 代码演示如何在机器学习项目中实际应用它们。无论你是刚入门机器学习的新手还是需要系统复习数据预处理的老手这篇文章都能给你一个完整的实操参考。我们不需要安装复杂的软件只需要一个 Python 环境加上 Pandas 和 SQLite3 这两个库就能完成大部分演示。1. 为什么机器学习需要处理 JSON 与 SQL很多同学在学习机器学习时会把大部分精力放在算法原理和模型调参上却忽略了数据获取和数据清洗的重要性。实际上一个真实机器学习项目的流程往往是这样的业务数据源 - 数据抽取 - 数据清洗 - 特征工程 - 建模 - 评估 - 上线数据源可以是日志文件、数据库、第三方 API也可以是爬虫抓取的网页。在这些数据源中JSON 和 SQL 占据了非常大的比重。为什么这么说JSON 是接口数据的标准格式。现在几乎所有 Web API 返回的数据都是 JSON 格式。比如你要获取天气数据、股票行情、社交媒体评论返回给你的基本都是 JSON。机器学习要使用这些外部数据第一步就是解析 JSON。SQL 是业务数据的主要存储方式。企业内部的数据绝大多数存放在关系型数据库中如 MySQL、PostgreSQL、SQL Server、SQLite。要提取这些数据进行建模就必须掌握 SQL 查询。数据预处理是机器学习中最耗时的一环。有研究表明数据科学家 80% 的时间花在数据准备上而 JSON 解析和 SQL 查询是数据准备中最基础的能力。所以掌握 JSON 和 SQL 并不是在偏离机器学习而是在打机器学习的基础。2. JSON 基础与 Python 解析2.1 JSON 是什么JSONJavaScript Object Notation是一种轻量级的数据交换格式。它源于 JavaScript但现在已经成为跨语言、跨平台通用的数据格式标准。JSON 的结构非常简洁主要有两种数据结构对象Object由花括号{}包裹内部是一组键值对。数组Array由方括号[]包裹内部是一组有序的值。一个完整的 JSON 数据看起来是这样的{ users: [ {id: 1, name: Alice, age: 28, skills: [Python, SQL]}, {id: 2, name: Bob, age: 32, skills: [Java, Spark]} ], total: 2 }JSON 支持的数据类型包括字符串、数字、布尔值、数组、对象和 null。2.2 JSON 与 Python 数据结构的对应关系JSON 之所以流行是因为它和 Python 的数据结构高度契合。我们来看一张对应表JSON 类型Python 类型说明{}对象dict字典键值对集合[]数组list列表有序序列字符串str文本数字int/float整数或浮点数true/falseTrue/False布尔值nullNone空值在 Python 中解析 JSON我们一般使用标准库json。核心函数有三个json.loads()把 JSON 字符串解析成 Python 对象。json.load()从文件读取 JSON 并解析。json.dumps()把 Python 对象转换成 JSON 字符串。下面我们写一个最小示例。import json # 模拟从 API 返回的 JSON 字符串 json_str { users: [ {id: 1, name: Alice, age: 28, skills: [Python, SQL]}, {id: 2, name: Bob, age: 32, skills: [Java, Spark]} ], total: 2 } # 解析 JSON 字符串 data json.loads(json_str) # 查看解析后的数据类型 print(type(data)) # 输出class dict # 访问嵌套数据 print(data[users][0][name]) # 输出Alice # 把 Python 对象转换回 JSON 字符串 json_output json.dumps(data, ensure_asciiFalse, indent2) print(json_output)这里重点说一下json.dumps()的两个常用参数ensure_asciiFalse允许输出中文字符而不是转义成\uXXXX的 ASCII 形式。indent2美化输出格式方便阅读和调试。2.3 从 JSON 文件读取数据并转成 DataFrame在实际项目中我们拿到 JSON 后通常要把它转成 Pandas 的 DataFrame才能进行后续的清洗和分析。import json import pandas as pd # 假设有一个 JSON 文件data.json with open(data.json, r, encodingutf-8) as f: data json.load(f) # 如果 JSON 的根节点是一个列表可以直接传入 DataFrame df pd.DataFrame(data[users]) print(df.head())输出如下id name age skills 0 1 Alice 28 [Python, SQL] 1 2 Bob 32 [Java, Spark]这里有一个常见的坑如果 JSON 中的某个字段本身是列表比如skills直接转成 DataFrame 后该字段会以列表形式存存储。如果希望把列表展开成多行可以使用explode()方法df_exploded df.explode(skills) print(df_exploded)这样一行包含多技能的记录就会被拆分成多行每条技能一行。这在特征工程中非常常用。2.4 嵌套 JSON 的规范化真实 API 返回的 JSON 往往比上面的示例复杂得多经常会出现多层嵌套。比如下面这种结构{ order_id: A001, customer: { name: Alice, contact: { phone: 123456, email: aliceexample.com } }, items: [ {product: 笔记本, price: 5999, quantity: 1}, {product: 鼠标, price: 99, quantity: 2} ] }对于这种嵌套 JSON直接用pd.DataFrame(json)会出现问题因为customer字段是一个字典items字段是一个列表无法直接对齐。Pandas 提供了json_normalize()方法在新版本中推荐使用pd.json_normalize()可以自动把嵌套的 JSON 展开成扁平化的表格。import pandas as pd data { order_id: A001, customer: { name: Alice, contact: { phone: 123456, email: aliceexample.com } }, items: [ {product: 笔记本, price: 5999, quantity: 1}, {product: 鼠标, price: 99, quantity: 2} ] } # 规范化嵌套 JSON并指定 items 列表下的记录如何展开 df pd.json_normalize( data, record_pathitems, meta[order_id, [customer, name], [customer, contact, email]] ) print(df)输出product price quantity order_id customer.name customer.contact.email 0 笔记本 5999 1 A001 Alice aliceexample.com 1 鼠标 99 2 A001 Alice aliceexample.comrecord_path指定要展开的列表字段meta指定要保留的上级字段。嵌套字段用列表形式逐级指定比如[customer, name]表示取customer下的name。这种方式在处理电商订单、用户行为日志、多级评论等场景时十分好用。3. SQL 基础与 Python 操作3.1 SQL 在机器学习流程中的角色SQLStructured Query Language是关系型数据库的标准查询语言。在机器学习项目中SQL 主要用于两个场景从数据库提取训练数据比如从订单表、用户表、商品表中提取特征生成训练集。在线推理时的特征查询模型上线后需要实时从数据库拉取最新特征进行预测。掌握 SQL 的基础语法能让我们从海量数据中高效地筛选出所需数据而不是把所有数据都加载到内存中再过滤。3.2 Python 操作 SQLite为了演示方便本文使用 SQLite 作为示例数据库。SQLite 是 Python 内置支持的轻量级数据库无需额外安装服务非常适合学习和本地开发。Python 操作 SQLite 的标准方式是使用内置模块sqlite3。我们先用一段代码创建一个示例数据库和一张用户表。import sqlite3 # 连接数据库如果文件不存在会自动创建 conn sqlite3.connect(ml_demo.db) cursor conn.cursor() # 创建用户表 cursor.execute( CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT, signup_date TEXT ) ) # 插入示例数据 users_data [ (1, Alice, 28, 北京, 2023-01-15), (2, Bob, 32, 上海, 2023-02-20), (3, Charlie, 25, 广州, 2023-03-10), (4, David, 40, 深圳, 2022-12-01), (5, Eve, 35, 北京, 2023-05-18) ] cursor.executemany(INSERT INTO users VALUES (?, ?, ?, ?, ?), users_data) conn.commit() # 关闭连接 conn.close()executemany()可以批量插入多条记录参数使用?占位符可以避免 SQL 注入风险。这一点在后面的最佳实践中还会再提到。3.3 在 Python 中执行查询接下来从表中查询数据并转成 DataFrame。import sqlite3 import pandas as pd conn sqlite3.connect(ml_demo.db) # 查询所有用户 df_users pd.read_sql_query(SELECT * FROM users, conn) print(df_users)pd.read_sql_query()是 Pandas 提供的方法可以直接执行 SQL 语句并将结果转换为 DataFrame非常方便。我们再创建一个订单表演示多表关联查询。import sqlite3 conn sqlite3.connect(ml_demo.db) cursor conn.cursor() cursor.execute( CREATE TABLE IF NOT EXISTS orders ( order_id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT, amount REAL, order_date TEXT ) ) orders_data [ (101, 1, 笔记本, 5999, 2023-06-01), (102, 1, 鼠标, 99, 2023-06-02), (103, 2, 键盘, 299, 2023-06-03), (104, 3, 显示器, 1499, 2023-06-04), (105, 5, 笔记本, 5999, 2023-06-05), (106, 5, 耳机, 499, 2023-06-06) ] cursor.executemany(INSERT INTO orders VALUES (?, ?, ?, ?, ?), orders_data) conn.commit() conn.close()3.4 核心 SQL 操作在机器学习特征提取中最常用的 SQL 操作包括过滤与排序SELECT * FROM users WHERE city 北京 AND age 25 ORDER BY age DESC;聚合统计按城市统计用户数量、平均年龄SELECT city, COUNT(*) AS user_count, AVG(age) AS avg_age FROM users GROUP BY city;多表连接将用户表和订单表连接找出每个用户的订单总额SELECT u.id, u.name, u.city, SUM(o.amount) AS total_spent, COUNT(o.order_id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name, u.city;我们可以在 Python 中通过 Pandas 直接执行这条 SQLimport sqlite3 import pandas as pd conn sqlite3.connect(ml_demo.db) query SELECT u.id, u.name, u.city, SUM(o.amount) AS total_spent, COUNT(o.order_id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name, u.city df_features pd.read_sql_query(query, conn) print(df_features)输出id name city total_spent order_count 0 1 Alice 北京 6098.0 2 1 2 Bob 上海 299.0 1 2 3 Charlie 广州 1499.0 1 3 4 David 深圳 NaN 0 4 5 Eve 北京 6498.0 2这样我们就从原始的订单表中提取出了用户维度的特征。这正是机器学习特征工程中非常常见的操作把行为数据聚合成用户画像特征。注意LEFT JOIN保证左表的记录都会保留即使右表没有匹配记录。David没有订单所以total_spent是NULL在后续处理中需要填充或删除。4. 实战案例从 API JSON 到特征表入库上面介绍了 JSON 和 SQL 的基础操作接下来我们用一个完整的实战案例把两者串起来从模拟的 API 拉取用户行为数据JSON 格式解析后清洗再写入 SQLite 数据库最后通过 SQL 查询生成供机器学习使用的特征表。4.1 模拟 API 返回的 JSON 数据假设有一个返回用户浏览记录的 API每次返回 10 条记录记录中包含用户 ID、浏览商品 ID、商品类别、浏览时长和浏览时间。import json import random from datetime import datetime, timedelta # 模拟生成一条浏览记录 def generate_records(n, start_user1): records [] categories [electronics, clothing, books, home, sports] for i in range(n): user_id random.randint(start_user, start_user 20) product_id random.randint(1000, 9999) category random.choice(categories) duration round(random.uniform(5, 600), 2) # 随机生成过去 7 天内的一个时间 time datetime.now() - timedelta(daysrandom.randint(0, 7), hoursrandom.randint(0, 23), minutesrandom.randint(0, 59)) records.append({ user_id: user_id, product_id: product_id, category: category, duration: duration, view_time: time.strftime(%Y-%m-%d %H:%M:%S) }) return records # 模拟第一次调用 API 返回的数据 api_response_1 { code: 0, message: success, data: generate_records(10) } print(json.dumps(api_response_1, ensure_asciiFalse, indent2))这段代码模拟了一个标准 API 返回结构外层是状态码和消息内层是数据列表。这和我们实际工作中遇到的 API 结构非常相似。4.2 解析 JSON 并存入 SQLite现在我们把解析出来的数据写入 SQLite 数据库。import sqlite3 import pandas as pd # 解析 JSON records api_response_1[data] df_records pd.DataFrame(records) # 连接数据库 conn sqlite3.connect(behavior.db) # 写入 DataFrame 到 SQLite 表 df_records.to_sql(view_log, conn, if_existsreplace, indexFalse) # 验证 df_check pd.read_sql_query(SELECT * FROM view_log LIMIT 10, conn) print(df_check) conn.close()df.to_sql()是 Pandas 提供的便捷方法可以把 DataFrame 直接写入数据库表。参数说明如下name表名。con数据库连接。if_existsreplace如果表已存在则删除重建。indexFalse不把 DataFrame 的索引写入数据库。4.3 使用 UPDATE 处理增量数据现实场景中API 返回的数据往往是增量数据需要追加到已有表中而不是每次都重建。这时可以使用if_existsappend# 模拟第二次调用 API 返回的数据 api_response_2 { code: 0, message: success, data: generate_records(10, start_user30) } df_records_2 pd.DataFrame(api_response_2[data]) conn sqlite3.connect(behavior.db) df_records_2.to_sql(view_log, conn, if_existsappend, indexFalse) conn.close()不过要注意简单使用append可能会导致重复数据。在真实项目中通常会根据主键判断记录是否已存在或者先按user_id和view_time去重后再插入。更严谨的做法是使用 SQL 的INSERT OR REPLACE或MERGE语法但具体取决于数据库类型。4.4 通过 SQL 生成特征表数据分析阶段我们需要从浏览日志中提取用户维度的特征比如每个用户的总浏览时长每个用户的浏览次数每个用户浏览的商品类别数每个用户最近一次浏览时间我们可以用一条 SQL 完成这些聚合。import sqlite3 import pandas as pd conn sqlite3.connect(behavior.db) query SELECT user_id, COUNT(*) AS view_count, SUM(duration) AS total_duration, AVG(duration) AS avg_duration, COUNT(DISTINCT category) AS category_count, MAX(view_time) AS last_view_time FROM view_log GROUP BY user_id df_user_features pd.read_sql_query(query, conn) print(df_user_features) conn.close()这样生成的df_user_features就是我们机器学习中常用的用户特征表。后续可以继续加入标签、其他特征一起用于模型训练。4.5 处理时间字段上面的特征中last_view_time是一个字符串机器无法直接使用。在建模之前还需要把字符串格式的时间转换为数值型特征比如距今天数、距离第一次浏览的天数等。这一步一般在特征工程中完成因为 SQL 处理时间格式在不同数据库中差异较大留在 Python 中处理更灵活。import pandas as pd df_user_features[last_view_time] pd.to_datetime(df_user_features[last_view_time]) # 计算距离今天的天数 df_user_features[days_since_last_view] ( pd.Timestamp(now) - df_user_features[last_view_time] ).dt.days print(df_user_features[[user_id, view_count, days_since_last_view]])5. JSON 与 SQL 的使用边界在机器学习项目中JSON 和 SQL 不是对立的它们各自有最适合的使用场景。理解它们的使用边界可以帮助我们在项目中做出更好的技术选型。对比项JSONSQL数据形态嵌套、灵活的层级结构扁平、规范化的二维表典型来源Web API、日志文件、NoSQL关系型数据库优点灵活、易扩展、结构清晰查询高效、事务安全、易聚合缺点不适合复杂查询、占用空间大结构固定、扩展需要迁移机器学习用途数据采集与交换特征提取与存储在数据处理流程中一个比较典型的模式是API (JSON) - 解析 - 清洗 - 入数据库 (SQL) - 查询特征 - 建模也就是把 JSON 作为数据入口的交换格式把 SQL 作为数据存储和特征提取的方式。两者配合使用可以完成大多数机器学习项目的数据准备工作。6. 常见问题与排查思路在实际操作中JSON 解析和 SQL 查询都有一些高频踩坑点我把常见的整理成一个表格供你排查参考。问题现象常见原因解决思路json.loads()报错JSONDecodeErrorJSON 字符串格式错误比如引号不匹配、有多余逗号用在线 JSON 校验工具检查确认接口返回是否为 UTF-8 编码中文变成\uXXXX转义json.dumps()没有设置ensure_asciiFalse在dumps()或dump()中加上ensure_asciiFalseDataFrame 中嵌套字段无法展开JSON 结构嵌套过深直接传入DataFrame无法对齐使用pd.json_normalize()配合record_path和metapd.read_sql_query()无法读取数据库数据库连接参数错误或表不存在检查表名是否拼写正确确认连接对象已成功建立SQL 查询结果有重复行多表连接时一对多关系导致结果放大确认连接条件使用DISTINCT去重或先聚合再连接to_sql()写入速度慢一条条插入未使用批量事务使用if_exists参数批量写入或先分块再写NULL值无法直接用于模型训练SQL 中LEFT JOIN没有匹配的记录产生 NULL在 Python 中使用fillna()填充或删除空值行时间字段是字符串类型数据库存储格式或 API 返回格式是字符串使用pd.to_datetime()统一转换还有一点经常被忽略读取 JSON 文件时要指定正确的编码格式推荐使用encodingutf-8。如果文件是从 Windows 环境生成的可能会有 BOM 头导致读取的第一个字段名出现\ufeff此时可以在读取时指定encodingutf-8-sig解决。7. 最佳实践与工程建议上面我们实现了完整的数据处理流程下面总结一些在真实项目中非常有用的最佳实践。7.1 始终使用参数化查询在 Python 中拼接 SQL 字符串时永远不要直接使用 f-string 拼接用户输入值。这不仅可能导致 SQL 注入漏洞还会因引号问题导致语法错误。推荐做法是使用?占位符# 错误示例不要这样写 user_id 1 sql fSELECT * FROM users WHERE id {user_id} # 正确示例使用占位符 sql SELECT * FROM users WHERE id ? cursor.execute(sql, (user_id,))7.2 JSON 字段名映射与清洗从 API 拿到的 JSON 字段名可能与你的数据规范不一致通常在入库前做一次字段映射和类型清洗。def clean_record(raw): return { user_id: int(raw[userId]), view_duration: float(raw[view_time_seconds]), category: str(raw[category]).lower(), view_date: raw[view_date][:10] } df_clean pd.DataFrame([clean_record(r) for r in raw_records])这样可以避免脏数据直接进入数据库减少后续清洗的麻烦。7.3 数据量较大时的性能考虑JSON 解析如果 JSON 文件特别大几百 MB 甚至几 GB一次性读入内存可能导致内存溢出。建议使用ijson等流式解析库逐条读取记录。SQL 查询在特征查询时尽量把过滤条件写在 SQL 中只返回需要的数据列而不是在 Python 中再过滤。SQL 的索引和查询引擎比 Python 的内存操作高效得多。批量写入写入数据库时使用事务和批量操作避免逐条提交。7.4 流程可复现在机器学习项目中数据处理的每一步都应该可复现。建议将数据清洗逻辑封装成独立的 Python 模块或函数。使用版本控制管理代码而不是在 notebook 中杂乱地改。对关键步骤输出中间结果文件便于回溯。7.5 生产环境注意事项在真实生产环境中需要考虑以下几点数据库连接要使用连接池避免频繁创建和断开连接。从 API 拉取数据要设置超时和重试机制避免接口异常导致流程中断。数据入库之前先做去重和校验防止脏数据污染特征。不同的环境需要配置不同的数据库地址不要把连接信息硬编码在代码中。8. 总结今天的内容围绕机器学习数据处理中的两个基础能力展开。在 JSON 部分我们学习了 JSON 的基本语法、Python 标准库json的使用方法以及如何通过pd.json_normalize()把嵌套 JSON 展开成表格。在 SQL 部分我们使用 SQLite 作为示例数据库演示了建表、插入数据、聚合查询、多表连接等核心操作并说明了如何通过 Pandas 的read_sql_query()把查询结果直接转成 DataFrame。在实战环节我们完成了一个标准的机器学习数据准备流程模拟 API 返回 JSON 数据解析并清洗存入 SQLite从数据库中提取用户特征表。这个流程在真实项目中每天都会被重复。数据处理是机器学习的基础JSON 和 SQL 则是数据处理的两个核心工具。把这两个工具掌握扎实后续学习特征工程、模型训练时会轻松很多。如果你在操作过程中遇到本文没有覆盖的问题可以在评论区留言我们一起讨论。 机器学习数据准备：JSON解析与SQL查询实战指南
在机器学习项目中数据集往往不是天生就放在一个干净的 CSV 文件里等着我们去读取。更多时候数据散落在 API 接口返回的 JSON 字符串中或者静静地躺在数据库的某张表里。如果我们不能熟练地把 JSON 转换成可分析的结构化数据或者写不出高效的 SQL 查询去提取特征那么后续的建模工作就会寸步难行。这是 100 天机器学习挑战的第 16 天本文我们聚焦数据处理环节中两个最常用的数据格式与技术JSON 与 SQL。我会用通俗的语言解释它们是什么并通过 Python 代码演示如何在机器学习项目中实际应用它们。无论你是刚入门机器学习的新手还是需要系统复习数据预处理的老手这篇文章都能给你一个完整的实操参考。我们不需要安装复杂的软件只需要一个 Python 环境加上 Pandas 和 SQLite3 这两个库就能完成大部分演示。1. 为什么机器学习需要处理 JSON 与 SQL很多同学在学习机器学习时会把大部分精力放在算法原理和模型调参上却忽略了数据获取和数据清洗的重要性。实际上一个真实机器学习项目的流程往往是这样的业务数据源 - 数据抽取 - 数据清洗 - 特征工程 - 建模 - 评估 - 上线数据源可以是日志文件、数据库、第三方 API也可以是爬虫抓取的网页。在这些数据源中JSON 和 SQL 占据了非常大的比重。为什么这么说JSON 是接口数据的标准格式。现在几乎所有 Web API 返回的数据都是 JSON 格式。比如你要获取天气数据、股票行情、社交媒体评论返回给你的基本都是 JSON。机器学习要使用这些外部数据第一步就是解析 JSON。SQL 是业务数据的主要存储方式。企业内部的数据绝大多数存放在关系型数据库中如 MySQL、PostgreSQL、SQL Server、SQLite。要提取这些数据进行建模就必须掌握 SQL 查询。数据预处理是机器学习中最耗时的一环。有研究表明数据科学家 80% 的时间花在数据准备上而 JSON 解析和 SQL 查询是数据准备中最基础的能力。所以掌握 JSON 和 SQL 并不是在偏离机器学习而是在打机器学习的基础。2. JSON 基础与 Python 解析2.1 JSON 是什么JSONJavaScript Object Notation是一种轻量级的数据交换格式。它源于 JavaScript但现在已经成为跨语言、跨平台通用的数据格式标准。JSON 的结构非常简洁主要有两种数据结构对象Object由花括号{}包裹内部是一组键值对。数组Array由方括号[]包裹内部是一组有序的值。一个完整的 JSON 数据看起来是这样的{ users: [ {id: 1, name: Alice, age: 28, skills: [Python, SQL]}, {id: 2, name: Bob, age: 32, skills: [Java, Spark]} ], total: 2 }JSON 支持的数据类型包括字符串、数字、布尔值、数组、对象和 null。2.2 JSON 与 Python 数据结构的对应关系JSON 之所以流行是因为它和 Python 的数据结构高度契合。我们来看一张对应表JSON 类型Python 类型说明{}对象dict字典键值对集合[]数组list列表有序序列字符串str文本数字int/float整数或浮点数true/falseTrue/False布尔值nullNone空值在 Python 中解析 JSON我们一般使用标准库json。核心函数有三个json.loads()把 JSON 字符串解析成 Python 对象。json.load()从文件读取 JSON 并解析。json.dumps()把 Python 对象转换成 JSON 字符串。下面我们写一个最小示例。import json # 模拟从 API 返回的 JSON 字符串 json_str { users: [ {id: 1, name: Alice, age: 28, skills: [Python, SQL]}, {id: 2, name: Bob, age: 32, skills: [Java, Spark]} ], total: 2 } # 解析 JSON 字符串 data json.loads(json_str) # 查看解析后的数据类型 print(type(data)) # 输出class dict # 访问嵌套数据 print(data[users][0][name]) # 输出Alice # 把 Python 对象转换回 JSON 字符串 json_output json.dumps(data, ensure_asciiFalse, indent2) print(json_output)这里重点说一下json.dumps()的两个常用参数ensure_asciiFalse允许输出中文字符而不是转义成\uXXXX的 ASCII 形式。indent2美化输出格式方便阅读和调试。2.3 从 JSON 文件读取数据并转成 DataFrame在实际项目中我们拿到 JSON 后通常要把它转成 Pandas 的 DataFrame才能进行后续的清洗和分析。import json import pandas as pd # 假设有一个 JSON 文件data.json with open(data.json, r, encodingutf-8) as f: data json.load(f) # 如果 JSON 的根节点是一个列表可以直接传入 DataFrame df pd.DataFrame(data[users]) print(df.head())输出如下id name age skills 0 1 Alice 28 [Python, SQL] 1 2 Bob 32 [Java, Spark]这里有一个常见的坑如果 JSON 中的某个字段本身是列表比如skills直接转成 DataFrame 后该字段会以列表形式存存储。如果希望把列表展开成多行可以使用explode()方法df_exploded df.explode(skills) print(df_exploded)这样一行包含多技能的记录就会被拆分成多行每条技能一行。这在特征工程中非常常用。2.4 嵌套 JSON 的规范化真实 API 返回的 JSON 往往比上面的示例复杂得多经常会出现多层嵌套。比如下面这种结构{ order_id: A001, customer: { name: Alice, contact: { phone: 123456, email: aliceexample.com } }, items: [ {product: 笔记本, price: 5999, quantity: 1}, {product: 鼠标, price: 99, quantity: 2} ] }对于这种嵌套 JSON直接用pd.DataFrame(json)会出现问题因为customer字段是一个字典items字段是一个列表无法直接对齐。Pandas 提供了json_normalize()方法在新版本中推荐使用pd.json_normalize()可以自动把嵌套的 JSON 展开成扁平化的表格。import pandas as pd data { order_id: A001, customer: { name: Alice, contact: { phone: 123456, email: aliceexample.com } }, items: [ {product: 笔记本, price: 5999, quantity: 1}, {product: 鼠标, price: 99, quantity: 2} ] } # 规范化嵌套 JSON并指定 items 列表下的记录如何展开 df pd.json_normalize( data, record_pathitems, meta[order_id, [customer, name], [customer, contact, email]] ) print(df)输出product price quantity order_id customer.name customer.contact.email 0 笔记本 5999 1 A001 Alice aliceexample.com 1 鼠标 99 2 A001 Alice aliceexample.comrecord_path指定要展开的列表字段meta指定要保留的上级字段。嵌套字段用列表形式逐级指定比如[customer, name]表示取customer下的name。这种方式在处理电商订单、用户行为日志、多级评论等场景时十分好用。3. SQL 基础与 Python 操作3.1 SQL 在机器学习流程中的角色SQLStructured Query Language是关系型数据库的标准查询语言。在机器学习项目中SQL 主要用于两个场景从数据库提取训练数据比如从订单表、用户表、商品表中提取特征生成训练集。在线推理时的特征查询模型上线后需要实时从数据库拉取最新特征进行预测。掌握 SQL 的基础语法能让我们从海量数据中高效地筛选出所需数据而不是把所有数据都加载到内存中再过滤。3.2 Python 操作 SQLite为了演示方便本文使用 SQLite 作为示例数据库。SQLite 是 Python 内置支持的轻量级数据库无需额外安装服务非常适合学习和本地开发。Python 操作 SQLite 的标准方式是使用内置模块sqlite3。我们先用一段代码创建一个示例数据库和一张用户表。import sqlite3 # 连接数据库如果文件不存在会自动创建 conn sqlite3.connect(ml_demo.db) cursor conn.cursor() # 创建用户表 cursor.execute( CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT, signup_date TEXT ) ) # 插入示例数据 users_data [ (1, Alice, 28, 北京, 2023-01-15), (2, Bob, 32, 上海, 2023-02-20), (3, Charlie, 25, 广州, 2023-03-10), (4, David, 40, 深圳, 2022-12-01), (5, Eve, 35, 北京, 2023-05-18) ] cursor.executemany(INSERT INTO users VALUES (?, ?, ?, ?, ?), users_data) conn.commit() # 关闭连接 conn.close()executemany()可以批量插入多条记录参数使用?占位符可以避免 SQL 注入风险。这一点在后面的最佳实践中还会再提到。3.3 在 Python 中执行查询接下来从表中查询数据并转成 DataFrame。import sqlite3 import pandas as pd conn sqlite3.connect(ml_demo.db) # 查询所有用户 df_users pd.read_sql_query(SELECT * FROM users, conn) print(df_users)pd.read_sql_query()是 Pandas 提供的方法可以直接执行 SQL 语句并将结果转换为 DataFrame非常方便。我们再创建一个订单表演示多表关联查询。import sqlite3 conn sqlite3.connect(ml_demo.db) cursor conn.cursor() cursor.execute( CREATE TABLE IF NOT EXISTS orders ( order_id INTEGER PRIMARY KEY, user_id INTEGER, product TEXT, amount REAL, order_date TEXT ) ) orders_data [ (101, 1, 笔记本, 5999, 2023-06-01), (102, 1, 鼠标, 99, 2023-06-02), (103, 2, 键盘, 299, 2023-06-03), (104, 3, 显示器, 1499, 2023-06-04), (105, 5, 笔记本, 5999, 2023-06-05), (106, 5, 耳机, 499, 2023-06-06) ] cursor.executemany(INSERT INTO orders VALUES (?, ?, ?, ?, ?), orders_data) conn.commit() conn.close()3.4 核心 SQL 操作在机器学习特征提取中最常用的 SQL 操作包括过滤与排序SELECT * FROM users WHERE city 北京 AND age 25 ORDER BY age DESC;聚合统计按城市统计用户数量、平均年龄SELECT city, COUNT(*) AS user_count, AVG(age) AS avg_age FROM users GROUP BY city;多表连接将用户表和订单表连接找出每个用户的订单总额SELECT u.id, u.name, u.city, SUM(o.amount) AS total_spent, COUNT(o.order_id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name, u.city;我们可以在 Python 中通过 Pandas 直接执行这条 SQLimport sqlite3 import pandas as pd conn sqlite3.connect(ml_demo.db) query SELECT u.id, u.name, u.city, SUM(o.amount) AS total_spent, COUNT(o.order_id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name, u.city df_features pd.read_sql_query(query, conn) print(df_features)输出id name city total_spent order_count 0 1 Alice 北京 6098.0 2 1 2 Bob 上海 299.0 1 2 3 Charlie 广州 1499.0 1 3 4 David 深圳 NaN 0 4 5 Eve 北京 6498.0 2这样我们就从原始的订单表中提取出了用户维度的特征。这正是机器学习特征工程中非常常见的操作把行为数据聚合成用户画像特征。注意LEFT JOIN保证左表的记录都会保留即使右表没有匹配记录。David没有订单所以total_spent是NULL在后续处理中需要填充或删除。4. 实战案例从 API JSON 到特征表入库上面介绍了 JSON 和 SQL 的基础操作接下来我们用一个完整的实战案例把两者串起来从模拟的 API 拉取用户行为数据JSON 格式解析后清洗再写入 SQLite 数据库最后通过 SQL 查询生成供机器学习使用的特征表。4.1 模拟 API 返回的 JSON 数据假设有一个返回用户浏览记录的 API每次返回 10 条记录记录中包含用户 ID、浏览商品 ID、商品类别、浏览时长和浏览时间。import json import random from datetime import datetime, timedelta # 模拟生成一条浏览记录 def generate_records(n, start_user1): records [] categories [electronics, clothing, books, home, sports] for i in range(n): user_id random.randint(start_user, start_user 20) product_id random.randint(1000, 9999) category random.choice(categories) duration round(random.uniform(5, 600), 2) # 随机生成过去 7 天内的一个时间 time datetime.now() - timedelta(daysrandom.randint(0, 7), hoursrandom.randint(0, 23), minutesrandom.randint(0, 59)) records.append({ user_id: user_id, product_id: product_id, category: category, duration: duration, view_time: time.strftime(%Y-%m-%d %H:%M:%S) }) return records # 模拟第一次调用 API 返回的数据 api_response_1 { code: 0, message: success, data: generate_records(10) } print(json.dumps(api_response_1, ensure_asciiFalse, indent2))这段代码模拟了一个标准 API 返回结构外层是状态码和消息内层是数据列表。这和我们实际工作中遇到的 API 结构非常相似。4.2 解析 JSON 并存入 SQLite现在我们把解析出来的数据写入 SQLite 数据库。import sqlite3 import pandas as pd # 解析 JSON records api_response_1[data] df_records pd.DataFrame(records) # 连接数据库 conn sqlite3.connect(behavior.db) # 写入 DataFrame 到 SQLite 表 df_records.to_sql(view_log, conn, if_existsreplace, indexFalse) # 验证 df_check pd.read_sql_query(SELECT * FROM view_log LIMIT 10, conn) print(df_check) conn.close()df.to_sql()是 Pandas 提供的便捷方法可以把 DataFrame 直接写入数据库表。参数说明如下name表名。con数据库连接。if_existsreplace如果表已存在则删除重建。indexFalse不把 DataFrame 的索引写入数据库。4.3 使用 UPDATE 处理增量数据现实场景中API 返回的数据往往是增量数据需要追加到已有表中而不是每次都重建。这时可以使用if_existsappend# 模拟第二次调用 API 返回的数据 api_response_2 { code: 0, message: success, data: generate_records(10, start_user30) } df_records_2 pd.DataFrame(api_response_2[data]) conn sqlite3.connect(behavior.db) df_records_2.to_sql(view_log, conn, if_existsappend, indexFalse) conn.close()不过要注意简单使用append可能会导致重复数据。在真实项目中通常会根据主键判断记录是否已存在或者先按user_id和view_time去重后再插入。更严谨的做法是使用 SQL 的INSERT OR REPLACE或MERGE语法但具体取决于数据库类型。4.4 通过 SQL 生成特征表数据分析阶段我们需要从浏览日志中提取用户维度的特征比如每个用户的总浏览时长每个用户的浏览次数每个用户浏览的商品类别数每个用户最近一次浏览时间我们可以用一条 SQL 完成这些聚合。import sqlite3 import pandas as pd conn sqlite3.connect(behavior.db) query SELECT user_id, COUNT(*) AS view_count, SUM(duration) AS total_duration, AVG(duration) AS avg_duration, COUNT(DISTINCT category) AS category_count, MAX(view_time) AS last_view_time FROM view_log GROUP BY user_id df_user_features pd.read_sql_query(query, conn) print(df_user_features) conn.close()这样生成的df_user_features就是我们机器学习中常用的用户特征表。后续可以继续加入标签、其他特征一起用于模型训练。4.5 处理时间字段上面的特征中last_view_time是一个字符串机器无法直接使用。在建模之前还需要把字符串格式的时间转换为数值型特征比如距今天数、距离第一次浏览的天数等。这一步一般在特征工程中完成因为 SQL 处理时间格式在不同数据库中差异较大留在 Python 中处理更灵活。import pandas as pd df_user_features[last_view_time] pd.to_datetime(df_user_features[last_view_time]) # 计算距离今天的天数 df_user_features[days_since_last_view] ( pd.Timestamp(now) - df_user_features[last_view_time] ).dt.days print(df_user_features[[user_id, view_count, days_since_last_view]])5. JSON 与 SQL 的使用边界在机器学习项目中JSON 和 SQL 不是对立的它们各自有最适合的使用场景。理解它们的使用边界可以帮助我们在项目中做出更好的技术选型。对比项JSONSQL数据形态嵌套、灵活的层级结构扁平、规范化的二维表典型来源Web API、日志文件、NoSQL关系型数据库优点灵活、易扩展、结构清晰查询高效、事务安全、易聚合缺点不适合复杂查询、占用空间大结构固定、扩展需要迁移机器学习用途数据采集与交换特征提取与存储在数据处理流程中一个比较典型的模式是API (JSON) - 解析 - 清洗 - 入数据库 (SQL) - 查询特征 - 建模也就是把 JSON 作为数据入口的交换格式把 SQL 作为数据存储和特征提取的方式。两者配合使用可以完成大多数机器学习项目的数据准备工作。6. 常见问题与排查思路在实际操作中JSON 解析和 SQL 查询都有一些高频踩坑点我把常见的整理成一个表格供你排查参考。问题现象常见原因解决思路json.loads()报错JSONDecodeErrorJSON 字符串格式错误比如引号不匹配、有多余逗号用在线 JSON 校验工具检查确认接口返回是否为 UTF-8 编码中文变成\uXXXX转义json.dumps()没有设置ensure_asciiFalse在dumps()或dump()中加上ensure_asciiFalseDataFrame 中嵌套字段无法展开JSON 结构嵌套过深直接传入DataFrame无法对齐使用pd.json_normalize()配合record_path和metapd.read_sql_query()无法读取数据库数据库连接参数错误或表不存在检查表名是否拼写正确确认连接对象已成功建立SQL 查询结果有重复行多表连接时一对多关系导致结果放大确认连接条件使用DISTINCT去重或先聚合再连接to_sql()写入速度慢一条条插入未使用批量事务使用if_exists参数批量写入或先分块再写NULL值无法直接用于模型训练SQL 中LEFT JOIN没有匹配的记录产生 NULL在 Python 中使用fillna()填充或删除空值行时间字段是字符串类型数据库存储格式或 API 返回格式是字符串使用pd.to_datetime()统一转换还有一点经常被忽略读取 JSON 文件时要指定正确的编码格式推荐使用encodingutf-8。如果文件是从 Windows 环境生成的可能会有 BOM 头导致读取的第一个字段名出现\ufeff此时可以在读取时指定encodingutf-8-sig解决。7. 最佳实践与工程建议上面我们实现了完整的数据处理流程下面总结一些在真实项目中非常有用的最佳实践。7.1 始终使用参数化查询在 Python 中拼接 SQL 字符串时永远不要直接使用 f-string 拼接用户输入值。这不仅可能导致 SQL 注入漏洞还会因引号问题导致语法错误。推荐做法是使用?占位符# 错误示例不要这样写 user_id 1 sql fSELECT * FROM users WHERE id {user_id} # 正确示例使用占位符 sql SELECT * FROM users WHERE id ? cursor.execute(sql, (user_id,))7.2 JSON 字段名映射与清洗从 API 拿到的 JSON 字段名可能与你的数据规范不一致通常在入库前做一次字段映射和类型清洗。def clean_record(raw): return { user_id: int(raw[userId]), view_duration: float(raw[view_time_seconds]), category: str(raw[category]).lower(), view_date: raw[view_date][:10] } df_clean pd.DataFrame([clean_record(r) for r in raw_records])这样可以避免脏数据直接进入数据库减少后续清洗的麻烦。7.3 数据量较大时的性能考虑JSON 解析如果 JSON 文件特别大几百 MB 甚至几 GB一次性读入内存可能导致内存溢出。建议使用ijson等流式解析库逐条读取记录。SQL 查询在特征查询时尽量把过滤条件写在 SQL 中只返回需要的数据列而不是在 Python 中再过滤。SQL 的索引和查询引擎比 Python 的内存操作高效得多。批量写入写入数据库时使用事务和批量操作避免逐条提交。7.4 流程可复现在机器学习项目中数据处理的每一步都应该可复现。建议将数据清洗逻辑封装成独立的 Python 模块或函数。使用版本控制管理代码而不是在 notebook 中杂乱地改。对关键步骤输出中间结果文件便于回溯。7.5 生产环境注意事项在真实生产环境中需要考虑以下几点数据库连接要使用连接池避免频繁创建和断开连接。从 API 拉取数据要设置超时和重试机制避免接口异常导致流程中断。数据入库之前先做去重和校验防止脏数据污染特征。不同的环境需要配置不同的数据库地址不要把连接信息硬编码在代码中。8. 总结今天的内容围绕机器学习数据处理中的两个基础能力展开。在 JSON 部分我们学习了 JSON 的基本语法、Python 标准库json的使用方法以及如何通过pd.json_normalize()把嵌套 JSON 展开成表格。在 SQL 部分我们使用 SQLite 作为示例数据库演示了建表、插入数据、聚合查询、多表连接等核心操作并说明了如何通过 Pandas 的read_sql_query()把查询结果直接转成 DataFrame。在实战环节我们完成了一个标准的机器学习数据准备流程模拟 API 返回 JSON 数据解析并清洗存入 SQLite从数据库中提取用户特征表。这个流程在真实项目中每天都会被重复。数据处理是机器学习的基础JSON 和 SQL 则是数据处理的两个核心工具。把这两个工具掌握扎实后续学习特征工程、模型训练时会轻松很多。如果你在操作过程中遇到本文没有覆盖的问题可以在评论区留言我们一起讨论。