常见的 SQL 和 Python 问题及其解决方法

主页 » Python » 常见的 SQL 和 Python 问题及其解决方法

在数据和后端开发中，SQL 和 Python 的结合使用是最强大的组合之一。但这同时也为一系列不易察觉的错误、配置陷阱和性能问题打开了方便之门。如果您曾经盯着晦涩难懂的错误回溯信息发呆，而您的数据库连接“理应”正常工作，或者疑惑为什么同样的分析脚本在您的笔记本电脑上运行速度飞快，但在 SQL Server 中却慢得像蜗牛爬行，那么您并不孤单。

本指南汇集了实际的 SQL-Python 问题、底层 SQL Server 机器学习服务问题以及在分析中使用这两种语言的实用模式。本书没有含糊不清的建议，而是提供了具体的例子、典型的错误信息以及诊断和解决问题的分步思路，并全面介绍了如何使用 SQLite 和其他引擎在 Python 中设计、查询和操作数据库。

SQL 和 Python 之间常见的连接问题

将 SQL 和 Python 混合使用时，首先遇到的痛点之一就是如何建立稳定的连接。即使凭据和 DSN 看起来正确，驱动程序、路径或环境中的微小不匹配也可能在启动 app.py 或从命令行运行脚本时触发令人困惑的运行时错误。

在虚拟化环境中，这种脆弱性会更加明显。例如，您可能在主机操作系统上进行开发时，在虚拟机中运行 SQLite 或 SQL Server，并使用 SQL Developer 或 SQL Server Management Studio 等 GUI 工具测试连接。GUI 连接正常，但 Python 脚本连接失败，因为它使用了不同的驱动程序、缺少库或完全不同的网络路径。

常见的连接问题包括缺少 ODBC/DB API 驱动程序、DSN 配置错误、端口被阻止以及身份验证模式不匹配。经常可以看到 Python 抛出诸如“无法连接”之类的通用异常，而根本问题是系统无法加载共享库（例如 Linux 上的 libc++ 或 libc++abi），或者找不到 SQLite、PostgreSQL、MySQL 或 SQL Server 的预期 ODBC 驱动程序。

当您从 Python 连接时，通常会使用诸如 sqlite3、psycopg2、pyodbc、mysql-connector-python、PyMySQL 之类的库，或者像 SQLAlchemy 这样的 ORM 层。它们各自都有自己的连接字符串格式、错误类型和依赖项。图形用户界面客户端可能使用不同的驱动程序堆栈来隐藏这些问题，因此务必确认您的 Python 代码正在使用的具体驱动程序和连接参数。

为什么将 SQL 和 Python 结合起来具有强大的战略意义

除了技术上的难题之外，开发人员和分析师坚持将 Python 与 SQL 结合使用还有战略上的原因。每种语言涵盖数据生命周期的不同部分，它们共同为您提供了一个难以用单一工具实现的端到端工作流程。

SQL 仍然是关系数据管理的标准。它在处理结构化数据、关系完整性、索引和事务性工作负载方面表现出色。借助 SQL，您可以对大型数据集进行快速筛选、连接和聚合，为多种工具提供统一的访问方式，并获得数十年数据库研究支持的可预测性能。

一旦数据离开数据库上下文，Python 的优势就显现出来了。借助 pandas、NumPy、matplotlib 和 seaborn 等库，您可以以任意复杂的方式清理、重塑和分析数据，运行统计或机器学习，并以编程方式构建可视化或报告，包括 实时数据分析许多在 SQL 中显得笨拙或冗长的转换，在 Python 中都能简化为简单的表达式。

在实践中，这意味着明确的劳动分工。尽可能将过滤、聚合和基本转换操作下放到 SQL 中，然后将整理好的数据集导入 Python 进行更深入的分析、建模或可视化。精通这两种语言的分析师和工程师可以快速地从业务问题过渡到可复现的数据管道。

将 Python 连接到 SQL 数据库：库和模式

为了使 SQL 和 Python 能够可靠地协同工作，您需要合适的连接器，并且需要遵循一定的规范来打开、使用和关闭数据库会话。具体技术栈取决于数据库引擎，但概念类似。

