CODEWITHSUNDEEP
pythonmysqldatabase
oscar0urselli
oscar0urselli

Reputation: 45

Errors adding a new column to a table in MySQL with Python

I'm new to MySQL and database in general, however I'm having some issue when I try to add a new column of integer inside my table. To add a new column I'm doing so:

import mysql.connector

mydb = mysql.connector.connect(
    # host, user, password and database
)

mycursor = mydb.cursor(buffered = True)

# some stuff to get the variable domain

mycursor.execute('ALTER TABLE domainsMoreUsed ADD {} INTEGER(10)'.format(domain)) # domain is a string

but i get this error:

raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
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 'in INTEGER(10)' at line 1

I get the same error above also trying:

mycursor.execute('ALTER TABLE domainsMoreUsed ADD %s INTEGER(10)' % domain)

Instead when I use:

mycursor.execute('ALTER TABLE domainsMoreUsed ADD %s INTEGER(10)', (domain))

i get:

raise ValueError("Could not process parameters")
ValueError: Could not process parameters

I read some post of other users about the same error, but I couldn't find what I need. I'm pretty sure about the SQL syntax being correct. I'm using MySQL 8.0 with Python 3.8.3 on Windows 10. Thank you in advance for your help.

Upvotes: 1

Views: 105

Answers (1)

aneroid
aneroid

Reputation: 15962

What is the string domain set to? The error message syntax to use near 'in INTEGER(10)' at line 1, implies "in", which is a reserved word. If you want to use that for a table or column name, you need to add backticks: " ` " (left of '1' on the top row of your keyboard) around them.

Change your queries like this:

mycursor.execute('ALTER TABLE domainsMoreUsed ADD `{}` INTEGER(10)'.format(domain))

mycursor.execute('ALTER TABLE domainsMoreUsed ADD `%s` INTEGER(10)', (domain))

Upvotes: 1

Related Questions