CODEWITHSUNDEEP
pythonsqlvariablespyodbc
Long_dog
Long_dog

Reputation: 103

How to perform an SQL query with PYODBC using a column name variable?

I have written several functions to get particular items of data like this:

def get_short_desc(self, part_num):
    sql = 'SELECT impShortDescription FROM Parts '
    sql += 'WHERE impPartID LIKE ?'

    self.cursor.execute(sql, [part_num])
    item = self.cursor.fetchone().impShortDescription

    return item

I want to paramaterise the functions so I am not repeating myself (DRY):

def get_part_entry_item(self, var, part_num):
    sql = 'SELECT ? FROM Parts '
    sql += 'WHERE impPartID LIKE ?'

    self.cursor.execute(sql, [var, part_num])
    item = getattr(self.cursor.fetchone(), var)

    return item

The execute statement passes the value of part_num (a string) with single quotes which is correct. When the column name variable var = 'impShortDescription' the resulting SQL is effectively SELECT 'impShortDescription'... so the item returned is

('impShortDescription', )

so getattr(self.cursor.fetchone(), var) causes the error

AttributeError: 'pyodbc.Row' object has no attribute 'impShortDescription'

How do I pass a column name without the quotes?

Upvotes: 0

Views: 412

Answers (1)

Tim Roberts
Tim Roberts

Reputation: 54698

SQL substitution only works on values, not on table or field names. Do this. Yes, this means you must be careful not to pass user data as the field name.

def get_part_entry_item(self, var, part_num):
    sql = f'SELECT {var} FROM Parts '
    sql += 'WHERE impPartID LIKE ?'

    self.cursor.execute(sql, (part_num,))
    return self.cursor.fetchone()[var]

Upvotes: 1

Related Questions