MySQL syntax error while working with placeholders in Python

Question

I want to create table name with a one column but I'm getting this error:

mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''test' 'int')' at line 1

The code I'm trying to use:

table_name = 'test'
create_table = f'CREATE TABLE {table_name} (%s %s)'
cursor.execute(create_table,(headers[0], data_types[0],))

I've tried typing manually table name, column name and data type but it ended up with the same result. I've seen a lot of solutions on this problem but none of them worked out with this case

you have to specify values you have under headers[0] and data_types[0], otherwise it's hard to guess the query you submit — Anatoliy R
– Anatoliy R, Commented Nov 23, 2019 at 13:59
It's stated in the error message. headers[0] == 'test' and data_types[0] == 'int'. I've got my answer already but thank you for trying to help — kappaprajd
– kappaprajd, Commented Nov 23, 2019 at 14:12

GMB · Accepted Answer · 2019-11-23 14:00:49Z

1

You just can't bind this part of the query. Bind parameters are essentially used to replace litterals in the query, not random part of it. Under the hood, your RDBMS must be able to prepare the query without seeing the values of the bind parameters.

In this case, you need string concatenation:

table_name = 'test'
create_table = f'CREATE TABLE {table_name} ({headers[0]} {data_types[0]})';
cursor.execute(create_table);

answered Nov 23, 2019 at 14:00

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

kappaprajd Over a year ago

I was so stupid trying to use placeholders there. Thank you for your answer.

Collectives™ on Stack Overflow

MySQL syntax error while working with placeholders in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related