CODEWITHSUNDEEP
python-3.xsqliteflask
ManKino
ManKino

Reputation: 47

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

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': ''}

Upvotes: 1

Views: 945

Answers (1)

Matt Healy
Matt Healy

Reputation: 18531

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])

Upvotes: 2

Related Questions