Retrieving text from sqlite without string formatting for comparison

Question

Saving a string into a sqlite table, retrieving it again and comparing it to the original requires some filters to work and i dont know why exactly.

tl;dr How can i retrieve string Data from the SQLITE DB without requiring Filter Nr 3 as its dangerous for more complex strings ?


import sqlite3

RAWSTRING = 'This is a DB Teststing'

# create database and table
currentdb = sqlite3.connect('test.db')
currentdb.execute('''CREATE TABLE tickertable (teststring text)''')

# enter RAWSTRING into databasse
currentdb.execute('''INSERT INTO tickertable VALUES(?);''', (RAWSTRING,))

# get RAWSTRING from database
cursorObj = currentdb.cursor()
cursorObj.execute('SELECT * FROM tickertable')
DB_RAWSTRING = cursorObj.fetchall()
currentdb.commit()
currentdb.close()

# Prints This is a DB Teststing
print('originalstring : ', RAWSTRING)

# Prints [('This is a DB Teststing',)]
print('retrieved from DB: ', DB_RAWSTRING)

# Get first entry from List because fetchall gives a list
FILTER1_DB_RAWSTRING = DB_RAWSTRING[0]

# Convert the Listelement to String because its still a listelement and comparing fails to string
FILTER2_DB_RAWSTRING = str(FILTER1_DB_RAWSTRING)

# Remove annoying db extra characters and i dont know why they exist anyway 
FILTER3_DB_RAWSTRING = FILTER2_DB_RAWSTRING.replace("'", "").replace("(", "").replace(")", "").replace(",", "")

if RAWSTRING == FILTER3_DB_RAWSTRING:
    print('Strings are the same as they should')
else:
    print('String are not the same because of db weirdness')

The db does not add commas and single quotes to the values entered. Your code saves these values the way they are. So search for a solution for that issue. — forpas, Oct 19 '21 at 15:27
Closely related: https://stackoverflow.com/questions/7011291/how-to-get-a-single-result-from-a-sql-query-in-python — snakecharmerb, Oct 20 '21 at 08:42

score 1 · Accepted Answer · answered Oct 19 '21 at 14:38

So here's your problem: fetchall returns a list of tuples. This means that casting them to a string puts pesky parenthesis around each row and commas between each element of each row. If you'd like to retrieve the raw information from each column, that can be done by indexing the tuples:

entries = cursorObj.fetchall()
first_row = entries[0]
first_item = first_row[0]
print(first_item)

This ought to print just the content of the first row and column in the DB. If not, let me know!

David

Retrieving text from sqlite without string formatting for comparison

1 Answers1