Python MySQL Tutorial Using MySQL Connector Python

This Python MySQL tutorial demonstrates how to develop and integrate Python applications that work with a MySQL database server. Python is a dynamic language, and it has all the support to build large and complex enterprise database-centric applications using MySQL.

Total 5 modules available in python to communicate with a MySQL and provides MySQL database support to our applications.

1. MySQL Connector Python
2. PyMySQL
3. MySQLDB
4. mysqlclient
5. OurSQL

Note: Above all interfaces or modules are adhere to Python Database API Specification v2.0 (PEP 249) that means the syntax, method and the way of access database is the same in all.

PEP 249 has been designed to encourage and maintain similarity between the Python modules that are used to access databases. By doing this, above all modules are following rules defined in Python Database API Specification v2.0 (PEP 249).

You can choose any of the above modules as per your requirements. The way of accessing the MySQL database remains the same. I recommend you to use any of the following two modules:-

1. MySQL Connector Python
2. PyMySQL

Note: This tutorial based on MySQL Connector Python module.

MySQL Connector Python has the following advantages: –

• MySQL Connector Python is written in pure Python, and it is self-sufficient to execute database queries through python.
• It is an official Oracle-supported driver to work with MySQL and python.
• It is Python 3 compatible, actively maintained.

All sample examples mentioned in this article are created using MySQL Connector Python. If you are using any other module to work with a MySQL database server, don’t worry. Examples mentioned in this tutorial can also work only you need to change the import statements.

This Python MySQL tutorial mainly focuses on: –

• How to install MySQL Connector Python and use its functions to access the MySQL database.
• Perform MySQL CRUD operations such as data insertion, data retrieval, data update, and data deletion using Python.
• This tutorial also talks about transaction management, connection pooling, and error-handling techniques to develop robust python programs that work with a MySQL database server.

Let see each section now.

Install to MySQL Connector Python

In this section, you are going to learn how to install MySQL Connector Python on Windows, MAC, Linux, Unix, Ubuntu, and other operating system platforms. This section lets you know the prerequisite to install the MySQL connector python module.

You can install MySQL Connector Python using pip command or via source code.

Python MySQL Database Connection

This section mainly focuses on steps to connect the MySQL database in Python using MySQL Connector Python. Use the connect() method of “MySQL Connector Python” with the required parameters to connect MySQL. We can also learn how to close the database connection securely.

Also, learn the various MySQL Connection arguments required to communicate with MySQL server from Python. we will also cover how we can change the MySQL connection timeout when connecting through Python.

Python MySQL SQL SELECT Query to retrieve data from a Table 

• This section demonstrates how to write and execute a SQL SELECT query from Python application to fetch rows from MySQL table.
• Also, learn how to process SELECT query results, Fetch all rows or single row from the table, and count total rows of a table.
• Additionally, learn how to use the Python variable in the Select Query.

Python MySQL Insert data into a database table

This section demonstrates how to execute a MySQL INSERT Query from python to add a new row to the database table. After reading this section, you can insert single and multiple rows into MySQL table. Also, learn how to use Python variables in the parameterized query to insert dynamic data into a table.

Python MySQL Update Database Table

This section demonstrates how to execute a MySQL UPDATE command from python to update the MySQL database and table’s data. After reading this article, you can update a single row, multiple rows, a single column, and multiple columns. Additionally, learn how to use python variables in the parameterized query to update table data.

Python MySQL Delete Database and table’s data

The primary purpose of this section is to demonstrate how to use a SQL DELETE statement from your python to delete MySQL tables and database data. After reading this section, you can Delete a single row, multiple rows, a single column, and multiple columns. Also, learn to Delete all Rows, Delete table and an entire database from MySQL using python.

Python MySQL Execute Stored procedure

This section shows you how to execute MySQL stored procedures in Python and how to pass IN and OUT parameter to the MySQL stored procedure from python application. you will get to know how to use a cursor.callproc() function to call MySQL stored procedure.

Python MySQL Parameterized Query and Prepared Statement

This section demonstrates how to use Parameterized Queries or Prepared Statement from your python application. Also, you can learn how to use python variables in the query. Using a Parameterized query, you can pass user-defined or dynamic data to MySQL table.“]

Python MySQL Commit and Rollback to Manage Transactions

This article mainly focuses on how to manage database transactions in Python while working with MySQL. Also, learn how to manage the ACID property of MySQL transactions within Python using commit and rollback methods.

Python Database Connection Pooling With MySQL

This section mainly focuses on What is a connection pool and how to implement a connection pool using MySQL Connector Python. Learn how to create and use a connection pool to increase the performance of your applications.

Python MySQL BLOB Insert and Retrieve digital data in MySQL using Python

In this section, I will let you know how to insert or save any digital information such as a file, image, video, or a song as a blob data into MySQL table from python. Also, learn how we can fetch the file, image, video, or a song stored in MySQL using Python.

Python Database Programming Exercise

Solve our free Python database exercise to master database programming in Python. I have created a Hospital Information System exercise using Python and MySQL. This Exercise has six questions. I have provided the required tables so you can proceed directly to solve the questions of this exercise.

That’s it. Folks Let me know your comments in the section below.