Python MySQL

In this article, we are going to learn the following:

• How to connect Python to MySQL.
• How to create a new Database.
• Procedure for connecting the newly created database.
• Procedure for connecting the already existing database.
• Write SQL queries.

What is MySQL?

The most used Database management system is MySQL. SQL stands for Structured Query Language. This is almost similar to all programming languages that we generally have. But the major difference between Programming languages and Structured Query languages is Structured Query languages run with queries, whereas Programming languages run with programs and codes. One of the servers that can implement SQL is MySQL. MySQL is one of the best Database management systems because it is extremely easy to install free of cost, it is easy to use, it runs with great speed, and it is highly secured. MySQL is available for all operating systems (OS), which includes Windows, Mac OS, Linux, etc.

What are Database Management Systems?

Databases are used to store the data, including all the safety that is needed, and they retrieve the data whenever it is required. The systems that can manage all the data within the databases are known as Database Management Systems. To access the data that is stored in the database, we are supposed to have many permissions from that particular admin.

In java, we have JDBC drivers, i.e., Java Data Base Connectivity drivers, to develop a bridge between any Structured Query language ( like MySQL ) and Programming language ( Java ).

Python MySQL

To have a bridge or connection between python and SQL, python does not have any inbuilt drivers. So, we are supposed to download and install such drivers or software like MySQL.

Step-by-step procedure for arranging a connection and interaction between Python and MySQL:

1. Install MySQL
2. Connect Python to MySQL server using required drivers
3. Create a database
4. Link the new database or existing database
5. Writing SQL queries and execution
6. Close the connectivity to the MySQL server

1. Install MySQL

MySQL is used by python to initiate access to databases and form connectivity between python and databases. We generally use the " MySQL connector " driver to perform these actions. Firstly, we are supposed to install the MySQL driver before initiating and importing the " MySQL connector ". 

The syntax for importing MySQL driver using MySQL connector:

# the driver “ MySQL connector “ must be imported initially
import mysql.connector

You'll not get any errors if the drivers have been successfully installed. The error is a form of indication that the drivers are not installed properly.

2. Connect Python to MySQL server

After the installation of the required drivers, they need to be imported. After that, the user's credentials are needed to be given, including passwords.

The syntax for creating a connection or a bridge between MySQL server and Python:

# the driver MySQL connector must be imported initially
import mysql.connector
# the database “ data_base “ is being created within the current user MySQL server and waiting for the access
data_base = mysql.connector.connect(
    user = " name of your current admin user "
    password = " password of your current admin user "
    host = " localhost "
)
# printing the path of the connectivity of database with python
print(data_base)

The output of this code will be the path of the database connectivity.

After the connection is made between python and MySQL, we can give queried language code to perform further operations.

3. Creation of a Database

Now, we can use queried language In order to perform these types of actions.

The syntax for creating a new database:

CREATE DATABASE new_database_name;

A new database is created with the name " new_database_name ". In python, we do not use semicolons, i.e., " ; " at the end of the statements. But, coming to the queried languages like MySQL, we use a semicolon to indicate the end of the statement. As we have imported MySQL to python, we need to put a semicolon at the end of every statement as soon as the database is linked with python. We are supposed to use the " cursor " method to gain access to databases ( newly created ones or the existing ones ). The cursor method helps you to have access to a particular database. In order to execute SQL queries, we need to use the cursor method, i.e., " cursor() “.

4. Cursor method [ cursor() ]:

The cursor method gives complete access to the database records and connects the Python to MySQL connector, which in turn reflects with the MySQL server. So, the job of the cursor method is to stand as a mediator between the MySQL server and MySQL connector. In other words, the cursor is an instance of MySQLcursor class. In order to create a cursor, we generally call it the cursor method or cursor().

 Syntax for creating cursor or creating an instance of MySQLcursor class:

# creating an instance of MySQLcursor class
cursor = connection.cursor()

Whenever a query needs to be executed, it is sent to the method " cursor.execute() " ( where the cursor is the created or instantiated object in MySQLcursor class ) initially in the form of a string and then that particular query will be considered.

Example notation for creating a new database and defining a query using the instance of MySQLcursor class “cursor”:

# the driver MySQL connector must be imported initially
import mysql.connector


# the database “ data_base “ is being created within the current user MySQL server and waiting for the access
data_base = mysql.connector.connect(
    user = " name of your current admin user "
    password = " password of your current admin user "
    host = " localhost "
)
# creating a database query 
database_query = "CREATE DATABASE new_data_base_query_creation" 


# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()


# linking the statement “ database_query “ to the cursor to identify that particular statement as a query
cursor.execute(database_query)

If the code is executed without any errors, then you’ll find a new database in the MySQL server, i.e., denoting the creation of a new database " database_query " through a query is successful. The statement that is given is taken as a string and then converted into a query with the help of " cursor.execute() ". Being a query, the new database is created according to the command given within it. So, after the completion of the creation of the new database, it will be available on the MySQL server.

Example notation for defining a query using the instance of MySQLcursor class "cursor" with an existing database:

# the driver MySQL connector must be imported initially
import mysql.connector


# the database “ data_base “ is being created within the current user MySQL server and waiting for the access
data_base = mysql.connector.connect(
    user = " name of your current admin user "
    password = " password of your current admin user "
    host = " localhost " 
    existing_db = " my_name "
)


# printing the already existing database
print(data_base)

The output of this program will be the name of the existing database, i.e., " my_name ". This output indicates the successful connection with the existing database.

What if you want to know what all databases are already being existed in your computer system? What should you do to display all those names using Python MySQL connectivity? Let us learn how to perform this operation.

Example notation for printing all names of the existing databases in the computer system:

# the driver MySQL connector must be imported initially
import mysql.connector


# the database “ data_base “ is being created within the current user MySQL server and waiting for the access
data_base = mysql.connector.connect(
    user = " name of your current admin user "
    password = " password of your current admin user "
    host = " localhost "
)
# creating a database query 
database_query = " SHOW DATABASES " 


# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()


# linking the statement “ database_query “ to the cursor to identify that particular statement as a query
cursor.execute(database_query)


# printing all the existing databases using a for loop 
for x in cursor:
    print(x)

The code prints all existing databases within the system using for loop. The statement that is given with the name of " database_query " is taken as a string and then converted into a query with the help of " cursor.execute() ". Being a query, the databases that are present will be printed according to the command given within it. We have used "for loop" because we are not sure about the number of databases that are already present. So, to print all databases, it would be easy if we execute with the help of for loop. For every iteration, one name of the existing database will be printed.

Close the connectivity to the MySQL server or a particular database

We use the " close() " method when we wish to stop someone from accessing that database.

The syntax for closing the database:

database_name.close()


Let us consider the above example and close the access to that database.


# the driver MySQL connector must be imported initially
import mysql.connector


# the database “ data_base “ is being created within the current user MySQL server and waiting for the access
data_base = mysql.connector.connect(
    user = " name of your current admin user "
    password = " password of your current admin user "
    host = " localhost "
)
# creating a database query 
database_query = " SHOW DATABASES " 


# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()


# linking the statement “ database_query “ to the cursor to identify that particular statement as a query
cursor.execute(database_query)


# printing all the existing databases using a for loop 
for x in cursor:
    print(x)


# close the access to the database
data_base.close()

Note: If at all you want to access the same database after closing, you have to reconnect it.