对于轻量级、嵌入式工作流程，SQLite 通常是最简单的选择。Python 标准库自带 sqlite3 模块，因此无需安装额外软件即可创建数据库文件、定义表并运行查询。这非常适合原型设计、小型分析项目或关系型数据库概念的教学。

对于服务器级数据库，通常使用引擎特定的驱动程序或 ORM（对象关系管理）。PostgreSQL 广泛使用 psycopg2，SQL Server 通常通过 pyodbc 或 Microsoft 的 ODBC 驱动程序连接，而 MySQL/MariaDB 则依赖于 mysql-connector-python 或 PyMySQL。除此之外，SQLAlchemy 还提供了一个高级抽象层，使用户能够编写可移植的 SQL 表达式并管理连接池。

稳健的连接模式包括从环境变量或密钥管理器读取凭据、使用参数化查询以避免注入攻击以及应用适当的错误处理。在每个工作单元完成后，您应该显式地提交或回滚事务，并将连接释放回连接池或将其关闭，而不是保持许多空闲会话处于打开状态。

借助 SQLAlchemy 和 pandas，工作流程变得尤为顺畅。你需要构建一个连接 URL，创建一个引擎，然后使用 pandas.read_sql_query 将查询结果直接导入到 DataFrame 中。之后，你就可以利用 Python 生态系统的强大功能来清洗、分析和导出数据。

SQL Server 中的机器学习服务：R 和 Python 集成问题

Microsoft SQL Server 包含一项名为机器学习服务的功能，该功能将 R 和 Python 运行时嵌入到数据库引擎中。它允许您通过 sp_execute_external_script 调用外部脚本。这对于数据库内分析非常强大，但它也存在许多版本特定的错误和限制，您必须了解这些限制。

SQL Server 2016、2017、2019 和 2022 版本中，安装和升级问题尤其常见。问题包括特定 Azure VM 映像上缺少 R 组件、早期 SQL Server 2017 版本上的 Python 安装程序不完整，以及累积更新 (CU) 包无法提示离线 R 更新等。在某些情况下，您必须在命令行中传递额外的参数，例如 MRCACHEDIRECTORY，以将安装程序指向缓存的 CAB 文件。

此外，还存在平台特定的依赖问题。在 SQL Server 2019 及更高版本的 Linux 系统中，R 和 Python 运行时可能无法启动，因为扩展库路径中缺少 libc++.so.1 或 libc++abi.so.1 等共享库。SQL Server 中通常会显示“无法与运行时通信”的通用错误信息，而启动日志则会显示缺少 .so 文件。解决方法通常包括将所需的共享库复制到 /opt/mssql-extensibility/lib 目录，或者通过 mssql.conf 文件公开这些目录。

在配置了 FIPS 加密设置的 Windows 服务器上，还会出现另一类安装失败。尝试启用机器学习服务或语言扩展时，可能会出现应用程序容器创建与 Windows 平台 FIPS 验证算法不兼容的错误。解决方法是暂时禁用 FIPS，完成安装或升级，然后在 SQL Server 完全配置完成后重新启用 FIPS。

某些累积更新会引入暂时性回归问题，从而影响脚本执行。例如，SQL Server 2017 的 CU 5-7 版本中，当临时目录路径包含空格时，rlauncher.config 文件存在一个错误，会导致 R 脚本运行失败并显示“无法创建 R_TempDir”的错误。后续的 CU 版本修复了此问题，但在此之前，管理员必须使用 RegisterRExt.exe 程序，并分别添加卸载和安装标志，重新注册外部脚本环境。

客户端和服务端运行时版本不匹配

另一个经常引起困惑的问题是客户端工具（Microsoft R 客户端或 Python 包）与服务器端运行时（R 服务器或 SQL Server 机器学习服务）之间的版本兼容性。当您从客户端针对较旧的 SQL Server 实例运行远程脚本时，不匹配可能会触发明显的错误或不易察觉的序列化问题。

在 SQL Server 2016 R 服务中，客户端和服务器端的 R 库版本必须完全匹配。在运行 R Server 8.0.3 的服务器上使用 Microsoft R Client 9.x 时，会显示客户端不兼容的提示信息，并建议您安装匹配的版本。后续版本放宽了这一要求，但如果出现此类错误，您必须检查服务器和客户端，并升级服务器或安装兼容的客户端。

