python - SQLite Query in Python returns empty results
问题描述
I have to download a database file and decompress it and query using SQLite query in python. Location of the file:
For some strange reason the below code returns empty results if the run this code on remote database (primary.sqlite) , unlike if I run the same code to a custom created database with similar values.
The below code returns all the values between two version ranges if the upper limit of the version given is greater the the max value of the version in the table.
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");
Query:
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))
Results:
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
If I download the file and decompress it and run the simple code it returns these results so there is nothing wrong with the downloading the remote file and decompressing it.
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” ]]
The below code returns all the values between two version ranges if the upper limit of the version given is greater the the max value of the version in the table.
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");
Query:
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))
Results:
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
If I download the file and decompress it and run the simple code it returns these results so there is nothing wrong with the downloading the remote file and decompressing it.
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” ]]
However if I run the same code as that run on "primary.sqlite" file gives empty results.
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))
Expected Results:
[ [ "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” ]]
Current Results:
[]
解决方案
缺陷在这里：
>=
(SELECT MAX(1000000 * replace(version, '.', 'x') +
1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
replace(version, '.', '000') % 1000 ) FROM packages)
在“customdb”测试中返回预期结果，因为只有特定的名称/版本行。SELECT version FROM packages where name = 'libcurl'在测试中返回预期结果，primary.sqlite因为结果是按名称过滤的。
但是，由于上面引用的子查询没有按名称过滤，它正在为所有行选择最大“编号”版本packages
推荐阅读
- python-3.x - 迭代来自两个数据帧的列以估计相关性和 p 值
- sql - Azure 函数：异常：TypeError：connection.query 不是函数
- flutter - 更新多提供者颤振的问题
- go - 转到 TCP 侦听器 ACL
- python-3.x - 为什么这个正则表达式 (\d)\d\1 不能从字符串中找到替代的重复数字？
- chat - 控制 salesforce 实时聊天记录状态字段值
- postgresql - 无法使用 Postgresql 将年龄函数转换为浮点数
- .net-core - 有没有办法在使用 Microsoft.IDistributedCache 时重新连接 Redis NoConnection 异常？
- c# - 值的正则表达式范围从 1 - 1440（整数）
- javascript - 在一定时间内显示某物