Sqlite data storage

Question

I am running a sqlite command

SELECT address FROM Locations WHERE address='hola'

On a data base table

table

table structure

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

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()

• Shawn: What does SELECT *, typeof(address) FROM Locations give you? text for all three, or are two of them blob?
• cpt.John: text , blob ,blob

Answer 1

This is a charset problem. st it is ISO charset st1 and st2 it is UTF-8 charset.

And your query is in ISO charset format.

Answer 2

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.

From the documentation on how values of different types are compared:

A TEXT value is less than a BLOB value.

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.

• cpt.John: is there a way to select the blob field. also why TEXT field is not giving error when blob is entered
• Shawn: @JohnFrancis Sqlite uses dynamic typing; it's perfectly fine to store a blob in a column with a different affinity. sqlite.org/datatype3.html is mandatory reading for understanding how sqlite works. (And SELECT * FROM Locations WHERE address = X'686F6C61' is one way - that uses a blob literal. But better is to just not store strings as blobs in the first place, but as text values)

Answer 3

you are confusing comments

""" I'm multi-line comment"""

with strings

"I'm a string"

I suggest you to modify your code as follows

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()

• cpt.John: The value are entered properly with my python code into DB table. The problem is the sqlite command display only 1st row as output