MySQL Create Database

This chapter explains how to create a MySQL database from Python using the mysql-connector-python library. You will learn how to connect to a MySQL server, run the CREATE DATABASE statement, list available databases, and select a database for subsequent queries.

If you have not installed the connector yet, see MySQL Get Started first. Once your database exists, the next step is creating tables.

Installing the MySQL Connector

Install the official MySQL driver for Python with pip:

pip install mysql-connector-python

This package works with Python 3.6 and later and does not require a separate C extension.

Connecting to the MySQL Server

Before creating a database you must open a connection to the MySQL server. Provide the host address, a user account, and that account's password. Notice that you do not specify a database at this stage — the database does not exist yet.

import mysql.connector

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

print(mydb)   # <mysql.connector.connection.MySQLConnection object at 0x...>
mydb.close()

If the connection details are wrong, mysql.connector.connect() raises mysql.connector.errors.InterfaceError. Wrap the call in a try/except block in production code.

Creating a Database

Once connected, obtain a cursor and execute the CREATE DATABASE SQL statement. The cursor is the object through which all SQL commands are sent.

import mysql.connector

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

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

mycursor.close()
mydb.close()

If mydatabase already exists the server raises an error. Add IF NOT EXISTS to make the statement idempotent — it succeeds whether the database is new or already present:

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

Database naming rules

• Names may contain letters, digits, and underscores (_).
• Names are case-insensitive on Windows and case-sensitive on most Linux setups.
• The maximum length is 64 characters.
• Avoid reserved words such as database, table, or select as database names; if you must use them, quote the name with backticks in SQL.

Setting a character set

By default MySQL uses the server's default character set (usually utf8mb4 on modern installs). You can specify the character set and collation explicitly to guarantee consistent behaviour across different servers:

mycursor.execute(
    "CREATE DATABASE IF NOT EXISTS mydatabase "
    "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
)

utf8mb4 stores the full Unicode range (including emoji). utf8mb4_unicode_ci is a case-insensitive, accent-insensitive collation suitable for most web applications.

Listing Existing Databases

After creating the database you can verify it exists by executing SHOW DATABASES, which returns one row per database:

import mysql.connector

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

mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")

for (db_name,) in mycursor:
    print(db_name)

mycursor.close()
mydb.close()

Each row returned by SHOW DATABASES is a one-element tuple, so the unpacking (db_name,) extracts the string directly. Typical output looks like:

information_schema
mydatabase
mysql
performance_schema
sys

To check only whether your specific database exists you can filter in Python:

mycursor.execute("SHOW DATABASES")
databases = [row[0] for row in mycursor]

if "mydatabase" in databases:
    print("Database found.")
else:
    print("Database not found.")

Selecting a Database

Once a database exists, pass its name as the database argument when you open the connection — this is the normal pattern for all subsequent work:

import mysql.connector

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

mycursor = mydb.cursor()
# All queries now run against mydatabase
mycursor.close()
mydb.close()

You can also switch the active database on an existing connection without closing it:

mycursor.execute("USE mydatabase")

This is handy when you need to move between multiple databases in the same script, but passing database= to connect() is clearer for most use cases.

Handling Errors

Always handle potential database errors so your application fails gracefully instead of crashing with an unhandled exception:

import mysql.connector
from mysql.connector import Error

mydb = None
try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword"
    )
    mycursor = mydb.cursor()
    mycursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")
    print("Database created (or already exists).")
except Error as e:
    print(f"Error: {e}")
finally:
    if mydb is not None and mydb.is_connected():
        mycursor.close()
        mydb.close()

The finally block ensures the connection is closed even when an exception is raised, preventing connection leaks.

Full Working Example

The snippet below combines all the steps: create the database, verify it appears in SHOW DATABASES, then re-connect with the database selected:

import mysql.connector
from mysql.connector import Error

DB_NAME = "mydatabase"

def create_database():
    try:
        conn = mysql.connector.connect(
            host="localhost",
            user="yourusername",
            password="yourpassword"
        )
        cursor = conn.cursor()

        # Create the database if it does not exist
        cursor.execute(
            f"CREATE DATABASE IF NOT EXISTS {DB_NAME} "
            "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
        )
        print(f"Database '{DB_NAME}' created (or already exists).")

        # Confirm it appears in the database list
        cursor.execute("SHOW DATABASES")
        databases = [row[0] for row in cursor]
        if DB_NAME in databases:
            print(f"Confirmed: '{DB_NAME}' is listed on the server.")

        cursor.close()
        conn.close()

        # Re-connect with the database selected
        conn = mysql.connector.connect(
            host="localhost",
            user="yourusername",
            password="yourpassword",
            database=DB_NAME
        )
        print(f"Connected to '{DB_NAME}' successfully.")
        conn.close()

    except Error as e:
        print(f"Error: {e}")

create_database()

Expected output when run against a fresh MySQL server:

Database 'mydatabase' created (or already exists).
Confirmed: 'mydatabase' is listed on the server.
Connected to 'mydatabase' successfully.

Next Steps

• MySQL Create Table — define tables inside the database you just created.
• MySQL Insert — insert rows into your tables.
• MySQL Select — query data with SELECT.