In this lesson, you will learn how to connect the MySQL database in Python using the ‘MySQL Connector Python‘ module. This Python MySQL tutorial demonstrates how to develop and integrate Python applications with a MySQL database server.
In Python, We can use the following modules to communicate with MySQL.
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 way of access the database is the same in all.
PEP 249 is 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
Advantages and benefits of MySQL Connector Python: –
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.
How to Connect to MySQL Database in Python
1)Install MySQL connector module
Use the pip command to install MySQL connector Python.
pip install mysql-connector-python
2)Import MySQL connector module
Import using a import mysql.connector statement so you can use this module’s methods to communicate with the MySQL database.
3)Use the connect() method
Use the connect() method of the MySQL Connector class with the required arguments to connect MySQL. It would return a MySQLConnection object if the connection established successfully
4)Use the cursor() method
Use the cursor() method of a MySQLConnection object to create a cursor object to perform various SQL operations.
5)Use the execute() method
The execute() methods run the SQL query and return the result.
6)Extract result using fetchall()
Use cursor.fetchall() or fetchone() or fetchmany() to read query result.
7)Close cursor and connection objects
use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes
