API Reference

Connections

The connect() function is the primary entry point for the SingleStore package. It connects to a SingleStore database using either DB-API compliant parameters, or a connection string in the form of a URL.

The create_engine() function is used with the SQLAlchemy package to create an SQLAlchemy engine for SingleStoreDB connections. This is primarily for use in environments where the connection parameters are stored in environment variables so that you can create SingleStoreDB connections without specifying any parameters in the code itself.

connect([host, user, password, port, ...])	Return a SingleStoreDB connection.
create_engine(*args, **kwargs)	Create an SQLAlchemy engine for SingleStoreDB.

Connection

Connection objects are created by the singlestoredb.connect() function. They are used to create Cursor objects for querying the database.

Connection(**kwargs)	SingleStoreDB connection.
Connection.autocommit([value])	Set autocommit mode.
Connection.close()	Close the database connection.
Connection.commit()	Commit the pending transaction.
Connection.rollback()	Rollback the pending transaction.
Connection.cursor()	Create a new cursor object.
Connection.is_connected()	Determine if the database is still connected.
Connection.enable_data_api([port])	Enable the data API in the server.
Connection.disable_data_api()	Disable the data API.

The Connection.show attribute of the connection objects allow you to access various information about the server. The available operations are shown below.

Connection.show.aggregates()	Show all aggregate functions in the current database.
Connection.show.columns(table[, full])	Show the column information for the given table.
Connection.show.create_aggregate(name)	Show the function creation code for the given aggregate function.
Connection.show.create_function(name)	Show the function creation code for the given function.
Connection.show.create_pipeline(name[, extended])	Show the pipeline creation code for the given pipeline.
Connection.show.create_table(name)	Show the table creation code for the given table.
Connection.show.create_view(name)	Show the view creation code for the given view.
Connection.show.databases([extended])	Show all databases in the server.
Connection.show.database_status()	Show status of the current database.
Connection.show.errors()	Show errors.
Connection.show.functions()	Show all functions in the current database.
Connection.show.global_status()	Show global status of the current server.
Connection.show.indexes(table)	Show all indexes in the given table.
Connection.show.partitions([extended])	Show partitions in the current database.
Connection.show.pipelines()	Show all pipelines in the current database.
Connection.show.plan(plan_id[, json])	Show the plan for the given plan ID.
Connection.show.plancache()	Show all query statements compiled and executed.
Connection.show.procedures()	Show all procedures in the current database.
Connection.show.processlist()	Show details about currently running threads.
Connection.show.reproduction([outfile])	Show troubleshooting data for query optimizer and code generation.
Connection.show.schemas()	Show schemas in the server.
Connection.show.session_status()	Show server status information for a session.
Connection.show.status([extended])	Show server status information.
Connection.show.table_status()	Show table status information for the current database.
Connection.show.tables([extended])	Show tables in the current database.
Connection.show.warnings()	Show warnings.

ShowResult

The results of the above methods and attributes are in the form of a ShowResult object. This object is primarily used to display information to the screen or web browser, but columns from the output can also be accessed using dictionary-like key access syntax or attributes.

ShowResult(*args, **kwargs)

Simple result object.

Cursor

Cursors are used to query the database and download results. They are created using the Connection.cursor() method.

Cursor(connection)	Database cursor for submitting commands and queries.
Cursor.callproc(name[, params])	Call a stored procedure.
Cursor.close()	Close the cursor.
Cursor.execute(query[, args, infile_stream])	Execute a SQL statement.
Cursor.executemany(query[, args])	Execute SQL code against multiple sets of parameters.
Cursor.fetchone()	Fetch a single row from the result set.
Cursor.fetchmany([size])	Fetch size rows from the result.
Cursor.fetchall()	Fetch all rows in the result set.
Cursor.nextset()	Skip to the next available result set.
Cursor.setinputsizes(sizes)	Predefine memory areas for parameters.
Cursor.setoutputsize(size[, column])	Set a column buffer size for fetches of large columns.
Cursor.scroll(value[, mode])	Scroll the cursor to the position in the result set.
Cursor.next()	Return the next row from the result set for use in iterators.
Cursor.is_connected()	Is the cursor still connected?

Uploading from streaming sources

