json查询用于Python的sqlite3在Conda环境和python.org发布环境中。

huangapple go评论66阅读模式
英文:

json query for python sqlite3 in Conda env and python.org release env

问题

在m_mail_info表中列fromstosccsbccs是TEXT类型格式如'{"a":"123"}'或'[{"a":"123"}]'。

当我使用conda环境运行脚本时
> python=3.8.10  32Windows
它会打印

[(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,)]

无论如何,它都可以打印结果。

而如果我使用由python.org安装的python官方发行版3.8.10 32位运行脚本,
它会打印错误:

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上使用原始SQL,它返回与Conda环境中相同的结果

[![进入图片描述][1]][1]


  [1]: https://i.stack.imgur.com/j8EIi.png


我不知道如何找到conda环境和python官方发行版环境之间的区别。

如果你想复现这个问题,你可以创建一个只有一列的表,在查询语句中你必须使用```json_each```或```json_tree```。
英文:
import os
import sqlite3

g_rdb_path = &#39;mails.db&#39;

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

if __name__ == &#39;__main__&#39;:
    test()

in the m_mail_info table, column froms, tos, ccs, bccs are of TEXT type, in the format like '{"a":"123"}' or '[{"a":"123"}]'.

when I run the script with conda env:
> python=3.8.10 32bit, windows
it prints:

[(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,)]

whatever it is, it can print the result.

while if I run the script with the python env installed by python.org 3.8.10 32bit release,
it prints Error:

Traceback (most recent call last):
  File &quot;test.py&quot;, line 68, in &lt;module&gt;
    test()
  File &quot;test.py&quot;, line 43, in test
    t.execute(&#39;&#39;&#39;SELECT m_mail_info.id
sqlite3.OperationalError: no such table: json_tree

And I've tried with raw sql on SQLiteStudio, it returns the same result as in Conda env

json查询用于Python的sqlite3在Conda环境和python.org发布环境中。

I don't know how to find the difference between conda env and python official release env.

If you want to reproduce the issue, you may create a table with only one column, and in the query statement you must make use of json_each or json_tree .

答案1

得分: 1

以下是翻译好的部分:

"json_each" 和 "json_tree" 函数仅在 SQLite 的默认版本中从 3.38 版本(2022-02-22)开始包含。

Python 3.8 版本较旧,因此使用的是不包含它们的旧版本。

您可以通过以下方式确认:

import sqlite3

print(sqlite3.sqlite_version)

这篇Stack Overflow 帖子建议直接替换 Python 树中的 sqlite3.dll 模块...

英文:

The json_each and json_tree functions are only included by default in SQLite from version 3.38 (2022-02-22).

Python 3.8 is much older, and because of that uses an older version that does not include them.

You can confirm it with:

import sqlite3

print(sqlite3.sqlite_version)

This other SO post suggests that
it is possible to replace the sqlite3.dll module directly in the Python tree...

huangapple
  • 本文由 发表于 2023年5月6日 16:09:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76187819.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定