Python Tutorials · Python MySQL

MySQL Create Table

Learn all about MySQL Create Table in this comprehensive tutorial.

5 min read advanced

MySQL Create Database

MySQL Insert

•To create a table in MySQL, use the "CREATE TABLE" statement.
•You can check if a table exist by listing all tables in your database with the "SHOW TABLES" statement:
•When creating a table, you should also create a column with a unique key for each record.

Creating a Table

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

Make sure you define the name of the database when you create the connection

python

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

If the above code was executed with no errors, you have now successfully created a table.

Check if Table Exists

You can check if a table exist by listing all tables in your database with the "SHOW TABLES" statement:

python

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

Primary Key

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

This can be done by defining a PRIMARY KEY.

We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.

python

import mysql.connectormydb = 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))")

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

python

import mysql.connectormydb = 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")

Module quiz

2 questions

Which of the following is true about MySQL Create Table?

It is strictly typedIt is evaluated at runtimeIt requires explicit memory managementIt is deprecated

What is the most common pitfall when working with MySQL Create Table?

SyntaxErrorIndentationErrorNameErrorTypeError

Answer all questions to submit.

MySQL Create Database

MySQL Insert

MySQL Create Table

AI Summary

Creating a Table

Check if Table Exists

Primary Key

Module quiz