Connecting to a MySQL database with Python can be confusing. There are several different connection drivers, and not all of them play nicely with different operating systems. On top of that, enabling SSL can be a challenge.
This article covers four methods you can use to connect your Python application to MySQL. These examples will use PlanetScale, a serverless database platform for MySQL, but the actual database you connect to is arbitrary.
The following connection methods will be covered:
If you need a free database to test your connection, PlanetScale is an excellent option. PlanetScale is built on top of Vitess and provides you a familiar branching workflow to test and deploy schema changes.
Sign up for an account to get a free 5GB database.
Create a database
Once you're in the PlanetScale dashboard:
Your database will be created with a
4 development branch. You can make schema changes here and promote it to production once you're ready.
Get the connection credentials
To generate the connection credentials:
To ensure a secure connection, PlanetScale uses certificates from a Certificate Authority (CA) that is part of the system root available on almost all platforms. You must specify the path to the CA certificates you trust when connecting to the database. Note that this path depends on your platform. across various operating systems and distributions.
If you copied the credentials from the PlanetScale dashboard, this should already be done for you, as PlanetScale auto-detects your operating system.
If you’re using a Windows system, you need to download the root certificates, and then point to their location with the correct configuration options for the driver you’re using.
0 package is one of the most popular Python packages for MySQL. It contains the
7 module, an interface to MySQL that provides the Python database API.
Begin by installing
0. You can do this in two ways:
On Linux, you might need to install the Python3 and MySQL development headers and libraries before installing
Once you have
0 installed, you can connect to the database using the following code:
Note, you must install
2 and import the
3 module to access the database credentials in the
This example uses the connection to fetch the SQL version of the database and then closes the connection.
The rest of the connection methods have nearly the same syntax as this example. This similar syntax stems from the Python database API specification (PEP 249) that encourages consistency among the Python modules used to access databases.
The MySQL Connector/Python module is the official Oracle-supported driver to connect MySQL through Python. The connector is entirely Python, while
0 is written in C. It’s also self-contained, meaning it doesn’t require the MySQL client library or any Python modules outside the standard library.
Note that MySQL Connector/Python doesn’t support the old MySQL server authentication methods, which means that MySQL versions before 4.1 don’t work.
Start by installing the
1 module. It’s recommended to install it using pip:
Once installed, use the following code to connect to MySQL:
The PyMySQL package is another connector you can use to connect Python to MySQL. It’s a good option if you’re looking for speed, as it’s faster than
You can instal it using pip with:
Then, use the following connection code:
3 library is used for accessing a MySQL database from the asyncio framework. Apart from being asynchronous, the connection code is similar to PyMySQL. Note that using
3 requires Python 3.7+ and PyMySQL.
3, install the asynchronous module first with:
You can then connect to MySQL using the following code:
Persisting data in a database is one of the common features of software applications. Like Python, most programming languages support connection and interaction with different databases.
This tutorial explored connecting Python to MySQL. We shared four different common connectors you can use and tested them on a PlanetScale database using SSL for security — a crucial step, as it prevents man-in-the-middle attacks. We reviewed how the connector worked for each connection method and showed you the code necessary to run it. Now that you’ve worked through this hands-on overview, you’re all set to connect a MySQL database to a Python app.
How do I run MySQL connector in Python?
Procedure to Follow in Python to Work with MySQL.
Connect to the database..
Create an object for your database..
Execute the SQL query..
Fetch records from the result..
Informing the Database if you make any changes in the table..
How do I open MySQL connector?
To connect to MySQL Server:.
Locate the MySQL Command-Line Client. ... .
Run the client. ... .
Enter your password. ... .
Get a list of databases. ... .
Create a database. ... .
Select the database you want to use. ... .
Create a table and insert data. ... .
Finish working with the MySQL Command-Line Client..
Is MySQL connector a library in Python?
It is written in pure Python and does not have any dependencies except for the Python Standard Library. ... There are various versions of MySQL Connector/Python available:.
Where does MySQL connector install Python?
Installation. Packages are available at the Connector/Python download site. For some packaging formats, there are different packages for different versions of Python; choose the one appropriate for the version of Python installed on your system.