Connect to Aiven for MySQL® with Python

This example connects your Python application to an Aiven for MySQL® service, using the PyMySQL library.

Variables

These are the placeholders to replace in the code sample:

Variable	Description
MYSQL_HOST	Host name for the connection, from Aiven Console > the Overview page of your service
MYSQL_PORT	Port number to use, from Aiven Console > the Overview page of your service
MYSQL_USERNAME	User to connect with
MYSQL_PASSWORD	Password for this user

Prerequisites

For this example you will need:

• Python 3.7 or later

• The Python PyMySQL library. You can install this with pip:

  pip install pymysql

• Install the cryptography package:

  pip install cryptography

Code

Add the following to main.py and replace the placeholders with values for your project:

import pymysql

timeout = 10
connection = pymysql.connect(
    charset="utf8mb4",
    connect_timeout=timeout,
    cursorclass=pymysql.cursors.DictCursor,
    db="defaultdb",
    host=MYSQL_HOST,
    password=MYSQL_PASSWORD,
    read_timeout=timeout,
    port=MYSQL_PORT,
    user=MYSQL_USERNAME,
    write_timeout=timeout,
)

try:
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE mytest (id INTEGER PRIMARY KEY)")
    cursor.execute("INSERT INTO mytest (id) VALUES (1), (2)")
    cursor.execute("SELECT * FROM mytest")
    print(cursor.fetchall())
finally:
    connection.close()

This code creates a MySQL client and connects to the database. It creates a table, inserts some values, fetches them and prints the output.

To run the code:

python main.py

If the script runs successfully, the output will be the values that were inserted into the table:

[{'id': 1}, {'id': 2}]

Now that your application is connected, you are all set to use Python with Aiven for MySQL.

warning

Make sure to create a table with a unique name. If you try to create a table that already exists, an exception will be raised.