Python MySQL: Create Database

Introduction

Creating a database is one of the fundamental tasks when working with MySQL. Python, with its extensive library support, makes it easy to interact with MySQL databases. In this guide, we will use the mysql-connector-python library to create a new database in MySQL.

Setting Up

Install MySQL Connector

First, you need to install the MySQL connector for Python. You can install it using pip:

pip install mysql-connector-python

Connecting to MySQL

To create a database, you need to connect to the MySQL server. You will need the following details:

Hostname (usually localhost)
Username
Password

Example: Connecting to MySQL

import mysql.connector

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

if connection.is_connected():
    print("Connected to MySQL server")

# Close the connection
connection.close()

Creating a Database

Once you have connected to the MySQL server, you can create a new database by executing an SQL statement using a cursor object.

Example: Creating a Database

import mysql.connector

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

# Create a cursor object
cursor = connection.cursor()

# Create a new database
cursor.execute("CREATE DATABASE mydatabase")

print("Database created successfully")

# Close the connection
connection.close()

Checking if Database Exists

Before creating a database, it is a good practice to check if it already exists to avoid errors.

Example: Check if Database Exists

import mysql.connector

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

# Create a cursor object
cursor = connection.cursor()

# Check if database exists
cursor.execute("SHOW DATABASES")

databases = cursor.fetchall()

database_name = "mydatabase"

if (database_name,) in databases:
    print(f"Database '{database_name}' already exists.")
else:
    cursor.execute(f"CREATE DATABASE {database_name}")
    print(f"Database '{database_name}' created successfully.")

# Close the connection
connection.close()

Using the Created Database

Once the database is created, you can use it to create tables, insert data, and perform other database operations.

Example: Using the Created Database

import mysql.connector

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

# Create a cursor object
cursor = connection.cursor()

# Create a new table
create_table_query = """
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('Male', 'Female') NOT NULL,
    position VARCHAR(255)
)
"""
cursor.execute(create_table_query)

print("Table created successfully")

# Close the connection
connection.close()

Complete Example

Here is a complete example that includes connecting to the MySQL server, checking if a database exists, creating a database if it does not exist, and creating a table within the database.

import mysql.connector

# Database connection details
host = "localhost"
user = "your_username"
password = "your_password"
database_name = "mydatabase"

# Connect to the MySQL server
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password
)

# Create a cursor object
cursor = connection.cursor()

# Check if database exists
cursor.execute("SHOW DATABASES")
databases = cursor.fetchall()

if (database_name,) in databases:
    print(f"Database '{database_name}' already exists.")
else:
    cursor.execute(f"CREATE DATABASE {database_name}")
    print(f"Database '{database_name}' created successfully.")

# Connect to the newly created database
connection.database = database_name

# Create a new table
create_table_query = """
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('Male', 'Female') NOT NULL,
    position VARCHAR(255)
)
"""
cursor.execute(create_table_query)

print("Table created successfully")

# Close the connection
connection.close()

Conclusion

Creating a MySQL database using Python is straightforward with the mysql-connector-python library. By following the steps outlined above, you can easily connect to a MySQL server, check for the existence of a database, create a new database, and set up tables within it. This provides a solid foundation for building and managing your databases programmatically using Python.