How to Add a Column to a MySQL TAble In Python

In this article, we show how to add a column to a MySQL table in Python.

We show how to add a column at any point of the table, including at the beginning, the end, or after any specific column on the table.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

How to Add a Column to the End of a MySQL Table

So, the code to add a column to the end of a MySQL table in Python is shown below.

cursor.execute("ALTER TABLE table_name ADD email VARCHAR(100) NOT NULL")

The full code to add a column to the end of a MySQL in Python is shown below.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("ALTER TABLE table_name ADD email VARCHAR(100) NOT NULL") db.close()

So, this is all the code that is needed to add a column to the end of a MySQL table in Python.

So, the first thing we have to do is import the MySQLdb.

Once we have MySQLdb imported, then we create a variable named db. We set db equal to the MySQLdb.connect() function. This function will allow us to connect to a database. In side of this MySQLdb.connect() function are 4 parameters. These are, MySQLdb.connect("hostname", "username", "password", "database_name"). This line establishes connection with the database that you want.

We then create a variable named cursor, which we set equal to db.cursor(). This establishes a cursor for the database.

Next, we execute our function to add a column to the table using the cursor.execute() function. Inside of this function, we place in the line, "ALTER TABLE table_name ADD email VARCHAR(100) NOT NULL". So the whole line of code, is, cursor.execute("ALTER TABLE table_name ADD email VARCHAR(100) NOT NULL").

If the function executes and the column is added to the table, a '0' will be output. This alerts you that the column has been successfully added to the table.

How to Add a Column To the Beginning of a Table

The code to add a column to the beginning of a MySQL table in Python is shown below.

cursor.execute("ALTER TABLE Table_name ADD ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST")

We won't go through the entire code again, but this the cursor.execute() line is the only line that changes.

The code, above, will add the column, ID, to the table, Table_name. The ID column is an int of length 11, not null, auto increments (increments by 1 with each new entry), and is the primary key for the table.

The keyword FIRST is what places the new added column at the beginning.

How to Add a Column After a Specific Column of the Table

The code to add a column after a specific column in a table in shown below.

cursor.execute("ALTER TABLE Table_name ADD email VARCHAR(50) NOT NULL AFTER lastname;")

So, the code above adds a column, named email, of type of VARCHAR of length 50 that is not null after the column, lastname.

The keyword, AFTER, followed by the column name puts the new column after that specified column.

So, this is how you can add a column to MySQL table in Python, at any place in the table.

Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python