SQLite数据存储 [英] Sqlite data storage

问题描述

我正在运行一个sqlite命令

I am running a sqlite command

SELECT address FROM Locations WHERE address='hola'

在数据库表上

即使结果应该是3行，输出也只有一行

The output is only one row even though the result should be 3 rows

这是我运行的在数据库中插入值的Python 3代码:

This is the Python 3 code I ran to insert values in the DB:

st = "hola"
st1 = st.encode()
st2 =memoryview(st1)

conn = sqlite3.connect('test.sqlite')
cur = conn.cursor()

cur.execute('''
INSERT INTO Locations(address)VALUES(?)''',(st,))
cur.execute('''
INSERT INTO Locations(address)VALUES(?)''',(st1,))
cur.execute('''
INSERT INTO Locations(address)VALUES(?)''',(st2,))

conn.commit()

推荐答案

在使用示例python代码填充数据库中的表之后，我运行了以下查询:

After using your sample python code to populate a table in a database, I ran this query:

sqlite> select *, typeof(address) from locations;
id          address     typeof(address)
----------  ----------  ---------------
1           hola        text           
2           hola        blob           
3           hola        blob           

您要插入的两个值存储为斑点，一个存储为文本字符串.

Two of the values you're inserting are stored as blobs, one as a text string.

来自有关如何比较不同类型的值的文档:

TEXT值小于BLOB值.

A TEXT value is less than a BLOB value.

也就是说，即使字符串的基础字节与blob中的字节相同，像'hola'这样的字符串也永远不会等于blob.这就是为什么SELECT仅返回一个值的原因.

That is to say, a string like 'hola' will never be equal to a blob, even if the underlying bytes of the string are identical to the bytes in the blob. That's why only one value is being returned by your SELECT.

这篇关于SQLite数据存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！