我必须下载一个数据库文件并解压缩它,然后在python中使用SQLite查询。 文件的位置:
出于某种奇怪的原因,如果在远程数据库上运行此代码,下面的代码将返回空结果(主.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” ]]
当前结果:
[]
缺陷在于:
“customdb”测试中会返回预期的结果,因为只有特定的名称/版本行。 在
primary.sqlite
上的SELECT version FROM packages where name = 'libcurl'
测试中返回预期结果,因为结果是按名称筛选的。你知道吗但是,由于上面引用的子查询没有按名称过滤,因此它为
packages
中的所有行选择最大的“numberized”版本相关问题 更多 >
编程相关推荐