Creating New Database 

Databases form the backbone of any application by securely storing and managing data. In this guide, we’ll explore how to create a new database in MySQL using Python. Along the way, we’ll look at listing existing databases and adding a new one to your database server.

Download New Real Time Projects :-Click here

Getting the List of Existing Databases

Before creating a new database, it’s helpful to view the databases already present on the MySQL server. This can be achieved using the SHOW DATABASES; SQL query. Below is the Python implementation for retrieving the list of existing databases:

Example: Viewing Existing Databases

import mysql.connector  
  
# Create the connection object  
myconn = mysql.connector.connect(host="localhost", user="root", passwd="google")  
  
# Creating the cursor object  
cur = myconn.cursor()  
  
try:  
    # Execute the query to fetch database names  
    cur.execute("SHOW DATABASES")  
except:  
    myconn.rollback()  
  
# Displaying the databases  
for x in cur:  
    print(x)  
  
# Closing the connection  
myconn.close()  

Output

('EmployeeDB',)  
('Test',)  
('TestDB',)  
('information_schema',)  
('javatpoint',)  
('mydb',)  
('mysql',)  
('performance_schema',)  
('testDB',)  

The SHOW DATABASES command lists all the databases currently present in the MySQL server.

Creating a New Database

To create a new database, you can use the CREATE DATABASE <database-name> SQL command. The process is straightforward, and the new database will appear in the list of databases once it’s created successfully.

Example: Creating a New Database

import mysql.connector  
  
# Create the connection object  
myconn = mysql.connector.connect(host="localhost", user="root", passwd="google")  
  
# Creating the cursor object  
cur = myconn.cursor()  
  
try:  
    # Creating a new database  
    cur.execute("CREATE DATABASE PythonDB2")  
  
    # Fetching the updated list of databases  
    cur.execute("SHOW DATABASES")  
except:  
    myconn.rollback()  
  
# Displaying the updated list of databases  
for x in cur:  
    print(x)  
  
# Closing the connection  
myconn.close()  

Output

('EmployeeDB',)  
('PythonDB2',)  
('Test',)  
('TestDB',)  
('information_schema',)  
('javatpoint',)  
('mydb',)  
('mysql',)  
('performance_schema',)  
('testDB',)  

Explanation

1. Connecting to MySQL Server:
   The mysql.connector.connect() function establishes a connection to the MySQL server. Ensure that the host, user, and password parameters match your MySQL configuration.

2. Creating a Cursor Object:
   A cursor object is created using the myconn.cursor() method. It allows you to execute SQL queries.

3. Executing SQL Queries:

   • The SHOW DATABASES query fetches the list of all databases.
   • The CREATE DATABASE PythonDB2 query creates a new database named PythonDB2.

4. Exception Handling:
   The try-except block ensures that the program gracefully handles errors, such as database creation failure.

5. Closing the Connection:
   Closing the connection with myconn.close() is a best practice to free resources.

Updated Notes

• Backward Compatibility: MySQL and Python are widely used together, and the provided examples are compatible with modern MySQL server versions and the mysql-connector-python library.
• Future-Proof Your Code: Regularly update your Python libraries to ensure compatibility with the latest features and security patches.