← Back to Python Course | Chapter 15: Python & MySQL | Lesson 2 of 12

Python MySQL Create Database

A database itself -- the top-level container that holds all your tables -- can be created programmatically from Python, not just through a database admin tool. Running a CREATE DATABASE SQL statement through a cursor object, once you have a connection to the MySQL server, does exactly this.

In this page:

  1. Creating a Cursor
  2. Creating a Database
  3. Avoiding Errors with IF NOT EXISTS
  4. Connecting Directly to a Specific Database
  5. Choosing Character Set and Collation
Syntax
python 
cursor = connection.cursor()
cursor.execute("CREATE DATABASE database_name")

Creating a Cursor

A cursor, obtained with conn.cursor(), is the object used to actually execute SQL statements and retrieve results -- the connection object represents the link to the database server, while the cursor is what sends commands through that link.

Note: Create one cursor per connection for straightforward scripts -- you generally do not need more than one unless running genuinely concurrent operations.
Warning: Attempting to execute a query directly on the connection object (rather than a cursor obtained from it) is a common beginner mistake -- the connection itself has no .execute() method.

Example: Creating a Cursor

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()  # cursor is what actually sends SQL commands
print(type(cursor))

Creating a Database

cursor.execute("CREATE DATABASE dbName") sends the CREATE DATABASE SQL command through the cursor -- the connection used for this does not need to specify a particular database beforehand, since CREATE DATABASE operates at the server level, not within an existing database.

Note: Connect without naming a specific database (omit the database argument) when your script's job is specifically to create one that does not exist yet.
Warning: Creating a database requires the connecting MySQL user to have CREATE privileges -- a restricted application-level user often will not have this and needs an admin account for setup scripts.

Example: Creating a Database

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydatabase")  # runs at the server level, no database needs to be selected first
print("Database created")

Avoiding Errors with IF NOT EXISTS

CREATE DATABASE IF NOT EXISTS dbName makes the statement safe to run more than once -- instead of raising an error when the database already exists, MySQL simply does nothing and lets the script continue, which is useful for setup scripts that might run multiple times.

Note: Use IF NOT EXISTS in any setup script meant to be safely re-runnable, like an installer or a first-run initialization routine.
Warning: IF NOT EXISTS silently does nothing if the database already exists -- it will not warn you if an existing database has a different structure than you expect.

Example: Avoiding Errors with IF NOT EXISTS

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")  # does nothing instead of erroring if it already exists
print("Safe to run more than once")

Connecting Directly to a Specific Database

Once a database exists, subsequent connections can name it directly via the database argument in mysql.connector.connect(), which is simpler than creating a connection and then separately switching databases with a USE statement.

Note: For scripts that only ever work with one specific, already-created database, connect directly to it from the start rather than connecting generically and switching afterward.
Warning: Connecting with a database argument that does not exist yet raises an error immediately -- the database must already have been created first.

Example: Connecting Directly to a Specific Database

python 
from unittest.mock import MagicMock

connect = MagicMock(return_value=MagicMock())
conn = connect(host="localhost", user="root", password="", database="mydatabase")  # database named directly
print("Connected directly to mydatabase")

Choosing Character Set and Collation

CREATE DATABASE dbName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci sets the default character encoding and sorting rules for the database at creation time -- utf8mb4 is the modern recommended choice since it supports the full range of Unicode, including emoji.

Note: Always set utf8mb4 explicitly at database creation time; the older utf8 charset in MySQL is actually a restricted subset that cannot store every Unicode character.
Warning: Tables created later can override the database's default character set individually, so a database-level setting is a sensible default, not an absolute guarantee for every table.

Example: Choosing Character Set and Collation

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")  # utf8mb4 supports full Unicode, including emoji
print("Database created with full Unicode support")
Related Topics

Python MySQL Create Table · Python MySQL Get Started · Python MySQL Insert Into Table · Python MySQL Select From Table

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Trying to create a database with the same name as one that already exists, which raises an error unless you specifically check with IF NOT EXISTS in the SQL.
  2. Running CREATE DATABASE with a connection that does not have sufficient privileges, since it requires elevated database-creation rights the everyday application user often does not have.
  3. Forgetting that creating a database does not automatically select it for the current connection -- you still need a separate USE statement or a fresh connection specifying that database.
Chapter Summary
  • conn.cursor() creates a cursor object, used to execute SQL statements against the database.
  • cursor.execute("CREATE DATABASE dbName") sends that SQL command to create a new, empty database.
  • IF NOT EXISTS makes the CREATE DATABASE statement safe to re-run without erroring if the database already exists.
Browser Support

CREATE DATABASE is standard SQL, executed identically through mysql-connector-python across every supported Python and MySQL version.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Get Started Next Lesson → Python MySQL Create Table

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.