Rename a Column name in a specific Table in SQL from Python

Question

I'm trying to rename a column name in SQL-Server from Python.

Code

def ModifeingColumnName():
    try:
        myconn = mysql.connector.connect(host ="localhost" , user = "root" , password = "", database = "test")
        mycurs = myconn.cursor()
        mycurs.execute('''EXEC SP_RENAME 'test_table.Name','First_Name','COLUMN' ''')
        myconn.commit()
        print('changed successfully!')
    except:
        print('failed!')

my database name is (test)
my table is (test_table)
my column name is (Name)

Issue

It doesn't work. It always reached the except block.

What's the problem?

I'm using SQL and here is the error when i remove the try block :

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXEC SP_RENAME 'test.Name','First_Name','COLUMN'' at line

What happens if you print the exception rather than swallowing it and printing `failed!`? Also, you are using a `mysql` module but using a T-SQL query, so what are you using as your database, and are you using the right module to connect to it? — wkl, Jul 04 '22 at 18:59
Can you please debug your exception and add the error output. See [how to print the exception](https://stackoverflow.com/questions/1483429/how-do-i-print-an-exception-in-python). — hc_dev, Jul 04 '22 at 19:01
Based on your update, your problem is you're using `T-SQL` syntax to perform a table update when your backing database is MariaDB/MySQL. `SP_RENAME` is Microsoft SQL Server-specific. — wkl, Jul 04 '22 at 19:24

score 2 · Accepted Answer · answered Jul 04 '22 at 18:58

you can use it like this:

# Establish connection to MySQL database
import mysql.connector
  
db = mysql.connector.connect(
  host="localhost", # your host
  user="root", # your username
  password="root123", # your password
  database = "meow" # name of the database
  )
  

mycursor = db.cursor() # cursor() method is used to create a cursor object
  
query = "ALTER TABLE persons RENAME COLUMN PersonID TO Emp_Id;" # query to rename column
mycursor.execute(query) # execute the query
  
# close the Connection
db.close()

Rename a Column name in a specific Table in SQL from Python

Code

Issue

1 Answers1