While the standard way of loading a local file using LOAD DATA LOCAL INFILE is well-known, it is possible to load data from programmatic sources as well. This means that you can generate data with a Python function and load that data into the database without writing it to a file first. Below are two methods of doing this. Both of them use the local_infile=true parameter on the connection and the infile_stream= option on the cursor.execute method.

Uploading data using a generator function

conn = s2.connect('s2-host.com/my_db?local_infile=true')
cur = conn.cursor()
def upload_csv():
    yield '1,2,3\n'
    yield '4,5,6\n'
    yield '7,8,9\n'
    # Note that the data returned does not have to
    # correspond to a single row. Any length of
    # data can be returned.
    yield '10,11,12\n13,14,15\n'
cur.execute('CREATE TABLE generator (a INT, b INT, c INT)')
cur.execute(
    '''
    LOAD DATA LOCAL INFILE ':stream:' INTO TABLE generator
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ''',
    infile_stream=upload_csv(),
)

Uploading data using a queue and threads

from queue import Queue
from threading import Thread
conn = s2.connect('s2-host.com/my_db?local_infile=true')
cur = conn.cursor()
data_feeder = Queue()
cur.execute('CREATE TABLE queue (a INT, b INT, c INT)')
t = Thread(
    target=cur.execute,
    args=(
        '''
        LOAD DATA LOCAL INFILE ':stream:' INTO TABLE queue
            FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        ''',
    ),
    kwargs=dict(infile_stream=data_feeder),
)
t.start()
data_feeder.put('1,2,3\n')
data_feeder.put('4,5,6\n')
data_feeder.put('7,8,9\n')
# Note that the data sent does not have to
# correspond to a single row. Any length of
# data can be sent.
data_feeder.put('10,11,12\n13,14,15\n')
# Send an empty string to end the data loader.
data_feeder.put('')
t.join()

Utilities

get_jwt(email[, url, clusters, databases, ...])

Retrieve a JWT token from the SingleStoreDB single-sign-on URL.

Management API

The management objects allow you to create, destroy, and interact with workspaces in the SingleStoreDB Cloud.

The manage_workspaces() function will return a WorkspaceManager object that can be used to interact with the Management API.

manage_workspaces([access_token, version, ...])

Retrieve a SingleStoreDB workspace manager.

WorkspaceManager

WorkspaceManager objects are returned by the manage_workspaces() function. They allow you to retrieve information about workspaces in your account, or create new ones.

WorkspaceManager([access_token, version, ...])	SingleStoreDB workspace manager.
WorkspaceManager.organization	Return the current organization.
WorkspaceManager.workspace_groups	Return a list of available workspace groups.
WorkspaceManager.regions	Return a list of available regions.
WorkspaceManager.create_workspace_group(...)	Create a new workspace group.
WorkspaceManager.create_workspace(name, ...)	Create a new workspace.
WorkspaceManager.get_workspace_group(id)	Retrieve a workspace group definition.
WorkspaceManager.get_workspace(id)	Retrieve a workspace definition.

WorkspaceGroup

WorkspaceGroup objects are retrieved from WorkspaceManager.get_workspace_group() or by retrieving an element from WorkspaceManager.workspace_groups.

WorkspaceGroup(name, id, created_at, region, ...)	SingleStoreDB workspace group definition.
WorkspaceGroup.workspaces	Return a list of available workspaces.
WorkspaceGroup.stage	Stage manager.
WorkspaceGroup.create_workspace(name[, ...])	Create a new workspace.
WorkspaceGroup.refresh()	Update the object to the current state.
WorkspaceGroup.update([name, ...])	Update the workspace group definition.
WorkspaceGroup.terminate([force, ...])	Terminate the workspace group.

Workspace

Workspaces are created within WorkspaceGroups. They can be created using either WorkspaceGroup.create_workspace() or retrieved from WorkspaceManager.workspaces.

Workspace(name, workspace_id, ...[, ...])	SingleStoreDB workspace definition.
Workspace.connect(**kwargs)	Create a connection to the database server for this workspace.
Workspace.refresh()	Update the object to the current state.
Workspace.update([auto_suspend, ...])	Update the workspace definition.
Workspace.terminate([wait_on_terminated, ...])	Terminate the workspace.

Region

Region objects are accessed from the WorkspaceManager.regions attribute.

Region(id, name, provider)

Cluster region information.

Stage Files

To interact with files in your Stage, use the WorkspaceManager.stage attribute. It will return a Stage object which defines the following methods and attributes.

