How to create Database in MySQL using Python

Shashank Shanu

a year ago

Create Database In MySQL Using Python
Create Database In MySQL Using Python
Hello Folks,
Today, I am here to explain to you one of the most important and asked topics “How to work with MySQL using python”. Many times, while working on a big project you might come across a situation where you need to collect data from different data sources. One of the most used and famous databases MySQL. So, in this article, I will try to show you how we can connect and use MySQL in python and create a database.
So, without wasting much time, let’s start.

How to Create a Database in MySQL?

To create a database in MYSQL we can use CREATE DATABASE query.
Syntax:
Syntax of the CREATE DATABASE query −
CREATE DATABASE database_name
Example
Let’s create a database with the name new_db in MySQL:
CREATE DATABASE new_db;
To see the created database. We can use the SHOW DATABASES statement as shown below:
SHOW DATABASES;
Output:


+--------------------+
| Database           |
+--------------------+
| information_schema |
| logging            |
| mydatabase         |
| new_db               |
| performance_schema |
| students           |
| sys                |
+--------------------+
23 rows in set (0.13 sec)

Create a Database in MySQL Using Python

We have to establish a connection with MySQL and then to manipulate data in it we need to connect to a database. We may even connect to an existing database or, we can create a new database.
You would need special privileges to create or to delete a MySQL database. So, if you have access to the root user, you can create any database.
Example
Establishing connection with MYSQL and after that creating a database in it.
# import connector 
import mysql.connector

#establish connection
connect = mysql.connector.connect(user='root', password='password', host='127.0.0.1')

#Create cursor object using the cursor() method
connect_cursor = connect.cursor()

# Drop database MYDATABASE if already exists.
connect_cursor.execute("DROP database IF EXISTS My_New_Database")

# query to create a database
query = "CREATE database MY_New_DATABASE";

#Create database
connect_cursor.execute(query)

#Retrieving the list of databases
print("List of databases: ")
connect_cursor.execute("SHOW DATABASES")
print(connect_cursor.fetchall())

#Closing the connection
connect.close()
Output:
List of databases:
[
   ('information_schema',), 
   ('dbbug61332',), 
   ('details',), 
   ('exampledatabase',), 
   ('my_new_database',), 
   ('new_db',), 
   ('mysql',), 
   ('performance_schema',)
]
We can observe from the above result that our code gets successfully executed and my_new_database is created. That’s how simple it is to use MySQL using python and we can manipulate the data.
I hope after you enjoyed reading this article and finally, you came to know about How to create Database in MySQL using Python.
For more such blogs/courses on data science, machine learning, artificial intelligence and emerging new technologies do visit us at InsideAIML.
Thanks for reading…
Happy Programming…


Submit Review