Python中的SQLite查询返回空结果

Question

我必须下载一个数据库文件并解压缩它，然后在python中使用SQLite查询。 文件的位置：

http://amazonlinux.us-east-2.amazonaws.com/2/core/2.0/x86_64/11be506f86779fba9940dfce8be18d996726d1db2267fd3b13585c19e638db48/repodata/primary.sqlite.gz

出于某种奇怪的原因，如果在远程数据库上运行此代码，下面的代码将返回空结果(主.sqlite)，不同于对具有类似值的自定义创建的数据库运行相同的代码。你知道吗

如果 给定版本的上限大于表中版本的最大值。你知道吗

Customdb.db :

create table packages(version varchar,name varchar);


insert into packages values('7.61.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.61.1',"libcurl");

查询：

import sqlite3
conn = sqlite3.connect('Customdb.db')

c = conn.cursor()

software_name = "libcurl"
v1_start = "0.0.0"
v2_end_inc = "24.12.1"
v2_end_ex = "24.12.1"
only_version = '20.61.1'
print (type(v2_end_inc))

c.execute("""
        select name,version from packages
        where name = ?1 and
        1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000
        between
        (1000000 * replace(?2, '.', 'x') +
        1000 * replace(substr(?2, instr(?2, '.') + 1), '.', 'x') +
        replace(?2, '.', '000') % 1000)
        and
        (1000000 * replace(?3, '.', 'x') +
        1000 * replace(substr(?3, instr(?3, '.') + 1), '.', 'x') +
        replace(?3, '.', '000') % 1000)
        and
        (SELECT 1000000 * replace(?3, '.', 'x') +
        1000 * replace(substr(?3, instr(?3, '.') + 1), '.', 'x') +
        replace(?3, '.', '000') % 1000) >=
        (SELECT MAX(1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000 ) FROM packages)
        ORDER BY
        (1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000)
    """, (software_name, v1_start, v2_end_inc))

结果：

libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.61.1                                                                                                                          
libcurl|7.61.1

如果我下载文件并解压缩它并运行简单的代码，它会返回这些结果，所以下载远程文件并解压缩它并没有什么问题。你知道吗

import sqlite3
conn = sqlite3.connect('primary.sqlite')

c = conn.cursor()

c.execute("SELECT version FROM packages where name = 'libcurl' ")

results = c.fetchall()

package_obj_list = []  
l = len(results)
for package in results:
   package_obj_list.append(package)

print (package_obj_list)

results are:

[ [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  “7.61.1” ], [  “7.61.1” ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  “7.61.1” ]]

如果 给定版本的上限大于表中版本的最大值。你知道吗

Customdb.db :

create table packages(version varchar,name varchar);


insert into packages values('7.61.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.51.1',"libcurl");
insert into packages values('7.61.1',"libcurl");

查询：

import sqlite3
conn = sqlite3.connect('Customdb.db')

c = conn.cursor()

software_name = "libcurl"
v1_start = "0.0.0"
v2_end_inc = "24.12.1"
v2_end_ex = "24.12.1"
only_version = '20.61.1'
print (type(v2_end_inc))

c.execute("""
        select name,version from packages
        where name = ?1 and
        1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000
        between
        (1000000 * replace(?2, '.', 'x') +
        1000 * replace(substr(?2, instr(?2, '.') + 1), '.', 'x') +
        replace(?2, '.', '000') % 1000)
        and
        (1000000 * replace(?3, '.', 'x') +
        1000 * replace(substr(?3, instr(?3, '.') + 1), '.', 'x') +
        replace(?3, '.', '000') % 1000)
        and
        (SELECT 1000000 * replace(?3, '.', 'x') +
        1000 * replace(substr(?3, instr(?3, '.') + 1), '.', 'x') +
        replace(?3, '.', '000') % 1000) >=
        (SELECT MAX(1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000 ) FROM packages)
        ORDER BY
        (1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000)
    """, (software_name, v1_start, v2_end_inc))

结果：

libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.51.1                                                                                                                          
libcurl|7.61.1                                                                                                                          
libcurl|7.61.1

如果我下载文件并解压缩它并运行简单的代码，它会返回这些结果，所以下载远程文件并解压缩它并没有什么问题。你知道吗

import sqlite3
conn = sqlite3.connect('primary.sqlite')

c = conn.cursor()

c.execute("SELECT version FROM packages where name = 'libcurl' ")

results = c.fetchall()

package_obj_list = []  
l = len(results)
for package in results:
   package_obj_list.append(package)

print (package_obj_list)

results are:

[ [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  “7.61.1” ], [  “7.61.1” ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  “7.61.1” ]]

但是如果我运行的代码与主.sqlite“文件提供空结果。你知道吗

software_name = "libcurl"
v1_start = "0.0.0"
v2_end_inc = "24.12.1"
v2_end_ex = "24.12.1"
only_version = '20.61.1'
print (type(v2_end_inc))

c.execute("""
        select name,version from packages
        where name = ?1 and
        1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000
        between
        (1000000 * replace(?2, '.', 'x') +
        1000 * replace(substr(?2, instr(?2, '.') + 1), '.', 'x') +
        replace(?2, '.', '000') % 1000)
        and
        (1000000 * replace(?3, '.', 'x') +
        1000 * replace(substr(?3, instr(?3, '.') + 1), '.', 'x') +
        replace(?3, '.', '000') % 1000)
        and
        (SELECT 1000000 * replace(?3, '.', 'x') +
        1000 * replace(substr(?3, instr(?3, '.') + 1), '.', 'x') +
        replace(?3, '.', '000') % 1000) >=
        (SELECT MAX(1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000 ) FROM packages)
        ORDER BY
        (1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000)
    """, (software_name, v1_start, v2_end_inc))

预期结果：

[ [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  “7.61.1” ], [  “7.61.1” ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  "7.55.1" ], [  “7.61.1” ]]

当前结果：

[]

Answer 1

缺陷在于：

>=
        (SELECT MAX(1000000 * replace(version, '.', 'x') +
        1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
        replace(version, '.', '000') % 1000 ) FROM packages)

“customdb”测试中会返回预期的结果，因为只有特定的名称/版本行。 在primary.sqlite上的SELECT version FROM packages where name = 'libcurl'测试中返回预期结果，因为结果是按名称筛选的。你知道吗

但是，由于上面引用的子查询没有按名称过滤，因此它为packages中的所有行选择最大的“numberized”版本