Conda env和www.example.com release env中python sqlite3的json查询python.org

m1m5dgzv  于 2023-05-07  发布在  SQLite
关注(0)|答案(1)|浏览(154)
import os
import sqlite3

g_rdb_path = 'mails.db'

def test():
    c = sqlite3.connect(g_rdb_path)
    t = c.cursor()
    t.execute('''SELECT m_mail_info.id
    FROM m_mail_info, json_each(m_mail_info.froms)
    WHERE json_each.value LIKE '%{0}%'
    UNION
    SELECT m_mail_info.id
    FROM m_mail_info, json_tree(m_mail_info.tos)
    WHERE json_tree.value LIKE '%{0}%' and json_tree.type ="text"
    UNION
    SELECT m_mail_info.id
    FROM m_mail_info, json_tree(m_mail_info.ccs)
    WHERE json_tree.value LIKE '%{0}%' and json_tree.type ="text"
    UNION
    SELECT m_mail_info.id
    FROM m_mail_info, json_tree(m_mail_info.bccs)
    WHERE json_tree.value LIKE '%{0}%' and json_tree.type ="text"
    union
    SELECT m_mail_info.id
    FROM m_mail_info
    WHERE m_mail_info.subject like '%{0}%' or m_mail_info.plainContent like '%{0}%'
    '''.format('name'))
    print([i for i in t.fetchall()])
    t.close()
    c.close()

if __name__ == '__main__':
    test()

在m_mail_info表中,列froms、tos、ccs、bccs为TEXT类型,格式类似“{“a”:“123”}”或“[{“a”:“123”}]”。
当我用conda env运行脚本时:
python=3.8.10 32 bit,windows它打印:

[(21,), (22,), (23,), (29,), (36,), (38,), (39,), (41,), (43,), (44,), (53,), (55,), (56,), (57,), (58,), (59,), (60,), (61,), (62,), (63,), (66,), (67,), (, (9072,), (73,), (78,), (81,), (115,), (120,), (129,), (158,), (162,), (163,), (164,), (167,), (168,), (171,), (173,), (186,), (190,), (768,), (779,), (818,), ( (108901,), (906,), (948,), (1034,), (1035,), (1036,), (1037,), (1041,), (1043,), (1050,), (1052,), (1053,), (1054,), (1055,), (1056,), (1060,), (1062,), (1071,), (11, (1076,), (1080,), (1082,), (1084,), (1086,), (1087,), (1089,), (1090,), (1093,), (1095,), (1096,), (1097,), (1098,), (1100,), (1102,), (1108,), (1109,), (1114,), (1115,), (1117,), (1119,), (1121,), (1122,), (1126,), (1129,), (1131,), (1133,), (1139,), (1147,), (1149,), (1152,), (1153,), (1154,), (1158,), (1160,), (1169,)]

不管它是什么,它都可以打印结果。
而如果我用www.example.com 3.8.10 32位版本安装的python env运行脚本python.org,它会打印Error:

Traceback (most recent call last):
  File "test.py", line 68, in <module>
    test()
  File "test.py", line 43, in test
    t.execute('''SELECT m_mail_info.id
sqlite3.OperationalError: no such table: json_tree

我在SQLiteStudio上尝试使用rawsql,它返回的结果与Condaenv中相同

我不知道如何找到conda env和python官方发布的env之间的区别。
如果你想重现这个问题,你可以创建一个只有一列的表,在查询语句中你必须使用json_eachjson_tree

5ktev3wc

5ktev3wc1#

json_eachjson_tree函数仅默认包含在SQLite版本3.38(2022-02-22)中。
Python 3.8要老得多,因此使用的是不包含它们的旧版本。
您可以通过以下方式进行确认:

import sqlite3

print(sqlite3.sqlite_version)

另一个SO post建议可以直接在Python树中替换sqlite3.dll模块。

相关问题