SQLAlchemy SingleStoreDB Dialect

Common errors

• ModuleNotFoundError: No module named 'singlestoredb'

  cause The underlying SingleStoreDB Python client library (`singlestoredb`) is not installed.
  fix Install the `singlestoredb` client: `pip install singlestoredb` (or `pip install sqlalchemy-singlestoredb singlestoredb` to install both).

• sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:singlestoredb

  cause The `sqlalchemy-singlestoredb` package, which registers the dialect with SQLAlchemy, is either not installed or its entry points are not properly recognized.
  fix Ensure `sqlalchemy-singlestoredb` is installed: `pip install sqlalchemy-singlestoredb`. If it is installed and the error persists, try reinstalling it or checking your Python environment's path.

• sqlalchemy.exc.OperationalError: (singlestoredb.exceptions.OperationalError) (1045, "Access denied for user '...'@'%' (using password: YES)")

  cause The username, password, host, or port provided in the connection string is incorrect, or the user lacks necessary permissions on the SingleStoreDB server.
  fix Double-check your connection string details (username, password, host, port, database name). Verify network connectivity to the SingleStoreDB host and ensure the user has appropriate database privileges.

• sqlalchemy.exc.OperationalError: (singlestoredb.exceptions.OperationalError) (2003, "Can't connect to MySQL server on '...' (timed out)")

  cause The SingleStoreDB server is not running, is unreachable from the client's network, or the specified host/port is incorrect.
  fix Verify that the SingleStoreDB server is running and accessible from your network. Check the host IP address and port number in your connection string. Ensure no firewall rules are blocking the connection.

Warnings

• gotcha The `client_found_rows` connection parameter was changed to `True` by default in v1.1.3. This affects how `rowcount` is reported for DML statements (INSERT, UPDATE, DELETE), matching MySQL's `CLIENT_FOUND_ROWS` behavior. Applications relying on the old `rowcount` behavior might see different results.
  Fix: Review existing code that inspects `rowcount` after DML operations. If the old behavior is desired, explicitly set `client_found_rows=False` in the connection URL or `connect_args`.
• breaking Earlier versions (prior to v1.1.2) had issues handling special characters in passwords within the connection URL, leading to connection failures. While fixed, upgrading is recommended if you're on an older version and use complex passwords.
  Fix: Upgrade to `sqlalchemy-singlestoredb` v1.1.2 or newer. Ensure passwords are URL-encoded if manually constructing connection strings for older versions, though this library should handle it correctly in newer releases.
• gotcha Support for SQLAlchemy 2.0 was explicitly added in v0.3.0 and further refined in v1.1.1 to fix deprecation warnings. If you're using SQLAlchemy 2.0 with an older version of this dialect, you might encounter deprecation warnings or compatibility issues.
  Fix: Ensure you are using `sqlalchemy-singlestoredb` v1.1.1 or newer when working with SQLAlchemy 2.0 to avoid deprecation warnings and benefit from full compatibility.
• gotcha Version 1.2.0 introduced `singlestoredb_` prefixed keyword parameters (e.g., `singlestoredb_shard_key`, `singlestoredb_sort_key`) to SQLAlchemy DDL objects. Existing code will continue to work, but to leverage SingleStoreDB-specific features for table creation (like shard/sort keys, persisted columns), these new parameters should be adopted.
  Fix: Review the documentation for `sqlalchemy-singlestoredb` to understand and implement the new `singlestoredb_` keyword arguments when defining table structures with SingleStoreDB-specific features.

Install

• pip install sqlalchemy-singlestoredb singlestoredb Install with driver

Imports

• create_engine
  wrong:

  from sqlalchemy_singlestoredb import create_engine

  correct:

  from sqlalchemy import create_engine

  The dialect registers itself with SQLAlchemy; `create_engine` is imported from `sqlalchemy`.
• text

  from sqlalchemy import text

  Used for executing raw SQL queries within SQLAlchemy.

Quickstart

This quickstart demonstrates how to establish a connection to SingleStoreDB using SQLAlchemy, create a table, insert data, and select data. It uses environment variables for secure connection string management and handles basic error reporting.

import os
from sqlalchemy import create_engine, text

# Get connection details from environment variables for security
user = os.environ.get('SINGLESTORE_USER', 'admin')
password = os.environ.get('SINGLESTORE_PASSWORD', 'password')
host = os.environ.get('SINGLESTORE_HOST', '127.0.0.1')
port = os.environ.get('SINGLESTORE_PORT', '3306')
database = os.environ.get('SINGLESTORE_DATABASE', 'test_db')

# Construct connection URL
connection_url = f"singlestoredb://{user}:{password}@{host}:{port}/{database}"

try:
    # Create an engine instance
    engine = create_engine(connection_url)

    # Establish a connection and execute a simple query
    with engine.connect() as connection:
        # Example: Create a table if it doesn't exist
        connection.execute(text("CREATE TABLE IF NOT EXISTS my_table (id INT, name VARCHAR(255))"))
        print("Table 'my_table' ensured.")

        # Example: Insert data
        connection.execute(text("INSERT INTO my_table (id, name) VALUES (:id, :name)"), {"id": 1, "name": "Alice"})
        connection.execute(text("INSERT INTO my_table (id, name) VALUES (:id, :name)"), {"id": 2, "name": "Bob"})
        print("Data inserted.")

        # Example: Select data
        result = connection.execute(text("SELECT id, name FROM my_table"))
        for row in result:
            print(f"ID: {row.id}, Name: {row.name}")
        
        # Commit the changes (if not in autocommit mode, depends on dialect/DB config)
        connection.commit()

except Exception as e:
    print(f"An error occurred: {e}")