Build an app / Python + HTTP

How to connect a Python app to SingleStore via HTTP

on-this-guideOn this guide

In this guide, you’ll learn how to connect a Python application to SingleStore using its HTTP Data API. We’ll walk you through creating a deployment, workspace, and database in the SingleStore Portal, retrieving your connection credentials, and configuring your Python project with environment variables. Finally, you’ll implement a reusable helper function to execute and query SQL statements over HTTP, giving you a solid foundation for building applications on SingleStore.

prerequisitesPrerequisites

  • SingleStore Account

  • Python

Don’t have a SingleStore account yet?

Sign up now

create-deploymentCreate deployment

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Create NewDeployment.

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-workspaceCreate 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-databaseCreate 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-credentialsRetrieve 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-environmentPrepare 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:

1DB_USER="<USER>"2DB_PASSWORD="<PASSWORD>"3DB_HOST="<HOST>"4DB_PORT="<PORT>"5DB_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:

1python3 -m venv ./.venv2source ./.venv/bin/activate

4. In the same terminal, install the requests package to handle REST API requests by running the following command:

1pip install requests

5. Install and configure dotenv to load your environment variables by running the following command:

1pip install python-dotenv

6. 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):

1from dotenv import load_dotenv2
3load_dotenv()

7. Finally, you’ll add helper functions to query your database:

1import os2import json3import base644import requests5from dotenv import load_dotenv6
7load_dotenv()8
9DB_USER = os.getenv("DB_USER")10DB_PASSWORD = os.getenv("DB_PASSWORD")11DB_HOST = os.getenv("DB_HOST")12DB_NAME = os.getenv("DB_NAME")13
14credentials_raw = f"{DB_USER}:{DB_PASSWORD}"15CREDENTIALS = base64.b64encode(credentials_raw.encode()).decode()16
17
18def request(endpoint: str, body: str) -> dict:19    url = f"https://{DB_HOST}/api/v2{endpoint}"20    headers = {21        "Content-Type": "application/json",22        "Authorization": f"Basic {CREDENTIALS}",23    }24
25    response = requests.post(url, data=body, headers=headers)26
27    if not response.ok:28        text = response.text29        raise Exception(f"{response.status_code} {response.reason}\n{text}")30
31    return response.json()32
33
34def query(sql: str, database: str = DB_NAME) -> dict:35    payload = {"sql": sql, "database": database}36    return request("/query/rows", json.dumps(payload))37
38
39def execute(sql: str, database: str = DB_NAME) -> dict:40    payload = {"sql": sql, "database": database}41    return request("/exec", json.dumps(payload))
  • execute (/api/v2/exec)Executes SQL statements without returning result sets, typically used for DDL and DML operations (e.g., CREATE TABLE, INSERT, UPDATE, DELETE).
  • query (/api/v2/query/rows)Executes SQL statements and returns result sets typically used for SELECT queries. Each row in the result set is represented as an object mapping column names to their corresponding values.

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

1import os2import json3import base644import requests5from dotenv import load_dotenv6
7load_dotenv()8
9DB_USER = os.getenv("DB_USER")10DB_PASSWORD = os.getenv("DB_PASSWORD")11DB_HOST = os.getenv("DB_HOST")12DB_NAME = os.getenv("DB_NAME")13
14credentials_raw = f"{DB_USER}:{DB_PASSWORD}"15CREDENTIALS = base64.b64encode(credentials_raw.encode()).decode()16
17
18def request(endpoint: str, body: str) -> dict:19    url = f"https://{DB_HOST}/api/v2{endpoint}"20    headers = {21        "Content-Type": "application/json",22        "Authorization": f"Basic {CREDENTIALS}",23    }24
25    response = requests.post(url, data=body, headers=headers)26
27    if not response.ok:28        text = response.text29        raise Exception(f"{response.status_code} {response.reason}\n{text}")30
31    return response.json()32
33
34def query(sql: str, database: str = DB_NAME) -> dict:35    payload = {"sql": sql, "database": database}36    return request("/query/rows", json.dumps(payload))37
38
39def execute(sql: str, database: str = DB_NAME) -> dict:40    payload = {"sql": sql, "database": database}41    return request("/exec", json.dumps(payload))42
43
44def main():45    try:46        print("Create users table")47
48        create_table_sql = """49            CREATE TABLE IF NOT EXISTS users (50                id BIGINT AUTO_INCREMENT PRIMARY KEY,51                name VARCHAR(255) NOT NULL52            );53        """54
55        execute(create_table_sql)56
57        print("Insert user")58        insert_user_sql = "INSERT INTO users (name) VALUES ('John');"59        insert_user_result = execute(insert_user_sql)60        inserted_user_id = insert_user_result.get("lastInsertId")61        print(insert_user_result)62
63        print("Select all users")64        select_users_sql = "SELECT id, name FROM users;"65        select_users_result = query(select_users_sql)66        print(select_users_result)67
68        print("Select user")69        select_user_sql = f"SELECT id, name FROM users WHERE id = {inserted_user_id};"70        select_user_result = query(select_user_sql)71        print(select_user_result)72
73        print("Update user")74        update_user_sql = f"UPDATE users SET name = 'John Doe' WHERE id = {inserted_user_id};"75        update_user_result = execute(update_user_sql)76        print(update_user_result)77
78        print("Delete user")79        delete_user_sql = f"DELETE FROM users WHERE id = {inserted_user_id};"80        delete_user_result = execute(delete_user_sql)81        print(delete_user_result)82    except Exception as error:83        print("Error:", error)84
85
86if __name__ == "__main__":87    main()

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

1python ./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.

error-handling-and-troubleshootingError handling and troubleshooting

auth-failedAUTH_FAILED

If you receive an AUTH_FAILED exception when trying to establish a connection to database, try the following steps to remedy the issue: