Creating a database and table in MySQL with Python

To learn this lesson effectively, you will need to understand how to connect MySQL to Python. You can refer to the article How to connect MySQL database in Python to learn how to connect to MySQL in Python.

Please note that we are using MySQL integrated in XAMPP. To learn how to install XAMPP and run MySQL, please read the article Installing PHP Web Programming Environment with XAMPP.

1. Create a database in MySQL with Python

Once you have established a connection to MySQL in XAMPP, there are two methods for creating databases in MySQL:

Method 1. To create a database, use the phpMyAdmin tool. You can refer to the section Connect to a specific database in MySQL for instructions on how to create a database with phpMyAdmin.

Method 2. To create a database in MySQL, use Python to execute the command “CREATE DATABASE“.

import mysql.connector

# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=""
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE gochocit")
# disconnect from MySQL
mydb.close()

If the Python code executes without errors, the database creation is successful. If errors occur, the database creation is unsuccessful. For instance, the error below is an error when creating the database gochocit. This error occurs because the database gochocit already exists in MySQL, so it cannot be created again.

mysql.connector.errors.DatabaseError: 1007 (HY000): Can't create database 'gochocit'; database exists

To prevent this error, use the following SQL execution statement to verify and create a database if it does not already exist.

mycursor.execute("CREATE DATABASE IF NOT EXISTS gochocit")

To create a database with a specific character encoding (such as utf8_general_ci), modify the SQL execution statement as follows.

mycursor.execute("CREATE DATABASE gochocit CHARACTER SET utf8 COLLATE utf8_general_ci")

Note: The default character encoding in MySQL in XAMPP is utf8mb4_general_ci.

Once you’ve created a database in MySQL, you’ll need to connect to it to perform any operations or processing. For instructions on how to connect to a specific database in MySQL with Python, check out the section Connect to a specific database in MySQL.

2. Create a table in a database in MySQL

To create a table in a database, execute the SQL statement “CREATE TABLE” using Python. Remember that you must establish a connection to the MySQL database to create a table.

import mysql.connector

# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql_string = """CREATE TABLE IF NOT EXISTS gochocit_posts (
                ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                post_author VARCHAR(255),
                post_title TEXT,
                post_content TEXT,
                post_date DATETIME
                )
                """

mycursor.execute(sql_string)
# disconnect from MySQL
mydb.close()

In the example above, we create a table called gochocit_posts in the database gochocit. To check if the table exists, use the IF NOT EXISTS command. If it does not exist, create the table.