Most programming languages like Python provide support for this. In fact, in Python, there are several approaches you can take, each with its own advantages.
Prepare Your MySQL Configuration
To connect to the database, you need the following values:
Host: the location of the MySQL server, localhost if you’re running it on the same computer. User: the MySQL username. Password: the MySQL password. Database name: the name of the database you want to connect to.
Before connecting to the MySQL database, create a new directory:
Set Up a Python Virtual Environment
A Python virtual environment allows you to install packages and run scripts in an isolated environment. When you create a virtual environment, you can then install versions of Python and Python dependencies within it. This way, you isolate different versions and avoid compatibility issues.
Connect to MySQL Using mysqlclient
The mysqlclient driver is an interface to the MySQL database server that provides the Python database server API. It is written in C.
Run the following command in the virtual environment to install mysqlclient:
If you are on a Linux machine, install the Python 3 and MySQL development headers and libraries first.
On Windows, you can install mysqlclient using a binary wheel file. Download the mysqlclient file that’s compatible with your platform from Christoph Gohlke’s unofficial collection. You can then use the downloaded wheel file with pip to install mysqlclient like this:
Use the following connection code to connect to the MySQL database once the installation is complete:
In this program, you have:
Imported mysqlclient. Created a connection object using MySQLdb. connect(). Passed the database configuration details to MySQLdb. connect(). Created a cursor object to interact with MySQL. Used the cursor object to fetch the version of the connected MySQL database.
Remember to switch the database details with your own.
Connect to MySQL Using mysql-connector-python
mysql-connector-python is the official connection driver supported by Oracle. It is also written in pure Python.
Install it via pip to start using it.
Connect to MySQL using the following connection code.
The above connection code does the same thing that the mysqclient connection code does.
Once you create the connection object, you can create a cursor, which you can then use to execute queries on the database.
This connection program also uses the try…catch block. The Error class, from mysql.connector, lets you catch exceptions raised when connecting to the database. This should simplify debugging and troubleshooting.
Connect to MySQL Using PyMySQL
The PyMySQL connection driver is a replacement for MySQLdb. To use it, you need to be running Python 3.7 or newer and your MySQL server should be version 5. 7, or newer. If you use MariaDB it should be version 10.2 or higher. You can find these requirements on the PyMySQL Github page.
To install PyMySQL, run the following command.
Connect to MySQL using PyMySQL using this code.
Once you’ve made the connection, and created the cursor object, you can start making SQL queries.
Connect to MySQL Using aiomysql
The aiomysql connection driver is like the asynchronous version of PyMySQL. It provides access to a MySQL database from the asyncio framework.
To use aiomysql, you need Python 3.7+ and PyMySQL installed in your development environment.
Run the following command to install asyncio and aiomysql.
With aiomysql, you can connect Python to MySQL using a basic connection object and using a connection pool.
Here is an example showing how to connect to the MySQL database using a connection object.
Unlike a database connection object, a connection pool allows you to reuse database connections. It does this by maintaining a pool of open connections and assigning them upon request. When a client requests a connection, they are assigned one from the pool. Once the client closes the connection, the connection goes back to the pool.
The basic code for connecting through a pool is like below:
This program should print the version of MySQL you have connected to when you run it.
Managing Your PostgreSQL Database
This article showed you several ways you can connect a Python application to MySQL. Each of these methods allows you to interact with and execute queries on the database.
Once connected to a MySQL database, you can execute data queries and perform database transactions. You can create a Python application, hook it up to MySQL, and begin storing data.