训练模型的序列化和反序列化对版本差异尤其敏感。使用 R 语言中的 RevoScaleR 和 Python 中的 revoscalepy 时，如果模型使用较新的 API 进行序列化，则在采用较旧序列化基础架构的服务器上可能无法反序列化，从而导致内部错误，例如 R 语言中的 memDecompress 失败或 Python 中的 NameError（当未定义 rx_unserialize_model 时）。将 SQL Server 实例升级到 SQL Server 2017 的 CU3 或更高版本通常可以解决这些不匹配问题。

安装在 SQL Server 2017 上的预训练模型也可能遇到路径长度限制。早期版本将模型二进制文件存储在默认实例路径下的深层目录结构中，由于完整路径超出操作系统限制，Python 无法打开这些文件。建议的解决方法包括：将模型安装到自定义的较短路径，将 SQL Server 安装到较短的根目录，甚至使用 fsutil 创建 NTFS 硬链接，从而为同一文件提供更短的别名。

在使用 SQL Server 机器学习服务构建解决方案时，务必在部署计划中锁定版本和累积更新 (CU) 级别。将脚本分散到多个具有不同 CU 级别的服务器上，而不跟踪这些细节，是日后难以调试的序列化和运行时问题的根源。

资源治理、性能和冷启动行为

即使 SQL Server 机器学习服务已正确安装且版本匹配，由于资源管理和进程池化，您仍可能遇到性能瓶颈。了解发射台和卫星的运行机制是实现稳定延迟的关键。

SQL Server 为外部脚本创建每个用户、每个数据库、每种语言的进程池。在一段时间不活动后，首次调用 `sp_execute_external_script` 会导致 Launchpad 为 R 或 Python 启动新的附属进程。这种冷启动在高负载服务器或资源受限的虚拟机上可能会明显变慢。后续调用会重用已预热的进程池，因此第二次和第三次执行速度会快得多。

如果首次调用延迟是一个问题（例如在实时计分场景中），您可以通过定期运行轻量级脚本来保持池子处于预热状态。许多团队通过 SQL Agent 安排一个简单的“空操作”R 或 Python 脚本每隔几分钟运行一次，以防止空闲清理任务关闭附属进程。

在 SQL Server 2016 企业版中，早期版本将外部脚本内存限制在总内存的 20% 左右。对于一台 32 GB 的服务器来说，这意味着每次请求的 R 可执行文件大小可能限制在 6.4 GB 左右。对于更大的模型或更宽的数据集，这很快就会成为一个瓶颈，导致内存分配错误或频繁的页面交换。管理员必须检查当前的默认设置，并在预期处理复杂的机器学习工作负载时调整资源调控器设置。

平行性是另一个微妙的限制。当您从 SQL Server 外部（例如，通过 RGui）调用 Microsoft ML 或 RevoScaleR 库时，即使底层版本是企业版，这些库通常也以单线程模式运行。同样，SQL Server 2019 中也存在已知错误，使用 RxLocalPar 上下文或基础并行包的 R 脚本可能会因为在沙盒运行时写入空设备时出现问题而导致 SQL Server 挂起。

调用外部脚本时的数据类型、编码和模式约束

当使用 sp_execute_external_script 将 SQL 数据导入 R 或 Python 时，数据类型和编码常常是导致意外行为的根源。并非所有 SQL 类型都受支持，有些类型仅部分受支持或会被静默转换，这可能会导致精度损失或字符串损坏，尤其是在处理复杂结构时，例如： SQL 中的数组.

早期的 SQL Server 2017 CU 对 Python 输出模式的数值、十进制和货币类型有严格的限制。当与 WITH RESULT SETS 和 Python 结合使用时，不支持的类型会导致 SqlSatelliteCall 错误，并显示仅允许使用 bit、smallint、int、datetime、smallmoney、real 和 float（以及部分 char/varchar）类型。后续的累积更新修复了此问题，但您仍然需要注意向外部运行时公开哪些数据类型。

对于 R 脚本，money、numeric、decimal 和 bigint 类型都会转换为 R 的 numeric 类型。因此，数值较大或小数位数较多的值可能会失去精度；货币类型可能会触发关于分值无法准确表示的警告，并且 bigint 超过了 R 中的 53 位整数限制，导致最低有效位出现舍入误差。

