Python MySQL Create Table

Use the statement "CREATE TABLE" to create a MySQL table.

When you create your connection, make sure that you set the name of the database

We'll name the table "books" and give it three columns : "id", "name", and "price" :

CREATE TABLE books (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
price FLOAT(5, 2)
)

Notes on the table above :

You can choose additional optional attributes for each column after data type like AUTO INCREMENT, PRIMARY KEY, DEFAULT, NOT NULL and etc.

A main key column should be present in every table like above table (in this case: the "id" column). For each record in the table, its value must be different.

Example :- Create a table named "books" :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
qry = """CREATE TABLE books (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
price FLOAT(5, 2)
)"""
mycursor.execute(qry)
print("Table created successfully!")

Output :-

If you did not perform the preceding code without errors, you now built a table successfully.

The "SHOW TABLES" statement allows you to check if a table exists by listing all tables of your database.

Example :- List all tables from "mydatabase" :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
     print(x)

Output :-

When constructing a table, a column with a single key for each entry should also be created.

This can be achieved when a PRIMARY KEY is defined.

We have used the statement "AUTO_INCREMENT PRIMARY KEY" for id column, which inserts for each entry a unique number. Starting at 1, each record was incremented by one.

Use the ALTER TABLE Keyword if it already exists.

The "DROP TABLE" statement lets you remove an existing table from mysql database.

Example :- Delete the table "books" :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE books"
mycursor.execute(sql)
print("Table deleted!")

Output :-

You can use the IF EXISTS keyword to avoid an error, if the table you want to delete is previously deleted or no other reason exists.

Example :- When it exists, remove the table "books" :

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS books"
mycursor.execute(sql)

Output :-