0

Similar to Brunonono in Create MySQL Database in Python using the %s operator (even using the same packages) I'm trying to add columns from an excel table to a mysql table using the %s operator in Python. The error is the same:

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 '%s %s)' at line 1

The code is as follows

mydb=mysql.connector.connect(host="localhost",user="root")

#init cursor
mycursor=mydb.cursor()

column_title="ID"
cell_type="INT(10)"

mycursor.execute("ALTER TABLE Test ADD (%s %s)"),(column_title, cell_type)

Sadly, I wouldn't know how to apply the solution provided in the post above, where

mycursor.execute("CREATE DATABASE (%s)", (db_name))

was replaced with

create_statement = "CREATE DATABASE {:s}".format(db_name)
mycursor.execute(create_statement)
Community
  • 1
  • 1
cheesus
  • 1,111
  • 1
  • 16
  • 44
  • replace `(db_name)` with `(db_name,)` – shaik moeed Jul 04 '19 at 12:27
  • 1
    Play around with "format" in a Python interactive shell to get a feeling how it works. And of course read the docs. – Michael Butscher Jul 04 '19 at 12:30
  • the part with db_name is referencing another post. It is not actually part of my problem. My error occurs at the line mycursor.execute("ALTER TABLE Test ADD (%s %s)"),(column_title, cell_type) – cheesus Jul 04 '19 at 12:30
  • @MichaelButscher I assume that means that my problem is equally solvable using this method? :) Thats great – cheesus Jul 04 '19 at 12:32
  • 1
    You can't substitute literals like column names, table names or types using query parameters. You have to use string formatting for this. See [here](https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement). – shmee Jul 04 '19 at 12:32

2 Answers2

0

mycursor.execute("ALTER TABLE Test ADD (%s %s)"),(column_title, cell_type) is what you are doing currently

Instead do, 

mycursor.execute("ALTER TABLE Test ADD (%s %s)" % (column_title, cell_type))

Which would work unless I don't know MySQL syntax properly

Kaito
  • 183
  • 11
-1

Try this:

mycursor.execute('ALTER TABLE Test ADD (?, ?)',(column_title, cell_type))

But this doesn't use the %s operator.

Sorry, this works for SQLite and not MySQL.

Cauvery A
  • 1
  • 2