Python Create Table

Create table

To create a table in MySQL, use the "CREATE TABLE" statement.

Make sure to define the database name when creating the connection.

Example

Create table "customers":

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

Run Instance

If there are no errors when executing the above code, then you have successfully created a table now.

Check if the table exists

You can list all tables in the database using the "SHOW TABLES" statement to check if a table exists:

Example

Returns a list of databases in the system:

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

Run Instance

Primary Key

When creating a table, you should also create a column with a unique key for each record.

This can be achieved by defining a PRIMARY KEY.

We use the statement "INT AUTO_INCREMENT PRIMARY KEY", which will insert a unique number for each record. Starting from 1, each record increments by 1.

Example

Create a primary key when creating a table:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(255), address VARCHAR(255))")

Run Instance

If the table already exists, please use the ALTER TABLE keyword:

Example

Create a primary key on an existing table:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Run Instance