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 you need 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

Pre-requisites#

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 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.