Skip to main content

MySQL® - Connect with Python

Stephanie R.
Updated

Learn how to connect to a MySQL® database instance with one of the world's most famous programming languages: Python.

Cloud Databases allow you to focus on building and deploying cloud applications while OVHcloud takes care of the database infrastructure and maintenance in operational conditions.

You can find an example on the GitHub examples repository.

 

Requirements

 

Concept

A MySQL® instance can be managed in multiple ways. One of the easiest, yet powerful, is to use a Command Line Interface (CLI), as shown in our guide: Connect to MySQL® with CLI.

Another way is to interact directly using a programming language, such as Python. Python is one of the major programming languages in the world, especially in the Data ecosystem.

To do so, we will need to set up our Python environment with MySQL® drivers and finally code in Python to perform a few example actions.

 

Instructions

Set up your Python environment

To interact with your MySQL® instance using Python, your development environment needs to be configured with:

  • A compatible version of Python
  • Mysql connector

Please follow the official MySQL® Connector/Python documentation to get the latest information.

Once your Python environment is set up and you begin executing a python --version in your command line interface (CLI), you should see information about the version as shown below:

laptop$ python3 --version
Python 3.9.7

In the same console, by typing a pip list, check if mysql-connector-python is correctly installed:

laptop$  pip list           
Package                Version
---------------------- -------
mysql-connector-python 8.0.27
pip                    20.3.4
protobuf               3.19.3
setuptools             52.0.0
wheel                  0.34.2
(...)

We are now ready to learn how to connect to our MySQL® instance!

 

Connect with Python

Using MySQL® Connector

MySQL® Connector/Python enables Python programs to access MySQL® databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249).

In your Python environment, let's try a connection.

First, create the file connect-mysql.py.

Use the code example below, substituting your <user name>, <password>, <host>, and <port> into the spaces:

# Note this example is valid for Python v2 and v3

from __future__ import print_function
import sys
import mysql.connector
from mysql.connector.constants import ClientFlag

config = {
    'user':'avnadmin',
    'password':'K93xxxxxxxxxxaBp',
    'host':'mysql-11xxxx20-o2xxxxb53.database.cloud.ovh.us',
    'port':'20184'
}

cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=False)
cur.execute("SHOW GLOBAL STATUS LIKE 'Ssl_cipher'")
print(cur.fetchone())
cur.close()
cnx.close()

After executing your Python code, the result shown in the CLI should be like this:

('Ssl_cipher', 'TLS_AES_256_GCM_SHA384')

If not, your CLI should give you more details about the issue, for example:

  • Authentication failed: could be an error with your user login or password;
  • nodename nor servname provided, or not known: could be a wrongly typed hostname;
  • Connection reset by peer: usually due to connection without secure TLS mode. Use tls=true parameter.

 

Go further

For more information and tutorials, please see our other Managed Databases & Analytics or Platform as a Service guides. You can also explore the guides for other OVHcloud products and services.

OVHcloud Managed Databases and Analytics:

  • Grafana® is a registered trademark of Grafana Labs and is used with the permission of Grafana Labs. OVH SAS and its subsidiaries are not affiliated with or endorsed by Grafana Labs.
  • Kafka® is a registered trademark of The Apache Software Foundation and has been licensed for use by OVHcloud, who has no affiliation with and is not endorsed by The Apache Software Foundation.
  • MongoDB® is a registered trademark of MongoDB, Inc.
  • MySQL® is a registered trademark of Oracle and/or its affiliates.
  • PostgreSQL® is a registered trademark of the PostgreSQL Community Association of Canada.

Related articles