Build an app / Python + mysql-connector-python

How to connect a Python app to SingleStore using mysql-connector-python

On this guide

This guide walks you through the steps to connect a Python application to a SingleStore database using mysql-connector-python. You’ll start by setting up a SingleStore deployment and workspace, then create a database and retrieve your connection credentials. Finally, you’ll configure your Python project by installing the mysql-connector-python package, loading environment variables, and establishing a connection to handle queries against your SingleStore instance. By the end, you’ll have a working example that you can use in your application.

Prerequisites

SingleStore Account
Python

Don’t have a SingleStore account yet?

Skip to next section

Create deployment

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Create New → Deployment.

3. In the Create Workspace form, follow the on‑screen instructions to complete the form.

4. Click Create Workspace.

5. Wait for the workspace to finish deploying.

Create workspace

Note: If the required workspace already exists in the target deployment, you can skip this step.

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Deployments.

3. From the deployments list, select the deployment where you want to create a workspace.

4. In the left‑hand pane, click + Create Workspace.

5. In the Create Workspace form, follow the on‑screen instructions to complete the form.

6. Сlick Create Workspace.

7. Wait for the workspace to finish deploying.

Create database

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Deployments.

3. From the deployments list, select the deployment where you want to create a database.

4. In the right‑hand pane, click + Create Database.

5. In the Create Database form, enter a new database name and select the workspace to attach it to.

6. Click Create Database.

Retrieve database credentials

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Deployments.

3. From the deployments list, select the deployment that contains your database.

4. From the workspaces list, select the workspace to which your database is attached.

5. In the selected workspace, click Connect.

6. In the Connect dropdown, choose SQL IDE.

7. In the SQL IDE tab, copy the connection parameters.

If you don’t know the password, click Reset Password, then copy the new password.

Prepare environment

1. Create a .env file in the root of your project.

2. In this file, define your connection details by adding the following variables:

1
DB_USER="<USER>"
2
DB_PASSWORD="<PASSWORD>"
3
DB_HOST="<HOST>"
4
DB_PORT="<PORT>"
5
DB_NAME="<DATABASE_NAME>"

If you don’t know your connection string, see the Retrieve database credentials section above.

3. Now, in a terminal pointing to the root directory of your project, create (if you haven't already) and activate a Python virtual environment by running the following commands:

python3 -m venv ./.venv

source ./.venv/bin/activate

4. Install the mysql-connector-python PyPI package by running the following command:

pip install mysql-connector-python

5. In the same terminal, install and configure dotenv to load your environment variables by running the following command:

pip install python-dotenv

Then, you’ll need to import dotenv into your application so you can retrieve the environment variables stored in your .env file. To do this, at the following import statement at the very top of your application entry point (e.g., app.py):

1
from dotenv import load_dotenv
2

3
load_dotenv()

Establish database connection

In your application entry point, add the following code to establish a database connection:

1
import os
2
from dotenv import load_dotenv
3
import mysql.connector
4

5
load_dotenv()
6

7
db = mysql.connector.connect(
8
    user=os.getenv('DB_USER'),
9
    password=os.getenv('DB_PASSWORD'),
10
    host=os.getenv('DB_HOST'),
11
    port=int(os.getenv('DB_PORT', 3306)),
12
    database=os.getenv('DB_NAME')
13
)

You now have everything set up to start querying your database.

Query database

Let’s test to make sure that the connection is working as anticipated. Here is an example of a simple code snippet you can add to your app's main function at the entry point.

1
import os
2
from dotenv import load_dotenv
3
import mysql.connector
4

5
load_dotenv()
6

7
db = mysql.connector.connect(
8
    user=os.getenv('DB_USER'),
9
    password=os.getenv('DB_PASSWORD'),
10
    host=os.getenv('DB_HOST'),
11
    port=int(os.getenv('DB_PORT', 3306)),
12
    database=os.getenv('DB_NAME')
13
)
14

15

16
def main():
17
    cursor = None
18
    try:
19
        cursor = db.cursor(dictionary=True)
20

21
        print("Create users table")
22
        create_table_sql = """
23
            CREATE TABLE IF NOT EXISTS users (
24
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
25
                name VARCHAR(255) NOT NULL
26
            )
27
        """
28
        cursor.execute(create_table_sql)
29
        db.commit()
30

31
        print("Insert user")
32
        insert_user_sql = "INSERT INTO users (name) VALUES (%s)"
33
        cursor.execute(insert_user_sql, ("John",))
34
        db.commit()
35
        inserted_user_id = cursor.lastrowid
36
        print(f"Inserted user ID: {inserted_user_id}")
37

38
        print("Select all users")
39
        select_users_sql = "SELECT id, name FROM users"
40
        cursor.execute(select_users_sql)
41
        users = cursor.fetchall()
42
        print(users)
43

44
        print("Select user")
45
        select_user_sql = "SELECT id, name FROM users WHERE id = %s"
46
        cursor.execute(select_user_sql, (inserted_user_id,))
47
        user = cursor.fetchone()
48
        print(user)
49

50
        print("Update user")
51
        update_user_sql = "UPDATE users SET name = %s WHERE id = %s"
52
        cursor.execute(update_user_sql, ("John Doe", inserted_user_id))
53
        db.commit()
54
        print(f"Updated rows: {cursor.rowcount}")
55

56
        print("Delete user")
57
        delete_user_sql = "DELETE FROM users WHERE id = %s"
58
        cursor.execute(delete_user_sql, (inserted_user_id,))
59
        db.commit()
60
        print(f"Deleted rows: {cursor.rowcount}")
61
    except Exception as error:
62
        print("Error:", error)
63
    finally:
64
        if cursor:
65
            cursor.close()
66
        db.close()
67

68

69
if __name__ == "__main__":
70
    main()

Once you’ve saved your changes, open a terminal, navigate to your project’s root directory, and run the test script:

python ./app.py

This will issue a query against your SingleStore instance, allowing you to see if connectivity was successful or if further troubleshooting is required.