Python MySQL Create Database
In this page:
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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydatabase")
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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")
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
from unittest.mock import MagicMock
connect = MagicMock(return_value=MagicMock())
conn = connect(host="localhost", user="root", password="", database="mydatabase")
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
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
print("Database created with full Unicode support")- 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.
- 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.
- 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.
- 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.
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:
- Python MySQL Get Started
- Python MySQL Create Database
- Python MySQL Create Table
- Python MySQL Insert Into Table
- Python MySQL Select From Table
- Python MySQL Select With a Filter
- Python MySQL Order By
- Python MySQL Delete Record
- Python MySQL Drop Table
- Python MySQL Update Table
- Python MySQL Limit
- Python MySQL Join