字符串编码也很重要。将存储在 varchar 列中的 Unicode 数据传递给 R 或 Python 可能会导致非 ASCII 字符损坏，因为 SQL Server 的排序规则可能与 R 或 Python 所需的 UTF-8 编码不匹配。建议使用 SQL Server 2019 及更高版本中提供的 UTF-8 排序规则，或者将 Unicode 文本存储在 nvarchar 中，并在脚本中显式处理转换。

某些 SQL 功能完全禁止外部脚本使用。在某些情况下，引用 Always Encrypted 列或掩码列的查询不能直接传递给 R 脚本；您可能需要将受保护的数据复制到临时表中，且不进行加密或掩码处理，以便进行分析。此外，在 SQL Server 计算环境中，R 中的 colClasses 等参数无法覆盖列类型；您必须在 T-SQL 中使用 CAST 或 CONVERT 函数将数据传递给 R 之前进行转换。

二进制有效载荷也有特殊的规则。返回 R 的原始类型数据时，该值必须包含在输出数据框中，而不是绑定到输出参数。实际上只支持一个原始输出集；如果需要多个二进制输出，则可能需要多次调用存储过程，或者从脚本内部通过 ODBC 将数据推送回 SQL。

在 SQL Server 中安装和扩展 Python 时遇到的实际问题

安装和扩展 SQL Server 机器学习服务捆绑的 Python 环境比安装独立的 Anaconda 或系统 Python 环境要受限得多。许多用户在使用 pip 或 sqlmlutils 添加软件包时遇到错误，尤其是在 Windows 系统上使用 SQL Server 2019 时。

在 Windows 系统上，安装 SQL Server 2019 后经常会出现一个问题，那就是 pip 会报告 TLS/SSL 配置问题。它提示 ssl 模块不可用，即使您显然可以运行 Python。原因通常是 PYTHON_SERVICES 目录下的 DLLs 子目录中缺少 OpenSSL DLL 文件（libssl-1_1-x64.dll 和 libcrypto-1_1-x64.dll）。将这些文件从 Library\bin 文件夹复制到 DLLs 文件夹，然后重新启动命令提示符，通常可以恢复 pip 发起 HTTPS 请求的能力。

一些流行的机器学习软件包（例如 TensorFlow）存在不兼容的依赖关系要求。TensorFlow wheel 文件可能需要比 SQL Server Python 环境中预装的 NumPy 版本更新的版本。由于 NumPy 被视为系统软件包，因此无法通过 sqlmlutils 进行升级，所以尝试通过这种方式安装 TensorFlow 会失败。您必须使用 `-m pip` 直接调用 `PYTHON_SERVICES` 可执行文件，并在该环境中升级或安装软件包，有时还需要手动更新 Microsoft Visual C++ 等可再发行运行时库。

在 Linux 系统上，自带的 pip 入口点可以直接被破解。对于 SQL Server 2019，从 `/opt/mssql/mlservices/runtime/python/bin` 运行 `pip` 可能会导致崩溃，并出现指向不存在的旧版 ML Server 位置的解释器错误。解决方法是从 PyPA 下载 `get-pip.py`，并使用 `/opt/mssql/mlservices/bin/python/python` 下的正确 Python 二进制文件运行它，从而有效地为该运行时重新引导 `pip`。

Python脚本中varbinary和varchar输出参数也存在一些微妙的行为差异。如果您的 sp_execute_external_script 调用公开了一个类型为 varbinary(max) 或 large varchar 的 OUTPUT 参数，而您未在 Python 脚本中为其赋值，则 BxlServer 组件可能会引发错误并停止工作。安全的做法是在 Python 代码中显式初始化这些参数，即使您只是将其设置为空字符串或 0x0。

使用 SQLite 的经典 SQL + Python 工作流程

抛开 SQL Server 的具体细节，学习和构建 SQL-Python 集成原型的一种非常有效的方法是使用 SQLite 和 Python 的 sqlite3 模块。SQLite 将数据存储在单个文件中，不需要单独的服务器进程，其行为类似于支持 SQL 的小型关系数据库。

