Creating new Database using Python MySQL

In this article, we are going to discuss how to create a new database by connecting Python and MySQL.

What is a Database?

The places or memory used to secure highly and store the data which can be retrieved or modified later. All the databases are managed by particular systems known as Database Management systems in which one of the languages is MySQL.

MySQL is a Structured Query language, whereas Python is a Programming language. We are supposed to merge these two different languages in order to create a new database using Python and MySQL. In Java, we have inbuilt drivers, which by default, form a bridge between the Programming language ( java ) and the Structured Query language. In python, we need to import the required drivers that can support MySQL and Python.

Let us understand the concept of creating a database using Python and MySQL by following the steps mentioned below.

Step-by-step procedure for creating a Database using Python and MySQL:

1. Install MySQL
2. Connect Python to MySQL server using the required database notations
3. Create a database
4. Close the connectivity to the MySQL server

Install MySQL

In order to initiate access for Python to the database, we need to install MySQL so that it forms a connective bridge between Python and the databases.

We import all the characteristics of the MySQL server using the " MySQL connector " driver. The driver " MySQL connector " is used to grant permissions for python to access the database. Install the " MySQL connector " driver and import it to python.

Syntax to import MySQL through “ MySQL connector “ driver:

# import the driver “ MySQL connector “ initially
import mysql.connector

The above line imports all the members of MySQL into Python. Having zero errors is an indication of successful installation and import of MySQL.

Connect Python to MySQL server using the required database notations

As soon as the installation and importing of MySQL is completed, we are supposed to connect the system and MySQL databases by giving all the information of the admin user, including the password.

The syntax for arranging a connection between the system and MySQL databases using Python:

# import the driver “ MySQL connector “ initially
import mysql.connector


# the database “ data_base “ is being used to create a connection within the current user and MySQL server and then waiting for the required permissions from the admin user
data_base = mysql.connector.connect(
    user = " name of the current admin user "
    password = " password of the current admin user "
    host = " localhost "
)
# printing the database connectivity with python
print(data_base)

This program prints the credentials created using the " data_base ". The connection between the user and the server is made after the above step. Hereafter, you can specify the queries using a few methods. Those methods shall be discussed below.

Query to create a new database:

CREATE DATABASE name_of_new_database;

The syntax to create a new Database:

# syntax for creating a new database 
database_query = " CREATE DATABASE name_of_new_database " 

This query and line of code are used to create a new database. But, in Python, queries are not considered to be actual queries. They need to be converted into a string, and after that, they are reconsidered to be SQL queries. To handle this, we need to use the " cursor() “ and “ execute() “ methods.

The cursor() method is used to gain access to the required existing database or newly created databases. After the specific database is mentioned, the cursor() method grants access to that particular database. Let us understand the cursor() method in detail.

Cursor method [ cursor() ]:

The method “ cursor() “ supposes to be an access giver or an access mediator. An object is created, which is also an instance of MySQLcursor class. This object is used to call its method, i.e., the cursor method.

The syntax of cursor() method and creating cursor object:

# creating an instance of MySQLcursor class and calling the cursor method using that object
cursor = connection.cursor()

As we have already discussed, a query is not initially considered to be a query unless the " execute() " method is introduced. The purpose of the execute method is that it converts the query initially into a string and then receives it as a database query.

Let us have a look at a complete general program where we can create a new database:

# import the driver “ MySQL connector “ initially
import mysql.connector


# the database “ data_base “ is being used to create a connection within the current user and MySQL server and then waiting for the required permissions from the admin user
data_base = mysql.connector.connect(
    user = " name of the current admin user "
    password = " password of the current admin user "
    host = " localhost "
)
# creating an instance of MySQLcursor class and calling the cursor method using that object
cursor = connection.cursor()


# syntax for creating a new database 
database_query = " CREATE DATABASE name_of_new_database " 


# linking the statement “ database_query “ to the cursor to convert into a string and then identify that particular string as a query
cursor.execute(database_query)

An explanation of the program mentioned above:

In order to access MySQL, we have imported everything from MySQL using the " mysql.connector " driver. After that, we initiated a connection between the MySQL databases and the system by taking all permissions from the system's admin in order to access its own databases. The username and password of the admin user should be given within the database area.

A connection is established using the " cursor() “ method and instance of MySQLcursor class. The SQL query for creating a new database is set to the " database_query ". After the implementation of the execute() method, the query is taken as a string at first and then considered to be an SQL query. A new database with the name " name _ of _ new _ database " is created.

Close the connectivity to the MySQL server

The program runs even without closing the access to the databases. But, after completing our work, if the access is left without closing, unnecessary and unauthorized access can take place, which might sometimes lead to the deletion of the entire data in the present database. In order to avoid these issues, we must close the connectivity using the " close() " method.

The syntax of close() method:

# to close the connectivity or access to the specific database
database_name.close()

Let us implement this close() method in the above program.

# import the driver “ MySQL connector “ initially
import mysql.connector


# the database “ data_base “ is being used to create a connection within the current user and MySQL server and then waiting for the required permissions from the admin user
data_base = mysql.connector.connect(
    user = " name of the current admin user "
    password = " password of the current admin user "
    host = " localhost "
)
# creating an instance of MySQLcursor class and calling the cursor method using that object
cursor = connection.cursor()


# syntax for creating a new database 
database_query = " CREATE DATABASE name_of_new_database " 


# linking the statement “ database_query “ to the cursor to convert into a string and then identify that particular string as a query
cursor.execute(database_query)


# to close the connectivity and access to the specific database
data_base.close()

After implementing the close() method, the particular database, i.e., the database with the name “ database_name “ is closed temporarily unless and until we connect to that database again by following all the above steps. 

Let us see an example program that covers all the concepts discussed:

# import the driver “ MySQL connector “ initially
import mysql.connector


# the database “ data_base “ is being used to create a connection within the current user and MySQL server and then waiting for the required permissions from the admin user
data_base = mysql.connector.connect(
    user = " scott "
    password = " tiger "
    host = " localhost "
)
# creating an instance of MySQLcursor class and calling the cursor method using that object “ my_cursor “
my_cursor = connection.cursor()


# syntax for creating a new database 
query = " CREATE DATABASE Hello " 


# linking the statement “ query “ to the cursor to convert it into a string and then identify that particular string as a query
my_cursor.execute(query)


# to close the connectivity and access to the specific database
data_base.close()

The above program creates a database with the name " Hello " and closes the connection after the creation. This is how databases are created using Python and MySQL. You can also insert data, remove data, and modify the data in the databases ( newly created ones or already existing ones ) by using different SQL queries but with the same process.