How to make SQLite query where both parameter and value are variables?

I’m trying to allow the user to search a SQL table, by first taking their input on which PARAMETER they want to search by, and after that taking their input on which VALUE they want to search for inside that parameter.

I take both inputs, assign them to two different variables, and then pass those variables into a SQL execute command. This works if I’m only passing the value variable and hardcoding the parameter, but not the other way around. This would suggest that the problem is processing the parameter variable, but I don’t know what the problem is.

This is going to be for a Django site, but for the moment I’m just testing it as a command line app. It’s running Python 3.6.8 and sqlite3.

I’ve tried hardcoding only the parameter and dynamically inputting the value, which worked, and viceversa, which didn’t. I also tried it with different values which hold different value types (ints, varchar, etc.), to make sure it wasn’t a problem with the variable type I was passing to the execute command, and they all worked when hardcoding the parameter.

This suggests to me that the problem isn’t the variables themselves, but passing a variable as a parameter in an SQL query. I tried looking up people with similar problems, but not only did I not find any, I also didn’t find any documentation (official or otherwise) that suggests that it’s possible (or desired) to pass a variable as a parameter.

import sqlite3

conn = sqlite3.connect('[project directory]/db.sqlite3')
c = conn.cursor()

def search_entry():
    '''Searches database based on user input, returns rows
    '''

    # Search parameters dictionary
    search_par = {
        '1': 'id',
        '2': 'date',
        '3': 'account',
        '4': 'trans_type',
        '5': 'main_cat',
        '6': 'sub_cat',
        '7': 'name',
        '8': 'amount',
        '9': 'all',
    }
    print('n')
    for key in search_par:
        print(key, ': ', search_par[key])
    print('n')

    # Select parameter number to search by
    while True:
        try:
            par_select = str(int(input('Select parameter number to search by: ')))
            if (int(par_select) < 10 and int(par_select) > 0):
                break
            else:
                pass
        except:
            pass
    sel_par = search_par[par_select]  # Select corresponding parameter

    # Select value to search
    while True:
        try:
            id_search = input('Select value to search: ')
            break
        except:
            pass

    c.execute('SELECT * FROM form_entry WHERE (?)=(?)', (sel_par, id_search,))
    rows = c.fetchall()
    for row in rows:
        for col in row:
            print('%s,' % col)
        print('n')

search_entry()

I expect this to return the results from the query, for example, if the user chose to search by id, and then search for the id 19, I’d expect those 2 variables to be passed to the execute command, and the search conducted. Instead, I get nothing back. No errors, just no output.

Answer

In 2 words: you can’t

SQL only accepts parametization of values, not identifiers, column names, table names etc. The why is a longer conversation but it’s something akin to why you can’t have variables in a programming language that get their name from another variable

The only thing you can do is build your sql in your programming language; concatenate in the column name and append the parameter value to the collection that the command object uses

Here’s a pseudo code (I don’t write python, sorry):

c.execute('SELECT * FROM form_entry WHERE '+colnamesarray[i]=?', colvaluesarray[i])

Advertisement

Answer