Stage(deployment_id, manager)	Stage manager.
Stage.open(stage_path[, mode, encoding])	Open a Stage path for reading or writing.
Stage.download_file(stage_path[, ...])	Download the content of a stage path.
Stage.download_folder(stage_path[, ...])	Download a Stage folder to a local directory.
Stage.upload_file(local_path, stage_path, *)	Upload a local file.
Stage.upload_folder(local_path, stage_path, *)	Upload a folder recursively.
Stage.info(stage_path)	Return information about a stage location.
Stage.listdir([stage_path, recursive])	List the files / folders at the given path.
Stage.exists(stage_path)	Does the given stage path exist?
Stage.is_dir(stage_path)	Is the given stage path a directory?
Stage.is_file(stage_path)	Is the given stage path a file?
Stage.mkdir(stage_path[, overwrite])	Make a directory in the stage.
Stage.rename(old_path, new_path, *[, overwrite])	Move the stage file to a new location.
Stage.remove(stage_path)	Delete a stage location.
Stage.removedirs(stage_path)	Delete a stage folder recursively.
Stage.rmdir(stage_path)	Delete a stage folder.

Personal, Shared, and Model Files

To manage personal files, shared files, and model files in an organization, you use the singlestoredb.manage_files() function. This will return a FilesManager instance to access each location. You then use the returned FileSpace to manage the files in each location. These classes are described below.

manage_files([access_token, version, ...])

Retrieve a SingleStoreDB files manager.

The FilesManager gives you access to each of the personal, shared, and model file locations.

FilesManager([access_token, version, ...])	SingleStoreDB files manager.
FilesManager.personal_space	Return the personal file space.
FilesManager.shared_space	Return the shared file space.
FilesManager.models_space	Return the models file space.

The FileSpace contains the methods for interacting with the personal, shared, or model files.

FileSpace(location, manager)	FileSpace manager.
FileSpace.open(path[, mode, encoding])	Open a file path for reading or writing.
FileSpace.download_file(path[, local_path, ...])	Download the content of a file path.
FileSpace.download_folder(path[, ...])	Download a FileSpace folder to a local directory.
FileSpace.upload_file(local_path, path, *[, ...])	Upload a local file.
FileSpace.upload_folder(local_path, path, *)	Upload a folder recursively.
FileSpace.info(path)	Return information about a file location.
FileSpace.listdir([path, recursive])	List the files / folders at the given path.
FileSpace.exists(path)	Does the given file path exist?
FileSpace.is_dir(path)	Is the given file path a directory?
FileSpace.is_file(path)	Is the given file path a file?
FileSpace.mkdir(path[, overwrite])	Make a directory in the file space.
FileSpace.rename(old_path, new_path, *[, ...])	Move the file to a new location.
FileSpace.remove(path)	Delete a file location.
FileSpace.removedirs(path)	Delete a folder recursively.
FileSpace.rmdir(path)	Delete a folder.

FilesObject

FilesObject`s are returned by the :meth:`StageObject.upload_file FilesObject.upload_folder(), FilesObject.mkdir(), FilesObject.rename(), and FilesObject.info() methods.

FilesObject(name, path, size, type, format, ...)	File / folder object.
FilesObject.open([mode, encoding])	Open a file path for reading or writing.
FilesObject.download([local_path, ...])	Download the content of a file path.
FilesObject.exists()	Does the file / folder exist?
FilesObject.is_dir()	Is the object a directory?
FilesObject.is_file()	Is the object a file?
FilesObject.abspath()	Return the full path of the object.
FilesObject.basename()	Return the basename of the object.
FilesObject.dirname()	Return the directory name of the object.
FilesObject.getmtime()	Return the last modified datetime as a UNIX timestamp.
FilesObject.getctime()	Return the creation datetime as a UNIX timestamp.
FilesObject.rename(new_path, *[, overwrite])	Move the file to a new location.
FilesObject.remove()	Delete the file.
FilesObject.removedirs()	Delete the directory recursively.
FilesObject.rmdir()	Delete the empty directory.

Notebook Tools

The SDK includes a notebook sub-module for tools that are for use in the SingleStore Managed Service Portal Notebooks environment. The following objects in sinlgestoredb.notebook are singletons that automatically track the organization, workspace group, workspace, stage, and secrets that are selected in the portal.

