
Python MySQL Tutorial – How to Create a Database Using Python
Last updated 2 weeks, 1 day ago | 55 views 75 5

When building a data-driven application, the first step is often creating a database. Python, paired with MySQL, makes this process smooth and efficient. In this tutorial, you’ll learn how to create a MySQL database programmatically using Python and the mysql-connector-python
package.
Table of Contents
-
What You Need
-
Install MySQL Connector
-
Connect to MySQL Server
-
Create a MySQL Database
-
Check if a Database Exists
-
Handle Errors Gracefully
-
Full Working Example
-
Tips and Common Pitfalls
✅ What You Need
Before you begin, ensure the following:
-
Python 3 is installed
-
MySQL server is installed and running
-
You have MySQL user credentials
-
You’ve installed the MySQL Connector for Python (we’ll cover that next)
Step 1: Install MySQL Connector
Use pip to install the mysql-connector-python
library:
pip install mysql-connector-python
This library allows Python to communicate with MySQL databases.
Step 2: Connect to MySQL Server
Before creating a database, you need to connect to the MySQL server:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password"
)
print("Connected to MySQL server!")
Replace
"your_username"
and"your_password"
with your actual MySQL credentials.
Step 3: Create a New Database
Once connected, you can create a new database using a SQL CREATE DATABASE
statement:
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mytestdb")
print("Database created successfully.")
Step 4: Check If Database Exists
You can list all databases and check if one already exists:
mycursor.execute("SHOW DATABASES")
for db in mycursor:
print(db)
✅ It's a good idea to check before creating a database to avoid errors.
Step 5: Handle Errors Gracefully
Wrap your code in a try-except block to catch connection or execution errors:
import mysql.connector
from mysql.connector import Error
try:
mydb = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password"
)
if mydb.is_connected():
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mytestdb")
print("Database created successfully.")
except Error as e:
print("Error:", e)
finally:
if mydb.is_connected():
mydb.close()
print("Connection closed.")
Complete Working Example
import mysql.connector
from mysql.connector import Error
def create_database(host, user, password, db_name):
try:
# Connect to MySQL server
connection = mysql.connector.connect(
host=host,
user=user,
password=password
)
# Create cursor and database
cursor = connection.cursor()
cursor.execute(f"CREATE DATABASE {db_name}")
print(f"Database '{db_name}' created successfully.")
except Error as err:
print("Error:", err)
finally:
if connection.is_connected():
connection.close()
print("MySQL connection is closed.")
# Run function
create_database("localhost", "root", "your_password", "mytestdb")
⚠️ Common Pitfalls
Pitfall | Solution |
---|---|
Access denied for user |
Ensure username/password is correct |
Can't connect to MySQL server |
Make sure MySQL server is running |
Database already exists | Use SHOW DATABASES before creation |
Not closing connection | Use finally block or with statement |
Tips
-
✅ Use parameterized queries when working with user input
-
✅ Always close your database connections
-
✅ Use a try-except block to catch connection or SQL errors
-
✅ Use lowercase, underscore-separated names for database naming consistency
Summary
Action | Code |
---|---|
Install connector | pip install mysql-connector-python |
Connect to MySQL | mysql.connector.connect() |
Create database | cursor.execute("CREATE DATABASE mydb") |
Show databases | cursor.execute("SHOW DATABASES") |
Handle errors | Use try-except block |
Final Thoughts
Creating a MySQL database using Python is straightforward, thanks to the mysql-connector-python
package. This foundational step allows you to build powerful applications that interact with structured data stored in MySQL. With proper error handling and clean connection management, your code will be more reliable and scalable.
Tips and Tricks
What is pass in Python?
Python | Pass Statement
The pass statement is used as a placeholder for future code. It represents a null operation in Python. It is generally used for the purpose of filling up empty blocks of code which may execute during runtime but has yet to be written.
def myfunction():
pass
How can you generate random numbers?
Python | Generate random numbers
Python provides a module called random using which we can generate random numbers. e.g: print(random.random())
We have to import a random module and call the random() method as shown below:
import random
print(random.random())
The random() method generates float values lying between 0 and 1 randomly.
To generate customized random numbers between specified ranges, we can use the randrange() method
Syntax: randrange(beginning, end, step)
import random
print(random.randrange(5,100,2))
What is lambda in Python?
Python | Lambda function
A lambda function is a small anonymous function. This function can have any number of parameters but, can have just one statement.
Syntex:
lambda arguments : expression
a = lambda x,y : x+y
print(a(5, 6))
It also provides a nice way to write closures. With that power, you can do things like this.
def adder(x):
return lambda y: x + y
add5 = adder(5)
add5(1) #6
As you can see from the snippet of Python, the function adder takes in an argument x and returns an anonymous function, or lambda, that takes another argument y. That anonymous function allows you to create functions from functions. This is a simple example, but it should convey the power lambdas and closures have.
What is swapcase() function in the Python?
Python | swapcase() Function
It is a string's function that converts all uppercase characters into lowercase and vice versa. It automatically ignores all the non-alphabetic characters.
string = "IT IS IN LOWERCASE."
print(string.swapcase())
How to remove whitespaces from a string in Python?
Python | strip() Function | Remove whitespaces from a string
To remove the whitespaces and trailing spaces from the string, Python provides a strip([str]) built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns the original string.
string = " Python "
print(string.strip())
What is the usage of enumerate() function in Python?
Python | enumerate() Function
The enumerate() function is used to iterate through the sequence and retrieve the index position and its corresponding value at the same time.
lst = ["A","B","C"]
print (list(enumerate(lst)))
#[(0, 'A'), (1, 'B'), (2, 'C')]
Can you explain the filter(), map(), and reduce() functions?
Python | filter(), map(), and reduce() Functions
- filter() function accepts two arguments, a function and an iterable, where each element of the iterable is filtered through the function to test if the item is accepted or not.
>>> set(filter(lambda x:x>4, range(7))) # {5, 6}
-
map() function calls the specified function for each item of an iterable and returns a list of result
>>> set(map(lambda x:x**3, range(7))) # {0, 1, 64, 8, 216, 27, 125}
-
reduce() function reduces a sequence pair-wise, repeatedly until we arrive at a single value..
>>> reduce(lambda x,y:y-x, [1,2,3,4,5]) # 3
Let’s understand this:
2-1=1
3-1=2
4-2=2
5-2=3Hence, 3.
What is a namedtuple?
Python | namedtuple
A namedtuple will let us access a tuple’s elements using a name/label. We use the function namedtuple() for this, and import it from collections.
>>> from collections import namedtuple
#format
>>> result=namedtuple('result','Physics Chemistry Maths')
#declaring the tuple
>>> Chris=result(Physics=86,Chemistry=92,Maths=80)
>>> Chris.Chemistry
# 92
Write a code to add the values of same keys in two different dictionaries and return a new dictionary.
We can use the Counter method from the collections module
from collections import Counter
dict1 = {'a': 5, 'b': 3, 'c': 2}
dict2 = {'a': 2, 'b': 4, 'c': 3}
new_dict = Counter(dict1) + Counter(dict2)
print(new_dict)
# Print: Counter({'a': 7, 'b': 7, 'c': 5})
Python In-place swapping of two numbers
Python | In-place swapping of two numbers
>>> a, b = 10, 20
>>> print(a, b)
10 20
>>> a, b = b, a
>>> print(a, b)
20 10
Reversing a String in Python
Python | Reversing a String
>>> x = 'PythonWorld'
>>> print(x[: : -1])
dlroWnohtyP
Python join all items of a list to convert into a single string
Python | Join all items of a list to convert into a single string
>>> x = ["Python", "Online", "Training"]
>>> print(" ".join(x))
Python Online Training
python return multiple values from functions
Python | Return multiple values from functions
>>> def A():
return 2, 3, 4
>>> a, b, c = A()
>>> print(a, b, c)
2 3 4
Python Print String N times
Python | Print String N times
>>> s = 'Python'
>>> n = 5
>>> print(s * n)
PythonPythonPythonPythonPython
Python check the memory usage of an object
Python | Check the memory usage of an object
>>> import sys
>>> x = 100
>>> print(sys.getsizeof(x))
28