Create Table - Python MySQL

Welcome to a tutorial on Python MySQL on how to create and list tables. Here you will learn how to create tables in any MySQL database, and how to check if a table already exists in any database in Python.

Python MySQL - Create Table

In a program, to store information in the MySQL database, we have to create the tables. Therefore, it is necessary to select our database first and then create a table inside the given database.

Check the example below. Also, at the time we created the connection, we can specify the name of your database.

import mysql.connector

db = mysql.connector.connect(
    host = "localhost",
    user = "yourusername",
    password = "yourpassword",
    database = "mydatabase1"
)

In the example above, the code is executed without any errors, meaning that you have successfully connected to the database name.

SQL Query to Create Table

Below is the general syntax to create a table in the selected database.

CREATE TABLE table_name;

Check out the example below, where we will create a table named students in the specified database with name mydatabase1 In the created table students we have these fields: name, rollno, branch, and address.

#for our convenience we will import mysql.connector as mysql
import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

cursor = db.cursor()

cursor.execute("CREATE TABLE students (name VARCHAR(255), rollno INTEGER(100), branch VARCHAR(255), address VARCHAR(255))")

When our code executes without any error, meaning the table has been created successfully. Also, in the case you want to check the existing tables in the database, the SHOW TABLES SQL is used.

List existing Tables in a Database

So, we will check if the table exists in our database in the example below.

#for our convenience we will import mysql.connector as mysql
import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database="mydatabase1"
)

cursor = db.cursor()
## getting all the tables which are present in 'datacamp' database
cursor.execute("SHOW TABLES")

tables = cursor.fetchall() ## it returns list of tables present in the database

## showing all the tables one by one
for table in tables:
    print(table)

Output:

('students',)

Python MySQL - Table with Primary Key

In the above session, we created a table with the name students in our database. In the database, we will store the student data and fetch it if required. However, while fetching data, we might find students with the same name and it can lead to wrong data getting fetched, or cause some confusion.

The Primary Key is to uniquely identify each record in a table.

What is Primary Key?

The primary key is typically an attribute to make a column or a set of columns accept unique values. Also, the helps one to find each row uniquely in the table.

Therefore, to identify each row uniquely with a number starting from 1, the syntax below is used.

INT AUTO_INCREMENT PRIMARY KEY

With the code above and using any column, one can make its value as auto-increment, meaning the database will automatically add an incremented value even if you do not insert any value for that column while inserting a new row of data into the table.

Add Primary Key during Table Creation

The example below shows how to add a primary key at the time of creating the table.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "mydatabase1"
)
cursor = db.cursor()

## creating the 'students' table with the 'PRIMARY KEY'

cursor.execute("CREATE TABLE students (name VARCHAR(255), rollno INTEGER(100) NOT NULL AUTO_INCREMENT PRIMARY KEY, branch VARCHAR(255), address VARCHAR(255))")

From the example above, the code ran without an error, meaning that you have successfully created a table named “students” with the column rollno as a primary key.

Python MySQL - Describe the Table

The code below describes any table to see what columns it has and all the meta-information about the table and all its columns.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "mydatabase1"
)

cursor = db.cursor()

cursor.execute("DESC students")

print(cursor.fetchall())

Output:

[('name', 'varchar(255)', 'YES', '', None, ''), ('rollno', 'int', 'NO', 'PRI', None, 'auto_increment'), ('branch', 'varchar(255)', 'YES', '', None, ''), ('address', 'varchar(255)', 'YES', '', None, '')]

Add Primary Key to Existing Table

From the example above, we assume that rollno column does not exist in the student table. As such we are going to add a column to be used as the primary key in an existing table in the example.

Check out the example here.

import mysql.connector as mysql

db = mysql.connect(
     host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "mydatabase1"
)

cursor = db.cursor()

## We are going to add rollno field with primary key in table students 

cursor.execute("ALTER TABLE students ADD COLUMN rollno INT AUTO_INCREMENT PRIMARY KEY")

print(cursor.fetchall())

Output:

[('name', 'varchar(255)', 'YES', '', None, ''), ('branch', 'varchar(255)', 'YES', '', None, ''), ('address', 'varchar(255)', 'YES', '', None, ''), ('rollno', 'int', 'NO', 'PRI', None, 'auto_increment')]