Query to database is not returning row value, instead is returning column name

Question

I'm trying to access the data of a row in a specific column, but the function is returning the name of the column instead of the row value. I notice that if I use the star (*) to select everything, the function returns all the values, not the keys, and if I manually type the name of the column I'm trying to access, it works too.

In the function _dict is a dictionary I'm sending as a parameter and its keys are the same as the column names in the database, so what I'm trying to do is every time the loop change to a different key, convert that to string, and select the value of that column name in the database.

def query(_dict, zipcode):
    # Connection to database
    conn = sqlite3.connect('mashup.db')
    # Creation of cursor
    cur = conn.cursor()
    # Make query to database
    for key in _dict:
        key_name = str(key)
        cur.execute("SELECT ? FROM places WHERE postal_code = ?",[key_name,zipcode])
        db_query = cur.fetchone()
        _dict[key] = db_query[0]

    return _dict

I also have tried cur.fetchone() and fetchall() but I get the same result.

Just in case, here is the dict i'm passing as parameter:

# Creation of the dict of places
places_dict = {
    'accuracy': '',
    'admin_code1': '',
    'admin_code2': '',
    'admin_code3': '',
    'admin_name1': '',
    'admin_name2': '',
    'admin_name3': '',
    'country_code': '',
    'latitude': '',
    'longitude': '',
    'place_name': '',
    'postal_code': ''}

Matt Healy · Accepted Answer · 2018-09-04 00:40:40Z

2

Placeholders (i.e. using ?) is not supported for column names or table names - only for values. So what's happening is that your query becomes literally:

SELECT 'accuracy' FROM places WHERE postal_code = '1234';

which is why you get the column name instead of the value stored in the row.

You'll need to format the string yourself to contain the column name, but please note that this method is vulnerable to SQL injection so you should ensure that your dictionary of column names is trusted and contains "good" values.

Example:

cur.execute("SELECT {} FROM places WHERE postal_code = ?".format(key_name), [zipcode])

answered Sep 4, 2018 at 0:40

Matt Healy

18.6k5 gold badges59 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ManKino Over a year ago

Thanks for your answer, it solved the problem, I knew that method but didn't do it exactly because the sql injection, but if is the only way, then, has to be that way. Thanks again.

Collectives™ on Stack Overflow

Query to database is not returning row value, instead is returning column name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related