{"id":7750,"library":"sqlalchemy-singlestoredb","title":"SQLAlchemy SingleStoreDB Dialect","description":"SQLAlchemy SingleStoreDB is a dialect for the SingleStoreDB database, enabling SQLAlchemy applications to connect and interact with SingleStoreDB. It provides support for SingleStoreDB-specific features like shard keys, sort keys, persisted columns, and vector data types. The current version is 1.2.1, and it typically releases new versions every few months to add features or address issues.","status":"active","version":"1.2.1","language":"en","source_language":"en","source_url":"https://github.com/singlestore-labs/sqlalchemy-singlestoredb","tags":["sqlalchemy","database","singlestore","orm","dialect"],"install":[{"cmd":"pip install sqlalchemy-singlestoredb singlestoredb","lang":"bash","label":"Install with driver"}],"dependencies":[{"reason":"Required ORM framework for the dialect.","package":"sqlalchemy"},{"reason":"The underlying DB-API 2.0 driver for connecting to SingleStoreDB.","package":"singlestoredb"}],"imports":[{"note":"The dialect registers itself with SQLAlchemy; `create_engine` is imported from `sqlalchemy`.","wrong":"from sqlalchemy_singlestoredb import create_engine","symbol":"create_engine","correct":"from sqlalchemy import create_engine"},{"note":"Used for executing raw SQL queries within SQLAlchemy.","symbol":"text","correct":"from sqlalchemy import text"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text\n\n# Get connection details from environment variables for security\nuser = os.environ.get('SINGLESTORE_USER', 'admin')\npassword = os.environ.get('SINGLESTORE_PASSWORD', 'password')\nhost = os.environ.get('SINGLESTORE_HOST', '127.0.0.1')\nport = os.environ.get('SINGLESTORE_PORT', '3306')\ndatabase = os.environ.get('SINGLESTORE_DATABASE', 'test_db')\n\n# Construct connection URL\nconnection_url = f\"singlestoredb://{user}:{password}@{host}:{port}/{database}\"\n\ntry:\n    # Create an engine instance\n    engine = create_engine(connection_url)\n\n    # Establish a connection and execute a simple query\n    with engine.connect() as connection:\n        # Example: Create a table if it doesn't exist\n        connection.execute(text(\"CREATE TABLE IF NOT EXISTS my_table (id INT, name VARCHAR(255))\"))\n        print(\"Table 'my_table' ensured.\")\n\n        # Example: Insert data\n        connection.execute(text(\"INSERT INTO my_table (id, name) VALUES (:id, :name)\"), {\"id\": 1, \"name\": \"Alice\"})\n        connection.execute(text(\"INSERT INTO my_table (id, name) VALUES (:id, :name)\"), {\"id\": 2, \"name\": \"Bob\"})\n        print(\"Data inserted.\")\n\n        # Example: Select data\n        result = connection.execute(text(\"SELECT id, name FROM my_table\"))\n        for row in result:\n            print(f\"ID: {row.id}, Name: {row.name}\")\n        \n        # Commit the changes (if not in autocommit mode, depends on dialect/DB config)\n        connection.commit()\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")","lang":"python","description":"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."},"warnings":[{"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`.","message":"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.","severity":"gotcha","affected_versions":"<1.1.3"},{"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.","message":"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.","severity":"breaking","affected_versions":"<1.1.2"},{"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.","message":"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.","severity":"gotcha","affected_versions":"<0.3.0, <1.1.1 (for SQLAlchemy 2.0 users)"},{"fix":"Review the documentation for `sqlalchemy-singlestoredb` to understand and implement the new `singlestoredb_` keyword arguments when defining table structures with SingleStoreDB-specific features.","message":"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.","severity":"gotcha","affected_versions":"<1.2.0 (for missing features)"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Install the `singlestoredb` client: `pip install singlestoredb` (or `pip install sqlalchemy-singlestoredb singlestoredb` to install both).","cause":"The underlying SingleStoreDB Python client library (`singlestoredb`) is not installed.","error":"ModuleNotFoundError: No module named 'singlestoredb'"},{"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.","cause":"The `sqlalchemy-singlestoredb` package, which registers the dialect with SQLAlchemy, is either not installed or its entry points are not properly recognized.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:singlestoredb"},{"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.","cause":"The username, password, host, or port provided in the connection string is incorrect, or the user lacks necessary permissions on the SingleStoreDB server.","error":"sqlalchemy.exc.OperationalError: (singlestoredb.exceptions.OperationalError) (1045, \"Access denied for user '...'@'%' (using password: YES)\")"},{"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.","cause":"The SingleStoreDB server is not running, is unreachable from the client's network, or the specified host/port is incorrect.","error":"sqlalchemy.exc.OperationalError: (singlestoredb.exceptions.OperationalError) (2003, \"Can't connect to MySQL server on '...' (timed out)\")"}]}