Connect to MySQL using PyMySQL in Python

What is PyMySQL?

It is a python library which is used for connecting MySQL database. It is completely written in Python and uses python for communicating with MySQL server. Compatible with Python 2.7 and python 3.x, and provides a simple and instinctive interface for connecting to the MySQL database, executing SQL queries and for retrieving results.

Why PyMySQL?

It does not require any additional software, it is easy to use, provides security that is it supports SSL connections for secure communication, hence it is a popular choice for building Python applications that require database connectivity.

What is MySQL

MySQL is an open-source Relational Database Management System (RDMS), which uses Structured Query Language (SQL) to manage and use data in the database. It has various features such as:

• Data storage and retrieval using the tables and rows.

• Indexing and searching capabilities.

• User authentication and access control.

• It provides replication and clustering for high availability and scalability.

• It supports various data types, including strings, numbers, etc.

• Used with other technologies, such as PHP, Python, to build web applications.

Steps Required to Connect MySQL using PyMySQL

These are the six major steps which can be used to connect MySQL using PyMySQL in python.

Step 1: Installing PyMySQL

You can install PyMySQL using pip in the command prompt in your system.

pip install PyMySQL

Step 2: Importing the library

Without importing the library, you cannot connect to the MySQL hence the second step is to import the PyMySQL library and any other library which are required by the code.

Step 3: Creating a connection object

The connection object plays an important role since it takes several parameters such as host, user, password, database, which are used to connect to MySQL server.

Step 4: Creating a cursor object

Since we want to execute our SQL queries we will create a cursor object by calling the cursor() method on the connection object.

Step 5: Execute SQL Queries

For executing the SQL queries, you can use the cursor object which is created in the 4th step. Cursor object comes with different methods for implementing the SQL queries such as fetchall(), execute(), executemany().

Step 6: Close the connection

One of the most important steps is closing the connection to the MySQL server after you have finished executing the SQL queries.

Example

Assume we have created a table named Customers in the MySQL database named test using the following queries –

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25) NOT NULL, 
   SALARY DECIMAL(18, 2), 
   PRIMARY KEY(ID)
);
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 
INSERT INTO CUSTOMERS VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); 
INSERT INTO CUSTOMERS VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 
INSERT INTO CUSTOMERS VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

Now, let’s combine these steps and write a code which will give us a complete understanding of how you can connect to MySQL using PyMySQL in python.

# Importing pymysql
import pymysql
# Creating a connection object.
host = 'localhost'
user = 'root'
password = 'password'
database = 'test'
# Establishing the connection 
connection = pymysql.connect(host=host, user=user, password=password, database=database)
# Creating the cursor object 
cursor = connection.cursor()
# Executing the SQL queries 
cursor.execute("SELECT * FROM customers")
# Printing the results
results = cursor.fetchall()
for row in results:
   print(row)
# closing the connection
cursor.close()
connection.close()

Here, you can change the 'username', 'password', 'database' with whatever names you have given in your MySQL server. This code will help you draw all the rows from the table which you have specified and print the result to you.

Conclusion

Connecting to MySQL using PyMySQL in Python is a simple and efficient way to interact with a MySQL database. You can establish a connection to a MySQL server by following the steps given in this article that is creating the cursor, executing the SQL queries, and retrieving the results. By using the PyMySQL, you can take advantage of various MySQL features such as prepared statements, transactions, and stored procedures.

Overall, PyMySQL provides an easy-to-use interface for connecting to and communicating with a MySQL database in a Python program. Hence, it is a valuable tool for data analysts and developers who work with MySQL databases because it helps in integrating the database into the python applications.