In Python, the “CREATE TABLE” statement is used to create a MySQL table.

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="myusername",
    password="mypassword",
    database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

Output:

#The table "customers" is created in the DB

When creating a table, a column with a unique key for each record should also be created. This is done by defining a PRIMARY KEY.

The statement “INT AUTO_INCREMENT PRIMARY KEY” is then used to insert a unique number for each record, starting at 1, and increased by one for each record.

import mysql.connector

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

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

The ALTER TABLE keyword is used to create a primary key for an existing table.

import mysql.connector

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

mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Output:

#The primary key "id" is added to the "customers" table

The “SHOW TABLES” statement will list all the existing tables in a MySQL database.

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:

#Will list all existing tables

---