Creating New Databases in MySQL Using Python
Share this content:
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
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:
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()
('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.
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.
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()
('EmployeeDB',)
('PythonDB2',)
('Test',)
('TestDB',)
('information_schema',)
('javatpoint',)
('mydb',)
('mysql',)
('performance_schema',)
('testDB',)
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.
Creating a Cursor Object:
A cursor object is created using the myconn.cursor()
method. It allows you to execute SQL queries.
Executing SQL Queries:
SHOW DATABASES
query fetches the list of all databases.CREATE DATABASE PythonDB2
query creates a new database named PythonDB2
.Exception Handling:
The try-except
block ensures that the program gracefully handles errors, such as database creation failure.
Closing the Connection:
Closing the connection with myconn.close()
is a best practice to free resources.
mysql-connector-python
library.
Post Comment