How to create Database in MySQL using Python

Shashank Shanu

4 years ago

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…

Related Blog

Top Discussion

Top Courses

Webinars

How to create Database in MySQL using Python

How to Create a Database in MySQL?

Create a Database in MySQL Using Python

Submit Review