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

Question

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?

Tim Roberts · Accepted Answer · 2021-03-18 06:11:38Z

1

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]

answered Mar 18, 2021 at 6:11

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

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

1 Comment

Long_dog Over a year ago

Thank you @Tim Roberts. User data issue noted.

Collectives™ on Stack Overflow

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

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