How to Create Table in MySql using Python?

Table is a logical structure which is used to store data into row and column format. MySQL database use table to save data and a database can have any number of tables.

In this tutorial, we will create a table into MySQL by using Python script. Since MySQL understands SQL queries, so we will use Python to execute the SQL query.

To create a table in MySql, following SQL query is used.

SQL Command To Create a Table

CREATE TABLE EMPLOYEE(
   EMP_ID VARCHAR(10),
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   JOB_PROFILE VARCHAR(20)

Python Script to Create MySQL Table

Now we will execute the above SQL command using Python script. In the below example, first we established a connection between Python and MySQL then created the table.

import mysql.connector
from mysql.connector import errorcode

try:
  
  # Connecting with MySql
  con = mysql.connector.connect(user='root', password='mysqlpassword', host='127.0.0.1', database='company')
	
  # Using cursor() method to create cursor object
  cursor = con.cursor()

  # Drop table EMPLOYEE if already exists
  cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

  # Creating Sql query to create table
  sql_query ='''CREATE TABLE EMPLOYEE(
   EMP_ID VARCHAR(10),
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   JOB_PROFILE VARCHAR(20)
  )'''
  cursor.execute(sql_query)
  print("Table created successfully")

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Your user name or password is incorrect")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  con.close()

Output:

Table created successfully

Verify the Table

Now, We can verify the created table by login into MySQL database. Use the below SQL command to fetch tables from database company which we created in previous tutorial.

show tables from company;

See, it shows that the EMPLOYEE table is created succeessfuly.

Conclusion

Well, in this topic, we learnt about creating a table into MySql using Python script by successfuly connected to that database. 

In our next topic, we will learn to insert data into the table. Till then keep learning and practice.