CODEWITHSUNDEEP
pythonmysql
Louis Storming
Louis Storming

Reputation: 151

MySQL: Parameterized column names in Python

My issue came when I decided to make a method that could handle a variation of queries, instead of coding 3 methods. I wanted to do so as to recycle code.

I have this table:

enter image description here

(I created it in the purpose of this question. You can do it by:

create table example (id int(1), ex1 varchar(15), ex2 varchar(15), ex3 varchar(15));
insert into example values(1, 'whatever11', 'whatever12', 'whatever13');
insert into example values(2, 'whatever21', 'whatever22', 'whatever23');
insert into example values(3, 'whatever31', 'whatever32', 'whatever33');

SO: I was trying to parameterize column names. I have done it in the whereclause all the time, but as I mention earlier, I thought it would be cleaner and more optimal to do just one method ( select %s from example where id=%s), instead of 3 different: (select ex1 from etc, select ex2 from etc.

So I tried this:

So the normal method is this:

def getex1(id):
    myCursor=mydb.cursor()
    query='select ex1 from example where id=%s'
    myCursor.execute(query, (id,))
    result=myCursor.fetchone()[0]
    print(result) #prints 'whatever11 if id=1'

When I searched how to do parameterized queries, I saw that to do various parameters, you can just do something like input=(param1, param2, and then execute by (query, input), so I tried to do so but with the column name:

here, info is 'ex1', 'ex2' or 'ex3':

def getAllFromExample(info, id):
    myCursor = mydb.cursor()
    query= 'select %s from example where id=%s'
    input = (info, id)
    myCursor.execute(query, input)
    result = myCursor.fetchone()[0]
    print(result) #in this case, prints the column names 'ex1', 'ex2', not the content

My guess is that you can't just do the param by columns, because you are not assigning a value (like in a whereor in a group by, you have an assignment: whatever=value).

Any insights on this? I did quite the research but did not find anything. here it is mentioned this.

Anything you see wrong with the question, ask me and I'll make it clearer!

Upvotes: 1

Views: 1245

Answers (1)

nacho
nacho

Reputation: 5397

You cannot parametrizied the table names, you only can do it with the column values, so you would have to do:

def getAllFromExample(info, DNI):
    myCursor = mydb.cursor()
    query= 'select '+info+' from example where id=%s'
    input = (id,)
    myCursor.execute(query, input)
    result = myCursor.fetchone()[0]
    print(result) #in this case, prints the column name

Upvotes: 2

Related Questions