MySQL: Parameterized column names in Python

Question

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:

(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!

nacho · Accepted Answer · 2019-03-11 11:10:36Z

2

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

edited Mar 11, 2019 at 11:10

answered Mar 11, 2019 at 10:49

nacho

5,3772 gold badges28 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

MySQL: Parameterized column names in Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related