Python Tutorial
Python MySQL - Create Database
Create a new database from Python and confirm it exists.
CREATE DATABASE
A MySQL server holds many databases, each a container for tables. Create one by executing a CREATE DATABASE statement through a cursor.
import mysql.connector
conn = mysql.connector.connect(
host="localhost", user="root", password="secret")
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydatabase")Use CREATE DATABASE IF NOT EXISTS mydatabase to avoid an error when the database already exists.
Check if It Exists
List all databases with SHOW DATABASES and look for yours.
cursor.execute("SHOW DATABASES")
databases = [row[0] for row in cursor.fetchall()]
print("mydatabase" in databases) # TrueConnect Directly to a Database
Once created, pass database= in the connection so every query runs against it without prefixing table names.
conn = mysql.connector.connect(
host="localhost",
user="root",
password="secret",
database="mydatabase", # select the database up front
)Alternatively switch databases on an open connection with cursor.execute("USE mydatabase").
Handle "Already Exists" Cleanly
from mysql.connector import Error
try:
cursor.execute("CREATE DATABASE mydatabase")
print("Created.")
except Error as e:
print("Skipped:", e.msg) # e.g. database exists (errno 1007)Character Set and Collation
For full Unicode support (including emoji), create the database with utf8mb4.
cursor.execute(
"CREATE DATABASE IF NOT EXISTS mydatabase "
"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
)Best Practices
- Use
IF NOT EXISTSto make creation idempotent. - Prefer
utf8mb4so all Unicode characters are stored correctly. - The connecting user needs the
CREATEprivilege. - Give databases clear, lowercase names to avoid case-sensitivity surprises across operating systems.
Try It Yourself
Exercise 1: Write a statement that creates shop only if it does not already exist, with full Unicode support.
Show solution
cursor.execute(
"CREATE DATABASE IF NOT EXISTS shop "
"CHARACTER SET utf8mb4")Exercise 2: Which SQL command lists all databases on the server?
Show solution
SHOW DATABASES.
📘 Real-World Deep Dive
Knowing <strong>MySQL Create Database (MySQL)</strong> well is what turns MySQL from a curiosity into a daily tool — you'll reach for it in nearly every real project.
Real-Life Scenario
An end-to-end usage of MySQL Create Database that you'd actually see in a data pipeline or analytics notebook.
Real-Life Example
import mysql.connector
conn = mysql.connector.connect(host="localhost", user="root", password="...")
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4")
print("ok")Expected Output
(see source)Common mistakes
- Reading with the default cursor returns tuples — switch to
DictCursorfor named access. - Strings without parameter binding produce SQL-injection holes — never use f-strings in queries.
- Connecting through a long-lived daemon pool: make sure
pool_reset_connectionis on to avoid session state leakage. - Treating MySQL Create Database as a black box without reading the docs — the API has subtle defaults that bite when you scale.
🚀 Performance & Best Practices
- Use prepared statements (
cursor.executemany) for bulk inserts. - Fetch with
cursor.fetchmany(1000)in streams instead offetchall(). - Use
pymysql's server-side cursors for very large result sets. - When working with MySQL, prefer vectorised / batched operations over Python loops.
🧪 Try It Yourself
- Reproduce the snippet on a representative slice of your own data.
- Profile the snippet with
cProfileortimeitand find the single biggest improvement. - Generalise the snippet into a small, reusable function you can drop into future projects.