Ahmed Al-haddad Ahmed Al-haddad - 7 months ago 12
SQL Question

MySQL returns the name of the column instead of the value

I am reading from MySQL database to find the value of specific columns in the database using the following command:

select = ["c9", "TEMP"]
c.execute("SELECT Client, %s, Date_Time FROM data WHERE Client = %s and Date_Time > '2016-04-26 18:17:15'", (select[1],select[0]))


But instead I am getting the values below, which are correct for the first and the third columns, however, I am getting the name of the second column instead of the value:

('c9', 'TEMP', datetime.datetime(2016, 4, 26, 20, 6, 53))
('c9', 'TEMP', datetime.datetime(2016, 4, 26, 20, 7, 53))
('c9', 'TEMP', datetime.datetime(2016, 4, 26, 20, 8, 53))


instead of this for example

('c9', '2700', datetime.datetime(2016, 4, 26, 20, 6, 53))
('c9', '2720', datetime.datetime(2016, 4, 26, 20, 7, 53))
('c9', '2700', datetime.datetime(2016, 4, 26, 20, 8, 53))


So, how do I retrieve the value instead of the name of the column?

Answer

Column names can't be parameterized in the query. You'll have to insert the column name some other way:

select = ["c9", "TEMP"]
query = "SELECT Client, {}, Date_Time FROM data WHERE Client = %s and Date_Time > '2016-04-26 18:17:15'".format(select[1])
cursor.execute(query, (select[0],))

Note that this leaves you vulnerable to SQL injection where you insert the column name. You'll want to check it to make sure that it's safe. I'd err on the side of caution and do something like:

assert select[1].isalnum()

(AssertionError probably isn't the right error here since it should be used to check internal invariants. Raising a ValueError or custom exception might be more appropriate).