I am using python 3.8.10 and the module sqlite3. Ubuntu 20.04.3 LTS @ Kernel: 5.4.0-90-generic x86_64

I have a database with the structure: id INTEGER primary key | parent_id INTEGER | key TEXT | type INTEGER | value TEXT. I can't edit the structure of the database nor am I allowed to change values in it.
Example record: 1250 | 1156 | TicketsLeft | 6 | 10

The code I'm using is:

#!/usr/bin/python3

import sqlite3

TextA= 'TextInDb'
def query_named(key_in_db):
    con = sqlite3.connect("DBNAME.db")
    cur = con.cursor()
    cur.execute('SELECT value FROM DBNAME WHERE key LIKE ?', ("%{}%".format(key_in_db),))
    records = cur.fetchall()
    print(records)
    for row in records:
        print(row)
    con.close()
    
    return records

print(query_named(TextA))

This returns:
[]
[]

Under Linux in python:
When I do 'SELECT value FROM DBNAME WHERE id = 1234' I get the corresponding result.
But when I 'SELECT value FROM DBNAME WHERE key LIKE "%SomeText%"' it returns: [].

Screenshots from DB Browser

Images: DB Browser Link
Working SQL Code

My aim is to get the ```value``` from a specific ```key``` so the placeholders % aren't really needed.

The prints are just for "debugging". I'm clueless why it doesn't work although within a SQLite Browser I am able to get the result I'm looking for.

Thanks to the comment from @forpas here is a working solution:

#!usr/bin/python3

import sqlite3

TextA = 'SomeText'
def query_named(key_in_db):
    con = sqlite3.connect("DBNAME.db")
    cur = con.cursor()
    cur.execute("SELECT value FROM DBNAME WHERE TRIM(key) =  ?", (key_in_db,))
    records = cur.fetchall()
    print(records)
    for row in records:
        print(row)
    con.close()

    return records

print(query_named(TextA))

Due to the comments:

Test-Scenarious

cur.execute('SELECT value FROM DBNAME WHERE key LIKE ?', ("{}".format('TicketsLeft'),))
return:
[]
[]
[]
[]
cur.execute('SELECT value FROM DBNAME WHERE key = ?', ('TicketsLeft',))
return:
[]
[]
cur.execute('SELECT value FROM DBNAME WHERE key = "TicketsLeft"')
return:
[]
[]
cur.execute("SELECT value FROM DBNAME WHERE key = 'TicketsLeft'")
return:
[]
[]
cur.execute("SELECT * FROM DBNAME WHERE id = 1250")
return:
[('4',)]
('4',)
[('4',)]

CodePudding user response：

It seems like you just haven't such record in your table. That may be some case-sensetive issue, I just copied your code and it works fine:

$ python d.py
[(None,)]
(None,)
[(None,)]

sqlite> select * from DBNAME;
1234||TextInDb||

CodePudding user response：

import sqlite3

TextA= 'TextInDb'

def query_named(key_in_db):

con = sqlite3.connect("DBNAME.db")

cur = con.cursor()

cur.execute("SELECT value FROM DBNAME WHERE key LIKE '%{}%'".format(key_in_db))

records = cur.fetchall()

print(records)
for row in records:
    print(row)
con.close()

return records

print(query_named(TextA))