SingleStoreDB Python Client

Warnings

• breaking The query parameter substitution syntax changed significantly in v0.5.0. Positional parameters changed from `:1` to `%s`, and named parameters from `:key` to `%(key)s`.
  Fix: Update all SQL queries to use the Python DB-API 2.0 standard substitution syntax (`%s` for positional, `%(key)s` for named parameters).
• breaking The `results_format` connection parameter was renamed to `results_type` in v0.5.0.
  Fix: Replace `results_format` with `results_type` in your `s2.connect()` calls and any configuration.
• gotcha Not using parameterized queries (e.g., f-strings or direct string concatenation) can lead to SQL injection vulnerabilities and incorrect query parsing.
  Fix: Always use placeholders (`%s` or `%(key)s`) and pass parameters as a tuple or dictionary to `cursor.execute()`. The client handles proper escaping and quoting.
• gotcha Failing to close database connections and cursors can lead to resource exhaustion and performance issues.
  Fix: Use Python's `with` statement for `s2.connect()` and `conn.cursor()` to ensure proper resource management, as shown in the quickstart example.
• breaking Minimum Python version requirement was raised to 3.8 in v0.8.0. Current version 1.16.9 requires Python 3.9+.
  Fix: Ensure your environment uses Python 3.9 or higher. For older Python versions, you may need to use an older client version or upgrade your Python environment.

Install

• pip install singlestoredb Install core client
• pip install 'singlestoredb[sqlalchemy]' Install with SQLAlchemy support
• pip install 'singlestoredb[dataframe]' Install with Pandas/Ibis DataFrame support

Imports

• connect

  import singlestoredb as s2
  conn = s2.connect(...)

• Connection

  from singlestoredb import Connection # (less common, usually obtained from s2.connect())

• Cursor

  from singlestoredb import Cursor # (less common, usually obtained from conn.cursor())

Quickstart

This quickstart demonstrates how to connect to a SingleStoreDB instance using environment variables for credentials, create a table, insert data using parameterized queries, and fetch results. The `with` statements ensure connections and cursors are properly closed.

import singlestoredb as s2
import os

# Get connection details from environment variables for security
HOST = os.environ.get('SINGLESTOREDB_HOST', '127.0.0.1')
PORT = int(os.environ.get('SINGLESTOREDB_PORT', '3306'))
USER = os.environ.get('SINGLESTOREDB_USER', 'root')
PASSWORD = os.environ.get('SINGLESTOREDB_PASSWORD', 'password')
DATABASE = os.environ.get('SINGLESTOREDB_DATABASE', 'test_db')

try:
    # Establish a connection
    with s2.connect(
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD,
        database=DATABASE
    ) as conn:
        print("Successfully connected to SingleStoreDB!")

        # Create a cursor object
        with conn.cursor() as cur:
            # Execute a query
            cur.execute("CREATE TABLE IF NOT EXISTS my_table (id INT, name VARCHAR(255))")
            cur.execute("INSERT INTO my_table (id, name) VALUES (%s, %s)", (1, 'Alice'))
            conn.commit()
            print("Table created and data inserted.")

            # Fetch results
            cur.execute("SELECT * FROM my_table")
            for row in cur.fetchall():
                print(row)

except s2.Error as e:
    print(f"Database error: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")