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
– wkl, Commented Jul 4, 2022 at 18:59
Can you please debug your exception and add the error output. See how to print the exception. — hc_dev
– hc_dev, Commented Jul 4, 2022 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
– wkl, Commented Jul 4, 2022 at 19:24

Amey · Accepted Answer · 2022-07-04 18:58:22Z

2

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

answered Jul 4, 2022 at 18:58

Amey

4804 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

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

Code

Issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Code

Issue

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related