python sqlite query selecting text padded with multiple zeros

Question

I had trouble with selecting TEXT data type padded with zeros in front.

Found out there's something wrong with my DB.

My multiple tries and all of answers by peers should all should work.

Thanks @snakecharmerb and @forpas for pointing out for possible flaw in DB.

For example, code column includes

000001, 000010, 000300 ...

There are also a multiple of Data with same code.

code    date
000030  20210101
000030  20210102
000030  20210103
000030  20210104
000030  20210105
...

I need to loop through a list so I tried multiple ways of using f-string, but it did not work.

con = sqlite3.connect("DIRECTORY")
cur = con.cursor()


code = '000030' // does not work
code = 000030 // forbidden by python 3.7

query = cur.execute(f"SELECT * From TABLE where code is {code}") // should work
query = cur.execute(f"SELECT * From TABLE where code is '{code}'") // should work
query = cur.execute(f'SELECT * From TABLE where code is "{code}"') // should work
query = cur.execute('SELECT * From TABLE where code = ?',('000030',)) // should work

query = cur.execute("SELECT * From TABLE where code is 000030") // works but cannot loop through a list

Also tried replacing 'is' with '=', '=='. All should work.

You probably want to use `=`, not `is`: `query = cur.execute('SELECT * From TABLE where code = ?',('000030',))` — snakecharmerb, Jun 20 '21 at 08:50

score 2 · Answer 1 · answered Jun 20 '21 at 09:29

Whatever the data type of the column code is, this query should work:

query = cur.execute("SELECT * From TABLE where code = ?", ("000030",))

Try with implicit conversions to integers for the column value and the parameter that you pass:

query = cur.execute("SELECT * From TABLE where code + 0 = ? + 0", ("000030",))

If this does not work, it means that the values of the column code are not like the ones that you posted in your question.

yes there is sth wrong with my DB thanks. – hyukkyulee Jun 20 '21 at 09:45 — hyukkyulee, Jun 20 '21 at 09:45

score 1 · Answer 2 · answered Jun 20 '21 at 09:05

Here's an working example:

import sqlite3


conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("""CREATE TABLE test (code TEXT)""")
for i in range(0, 100, 10):
    cur.execute("""INSERT INTO test (code) VALUES (?)""", (str(i).zfill(5),))
conn.commit()
cur.execute("""SELECT * FROM test""")
for row in cur:
    print(row)
print()
cur.execute("""SELECT code FROM test WHERE code = ?""", ('00030',))
for row in cur:
    print(row)
print()
conn.close()

Output

('00000',)
('00010',)
('00020',)
('00030',)
('00040',)
('00050',)
('00060',)
('00070',)
('00080',)
('00090',)

('00030',)

python sqlite query selecting text padded with multiple zeros

2 Answers2