在 SQLite 中，数据库本质上就是一个组织有序的文件，它将结构化数据持久化到磁盘上。它类似于 Python 字典，将键映射到值，但它增加了索引、高效存储大型数据集和查询功能。结构体围绕表（类似于电子表格）、行（记录）和列（字段）展开。更正式的关系术语中，它们分别是关系、元组和属性。

首先，使用 sqlite3.connect 连接到数据库文件。如果文件不存在，SQLite 会创建它。通过连接，您可以创建一个游标对象，它就像一个句柄，用于执行 SQL 命令并遍历结果。其工作流程类似于打开文件并逐行读取，区别在于您执行的是 SQL 语句而不是读取纯文本。

创建表需要指定列名和数据类型尽管 SQLite 在类型方面相当灵活，但定义类型有助于引擎选择高效的存储格式和索引策略。例如，一个简单的歌曲表可以定义一个文本标题和一个整数播放次数。使用 CREATE TABLE 创建表后，可以使用 INSERT 语句插入行，并使用参数占位符（问号）安全地绑定 Python 值。

在 Python 中使用 SQL：INSERT、SELECT、UPDATE、DELETE

SQL 提供了四个核心操作——INSERT、SELECT、UPDATE 和 DELETE——它们与使用 sqlite3 的 Python 代码完美对应。每个操作都会对表中的行进行操作，而 WHERE 子句允许您指定特定记录。

INSERT 语句会将新记录添加到表中。在 Python 中，你可以调用 `cursor.execute` 并执行类似 `INSERT INTO Songs (title, plays) VALUES (?, ?)` 的语句，同时传递一个参数元组。使用占位符而不是字符串拼接可以避免 SQL 注入，并正确处理引用。插入操作完成后，你需要调用 `conn.commit` 将事务中的更改提交到数据库文件中。

SELECT 语句从数据库读取数据，并可选择对结果进行筛选和排序。一个简单的 SELECT title, plays FROM Songs 语句会将游标转换为可迭代对象，用于遍历行。对于大型结果集，SQLite 不会一次性将所有行加载到内存中；而是在 for 循环迭代的过程中逐行生成。您可以使用 * 选择所有列，也可以指定一个子集，还可以使用 WHERE、ORDER BY 和 LIMIT 来约束和排序记录。

DELETE 语句会根据条件永久删除行。类似 `DELETE FROM Songs WHERE plays < 100` 这样的语句会删除所有播放次数低的歌曲。由于没有撤销操作，教程中通常会在脚本末尾删除行，以确保重新运行示例时结果一致。如果希望更改永久保存，则必须在删除操作后提交更改。

UPDATE 操作会修改现有行中的列您需要指定表、包含新值的 SET 子句以及可选的 WHERE 子句。例如，`UPDATE Songs SET plays = 16 WHERE title = 'My Way'` 会影响标题与该字符串匹配的所有行。如果省略 WHERE 子句，则会更新表中的每一行，这通常会导致意外的批量更改。

使用 SQLite 和 Python 构建 Twitter 爬虫

SQL 和 Python 混合使用的一个实际例子是一个简单的 Twitter 爬虫，它将状态存储在 SQLite 数据库中。尽管 Twitter 的 API 和政策会随着时间推移而改变，但其架构理念仍然具有指导意义：你需要遍历好友关系，避免重复访问帐户，并获取受欢迎程度指标，同时还要能够随时停止和恢复而不会丢失进度。

爬虫程序维护着一个 Twitter 账号表，并跟踪每个账号是否已被抓取以及作为好友出现多少次。每一行包含账号名称、一个指示是否已获取其好友列表的标志，以及该账号在其他用户的“好友”列表中出现的次数。这使您可以估算账号在样本网络中的受欢迎程度。

主循环会提示用户输入 Twitter 用户名或执行退出命令。如果用户直接按下回车键，脚本会查询数据库，找到下一个恢复状态为 0 的账户，并将其作为下一个目标。然后，脚本调用 Twitter 的好友列表接口，解析 JSON 响应，更新当前账户的恢复状态标志，并根据需要将每个好友信息插入或更新到数据库中，同时递增他们的好友计数器。

