Skip to main content

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:

VariableDescription
MYSQL_HOSTHost name for the connection, from Aiven Console > the Overview page of your service
MYSQL_PORTPort number to use, from Aiven Console > the Overview page of your service
MYSQL_USERNAMEUser to connect with
MYSQL_PASSWORDPassword 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.