CODEWITHSUNDEEP
pythonmysqlpython-3.xplaceholder
BCArg
BCArg

Reputation: 2250

placeholders for table names in python mysql

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?

Upvotes: 2

Views: 2292

Answers (3)

questionto42
questionto42

Reputation: 9590

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)

Upvotes: 0

Suraj
Suraj

Reputation: 1

    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()```

Upvotes: 0

nacho
nacho

Reputation: 5396

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

Upvotes: 4

Related Questions