These objects act just like the corresponding objects discussed in previous of this documentation (including attributes and methods), but they proxy all calls to the currently selected portal services.

organization	Organization in SingleStoreDB Cloud portal.
secrets	Wrapper for accessing secrets as object attributes.
workspace_group	SingleStoreDB workspace group definition.
stage	Stage manager.
workspace	SingleStoreDB workspace definition.

Server Tools

It is possible to start a SingleStoreDB server from the Python SDK in a couple of ways: Docker or Cloud Free Tier. Both of these methods are described below.

Docker

If you have Docker installed on your machine, you can use the Docker interface included with the Python SDK to start a SingleStoreDB server in Docker. This allows you to open interactive shells, SQL Studio, and also use the %sql magic commands from Jupysql with a SingleStoreDB server running in a container.

An example of starting SingleStoreDB in Docker is shown below.

from singlestoredb.server import docker
s2db = docker.start()
with s2db.connect() as conn:
    with conn.cursor() as cur:
        cur.execute('SHOW DATABASES')
        for line in cur:
            print(line)
s2db.stop()

It is possible to use the server instance as a context manager as well. This will automatically shut down the container after exiting the with block.

from singlestoredb.server import docker
with docker.start() as s2db:
    with s2db.connect() as conn:
        with conn.cursor() as cur:
            cur.execute('SHOW DATABASES')
            for line in cur:
                print(line)

If you do not explicitly shut down the container, it will get shut down when the Python process exits.

start([name, password, license, enable_kai, ...])	Manager for SingleStoreDB server running in Docker.
SingleStoreDB.logs()
SingleStoreDB.connect([use_data_api])	Connect to the SingleStoreDB server.
SingleStoreDB.connect_kai()	Connect to the Kai (MongoDB) server.
SingleStoreDB.connection_url	Connection URL for the SingleStoreDB server.
SingleStoreDB.http_connection_url	HTTP Connection URL for the SingleStoreDB server.
SingleStoreDB.kai_url	Connection URL for the Kai (MongoDB) server.
SingleStoreDB.studio_url	URL for the SingleStoreDB Studio.
SingleStoreDB.open_studio()	Open the SingleStoreDB Studio in a web browser.
SingleStoreDB.open_shell()	Open a shell in the SingleStoreDB server.
SingleStoreDB.open_mongosh()	Open a mongosh in the SingleStoreDB server.
SingleStoreDB.stop(*args)	Stop the SingleStoreDB server.

Cloud Free Tier

The Cloud Free Tier interface works much in the same way as the Docker interface, although it doesn’t support as many features. To start a server in the Cloud Free Tier, you use the following:

from singlestoredb.server import free_tier
s2db = free_tier.start()
with s2db.connect() as conn:
    with conn.cursor() as cur:
        cur.execute('SHOW DATABASES')
        for line in cur:
            print(line)
s2db.stop()

Just as with the Docker interface, you can also use a Python context manager to automatically shut down your connection.

start()	Manager for SingleStoreDB server running in Docker.
SingleStoreDB.connect([use_data_api])	Connect to the SingleStoreDB server.
SingleStoreDB.connection_url	Connection URL for the SingleStoreDB server.
SingleStoreDB.http_connection_url	HTTP Connection URL for the SingleStoreDB server.
SingleStoreDB.kai_url	Connection URL for the Kai (MongoDB) server.
SingleStoreDB.open_shell()	Open a shell in the SingleStoreDB server.
SingleStoreDB.open_mongosh()	Open a mongosh in the SingleStoreDB server.
SingleStoreDB.stop(*args)	Stop the SingleStoreDB server.

Configuration

The following functions are used to get and set package configuration settings. Execute the describe_option() function with no parameters to see the documentation for all options.

get_option(key)	Get the value of an option.
set_option(*args, **kwargs)	Set the value of an option.
describe_option(*keys, **kwargs)	Print the description of one or more options.

In addition to the function above, you can access options through the singlestoredb.options object. This gives you attribute-like access to the option values.

In [1]: import singlestoredb as s2
In [2]: s2.describe_option('local_infile')
local_infile : bool
    Should it be possible to load local files?
    [default: False] [currently: False]
In [3]: s2.options.local_infile
Out[3]: False
In [4]: s2.options.local_infile = True
In [5]: s2.describe_option('local_infile')
local_infile : bool
    Should it be possible to load local files?
    [default: False] [currently: True]