由于所有数据都存储在 SQLite 数据库中，您可以终止爬虫程序，稍后再重新启动它。数据库充当持久队列和状态存储。一个独立的辅助脚本可以导出 Twitter 表的内容，方便您查看哪些帐户已被识别、哪些帐户已被访问，以及每个帐户作为好友出现的次数。这种将爬取状态持久化到关系数据库的模式，可以很好地推广到其他网页或 API 爬取任务。

数据建模基础：主键、外键和规范化

将所有 Twitter 信息存储在单个表中很快就会遇到可扩展性和冗余问题。更稳健的方法是通过将实体（人）与关系（谁关注谁）分开，并通过键将它们链接起来，从而规范化数据。

人员表通常使用整数主键作为内部标识符在 SQLite 中，你可以声明 `id INTEGER PRIMARY KEY`，引擎会自动为插入的每一行生成一个唯一的整数。你还可以添加一个逻辑键，例如 Twitter 用户名，并将其标记为 `UNIQUE` 以防止重复。逻辑键是外部使用的，而主键则是你的代码和外键所引用的。

然后，一个单独的后续表使用外键来捕获关系。每行包含一对用户 ID，通常命名为 from_id 和 to_id（或类似名称），表示一个人关注了另一个人。您可以对这两列的组合声明 UNIQUE 约束，以确保不会意外地插入两次相同的关系。

规范化——即只存储每条信息一次，并通过键在其他地方引用它——可以避免重复数据，节省空间并提高性能。与其在数百万条关系记录中保存相同的用户名字符串，不如将其保存在人员表中一次，然后通过整数 ID 指向它。整数的比较和索引速度更快，这在大规模数据处理中至关重要。

在 Python 代码中，这种设计导致了插入或检索用户和关系的常见模式。在插入关系之前，必须确保双方都存在于人员表中：使用逻辑键进行 SELECT 查询，如果未找到任何行，则执行 INSERT 操作并将 lastrowid 作为新人员的 ID。只有确认无误后，才能使用 INSERT OR IGNORE 操作将新行插入到关联这些 ID 的后续表中。约束和 OR IGNORE 操作协同工作，无需过多手动检查即可保持数据一致性。

在 SQL 中使用 JOIN 合并相关表

一旦数据分散在多个规范化的表中，您就可以依靠 SQL JOIN 来重建所需的组合视图。JOIN 操作会根据匹配的键值合并两个表中的行，从而有效地为每个匹配项创建一个虚拟的宽行。

以 Twitter 为例，合并关注者和用户表可以让你看到特定用户关注了谁，或者谁关注了他们。类似 SELECT * FROM Follow JOIN People ON Follow.to_id = People.id WHERE Follow.from_id = 2 的查询会检索内部 ID 为 2 的用户关注的所有人员。JOIN 子句告诉数据库将每一行的 Follow.to_id 与 People.id 进行匹配，而 WHERE 条件限制了源用户。

结果集包含来自两个表的列您可能会看到来自关注表的两个整数 ID，以及来自人员表的完整人员记录（ID、句柄、已恢复标记）。当用户关注多个帐户时，每个关系都会生成一个合并的记录行，其中会重复源人员记录中的某些列，但方便您访问目标人员的属性。

连接有多种类型——内连接、左连接、右连接、全连接——但规范化的设计通常使用内连接来建立核心关系。INNER JOIN 只保留两侧都有匹配项的行，这符合关系行应始终引用已存在人员的理念。调试或探索时，您可以从每个表和 JOIN 查询中 SELECT 几行，以验证模型是否按预期运行。

这种关系模式无处不在：用户和角色、客户和订单、产品和类别、帖子和评论。一旦你熟练掌握了如何设计带有主键和外键的表以及如何编写 JOIN 查询，你就可以对复杂的领域进行建模和查询，同时还能利用 Python 进行更高层次的逻辑和分析。

综上所述，精通 SQL 和 Python 不仅意味着理解如何编写简洁的查询或脚本，还意味着理解运行时、驱动程序、数据类型和资源限制如何在不同平台上相互作用。从诊断 SQL Server 中晦涩难懂的机器学习服务错误，到在沙盒 Python 环境中管理库依赖项，再到设计规范化的 SQLite 模式和编排端到端的分析管道，您在数据库和代码之间切换得越流畅，您的数据解决方案就会变得越强大、越可扩展。