placeholders for table names in python mysql

Question

I am using python sql to edit a very simple table named students (whose columns are name and age), as shown below:

('Rachel', 22)
('Linckle', 33)
('Bob', 45)
('Amanda', 25)
('Jacob', 85)
('Avi', 65)
('Michelle', 45)

I am defining python functions to execute SQL code.

In my first function I want to update the age values in students table where the name matches something (e.g. Bob). If I define the following function:

def update_age(age, name):
    c.execute("""UPDATE students SET age = %s
    WHERE name = %s""", (age, name))

And then:

update_age(99, 'Bob')

I will get:

('Rachel', 22)
('Linckle', 33)
('Bob', 99)
('Amanda', 25)
('Jacob', 85)
('Avi', 65)
('Michelle', 45)

On a second function I would like to specify also the name of the table, with the following code:

def update_age_table(table, age, name):
    c.execute("""UPDATE %s SET age = %s
    WHERE name = %s""", 
             (table, age, name)) # note that here I am only replacing students by the placeholder %s

Then if I do:

update_age_table(table='students', age=95, name='Jacob')

I will get the following error message (it is long, I am only displaying the last sentence:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''students' SET age = 95
    WHERE name = 'Jacob'' at line 1

I guess that the error comes from the fact that I am assigning two of the placeholders to variables, namely age and name, which is not the case of the table name, where there is no variable assignment.

Does anyone know how I can use placeholders in SQL commands without assigning them to variables?

nacho · Accepted Answer · 2019-01-10T12:54:03.583

4

Thatś because you cannot pass the table name as a parameter in the execute sentence. You should do it this way:

def update_age_table(table, age, name):
    c.execute("UPDATE "+table+" SET age = %s
    WHERE name = %s", 
             (table, age, name)) #

The prepared statement doesn't work for table names

EDIT You have to remove the table parameter like this:

def update_age_table(table, age, name):
    c.execute("UPDATE "+table+" SET age = %s WHERE name = %s",(age, name)) #

Sorry was a mistake

edited Jan 10 '19 at 12:54

answered Jan 09 '19 at 16:12

nacho

5,280
2
25
34

If I try to define the function with your code I will get `SyntaxError: EOL while scanning string literal`. I then tried to put the `c.execute()` command in a single line (to fix the error shown above), then if I run `update_age_table(table='students', age=95, name='Jacob')` I get `ProgrammingError: Not all parameters were used in the SQL statement` – BCArg Jan 10 '19 at 08:41
I edited my answer, had to remove the table parameter when you call c.execute() – nacho Jan 10 '19 at 12:55
It works now, the answer gets down to string concatenation in python, clever – BCArg Jan 10 '19 at 14:40

score 0 · Answer 2 · answered Nov 17 '21 at 14:43

0

    dt= datetime.datetime.now()
    new_date=str(dt)
    idname=input("Please enter Your Id.  ")
    bname= input("Please Enter name of book which you want to Issue: ")
    idn=(idname,)
    sql="insert into id%s (issuedbook,date)"%idn +"values (%s,%s)"

    val=(bname,new_date)
    cursor.execute(sql,val)
    cnx.commit()
insert_data()```

answered Nov 17 '21 at 14:43

Suraj

1
1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 17 '21 at 14:49

score 0 · Answer 3 · answered Jan 25 '22 at 22:53

Without having tested it, this should be a better coding style of the accepted answer. As the whole Q/A shows, the variables are passed only at cursor.execution() time to make it more secure, but the table statement of the execute() string is evaluated before the args are evaluated, that is why tables have to be plain text evaluated before execute() but the variables do not. See another example with similar challenge at Python - pass a list as params to SQL, plus more variables where the table is not passed either.

Therefore, just as an add-on for the rightly accepted query:

def update_age_table(UPDATE_QUERY, args):
    c.execute(UPDATE_QUERY, args)
    c.commit()

# example for string testing:
table, age, name = "table_x", 2, "name_y"

UPDATE_QUERY = f"""
   UPDATE {table}
   SET age = %s
   WHERE name = %s
"""

# # UPDATE_QUERY Out:
# '\n    UPDATE table_x\n    SET age = %s\n    WHERE name = %s\n'

args = [age, name]
update_age_table(UPDATE_QUERY, args)

placeholders for table names in python mysql

3 Answers3