Python sqlite3中两段看似相同的SQL查询返回不同结果排查

我首次接触SQLite3/SQL，写的函数在生产DataFrame数据上可以正常运行，但单元测试时却失败了，环境是Python 3.9 + sqlite3。

核心代码 #

import sqlite3

class TestObject:
  def __init__(self, db_loc):
    self.con = sqlite3.connect(db_loc)
    self.cur = self.con.cursor()

  def get_thingy(self, row):
    # 该方法通过DataFrame的.apply调用，row为pd.Series
    params = (row.col1, row.col2, str(row.col3), row.col4, row.col5)
    print(params)
    res = self.cur.execute("""
    SELECT thingy FROM thingies
    WHERE (col1 = ? or col2 = ?) AND col3 = ? AND col4 = ? AND col5 = ?""", params).fetchone()
    print(res)
    print(self.cur.execute("""SELECT thingy FROM thingies 
      WHERE (col1 = 1 or col2 = 2) AND col3 = '3' AND col4 = 4 AND col5 = 5""").fetchone())
    # 后续处理逻辑

  def get_thingies(self):
    self.thingy_table['thingy'] = self.thingy_table.apply(lambda row: self.get_thingy(row), axis=1)

测试代码 #

import pytest
import os
import pandas as pd
from [文件路径] import TestObject

class TestClass:
  @pytest.fixture
  def testobj(self):
    db_path = 'test.db'
    test = TestObject(db_path)
    yield test
    if test.con: test.con.close()
    os.remove(db_path)
 
  def test_get_thingy(self, testobj):
    testobj.cur.execute("""CREATE TABLE thingies(col1, col2, col3, col4, col5, thingy)""")
    testobj.cur.execute("""INSERT INTO thingies VALUES (1, 2, '3', 4, 5, "yay thingy")""")
    testobj.con.commit()
    testobj.get_thingy(pd.Series({'col1': 1, 'col2': 2, 'col3': 3, 'col4': 4, 'col5': 5}))

运行测试输出 #

(1, 2, '3', 4, 5)
None
('yay thingy',)

疑问 #

• 为什么打印出的params看起来和硬编码参数完全一致，但参数化查询返回None，硬编码查询却能拿到正确结果？
• 从pandas Series取值有没有特殊问题？
• 生产环境中DataFrame的apply能正常运行，难道DataFrame的行不是Series？

问题根源与解决方案 #

问题根源 #

pandas Series中的数值类型默认是numpy数值类型（比如numpy.int64），而SQLite3的参数化查询对numpy类型的处理和Python原生类型不一致。虽然打印出来的params内容和手动输入的一样，但实际元素类型不同：手动输入的是Python原生int/str，从Series取出的是numpy.int64等类型，SQLite3在比较时无法正确匹配这些类型，导致WHERE条件不成立，返回None。

解决方案 #

将从Series中取出的数值显式转换为Python原生类型即可：

方案1：使用基础类型转换

params = (
    int(row.col1),
    int(row.col2),
    str(row.col3),
    int(row.col4),
    int(row.col5)
)

方案2：使用item()方法获取原生类型

适用于单个标量值，能直接取出Python原生类型：

params = (
    row.col1.item(),
    row.col2.item(),
    str(row.col3.item()),
    row.col4.item(),
    row.col5.item()
)

生产环境正常的原因 #

生产环境的DataFrame数据可能本身就是Python原生类型（比如读取CSV时指定了dtype，或者数据来源是Python原生类型），因此不会出现类型不匹配的问题。

内容的提问来源于stack exchange，提问作者Réka