Create Database - Python MySQL

Welcome to another tutorial on MySQL in Python. Here you will learn how to create MySQL Database, and how to check if any database is present in MySQL already by using Python.

In other to create the database in MySQL the "CREATE DATABASE" statement is used.

Python MySQL - CREATE DATABASE

Follow up with the example below to see the basic syntax of the create database statement.

CREATE DATABASE database_name

Python MySQL - Create Database Example

Check out the example below to see how it is done.

#for our convenience we will import mysql.connector as mysql
import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword"
)

## Now Let us create an instance of 'cursor' class which is used to execute the 'SQL' statements in 'Python'

cursor = db.cursor()

## creating a database called 'studytonight'
## 'execute()' method is used to compile a 'SQL' statement
## below statement is used to create tha 'studytonight' database

cursor.execute("CREATE DATABASE mydatabase1")

In the above example, if the named database already exists, then we will get an error.

In this case, if there is no error response, it indicates that the database is created successfully.

Python MySQL - List all Database

By using Python, we can list all the databases in MySQL, and in that list we can also check if any particular Database is available or not.

The SHOW DATABASES SQL statement is used to check if any database already exists.

Check out the example below:

#for our convenience we will import mysql.connector as mysql
import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword"
)

cursor = db.cursor()

cursor.execute("SHOW DATABASES")

for x in cursor:
  print(x)

Output:

('information_schema',)
('mysql',)
('performance_schema',)
('mydatabase1',)
('sys',)

From the above example, we used Python for loop to iterate over the cursor object and print the names of all the databases found. Also, we can compare the name of the databases to check if any particular database is present, before creating it by using Python if condition.