.. module:: singlestoredb
The :func:`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 :func:`vector_db` function gives you an interface for working with an API better for working with vector data.
The :func:`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.
.. autosummary:: :toctree: generated/ connect vector_db create_engine
Connection objects are created by the :func:`singlestoredb.connect` function. They are used to create :class:`Cursor` objects for querying the database.
.. currentmodule:: singlestoredb.connection
.. autosummary:: :toctree: generated/ Connection Connection.autocommit Connection.close Connection.commit Connection.rollback Connection.cursor Connection.is_connected Connection.enable_data_api Connection.disable_data_api Connection.vector_db
The :attr:`Connection.show` attribute of the connection objects allow you to access various information about the server. The available operations are shown below.
.. currentmodule:: singlestoredb.connection
.. autosummary:: :toctree: generated/ ShowAccessor.aggregates ShowAccessor.columns ShowAccessor.create_aggregate ShowAccessor.create_function ShowAccessor.create_pipeline ShowAccessor.create_table ShowAccessor.create_view ShowAccessor.databases ShowAccessor.database_status ShowAccessor.errors ShowAccessor.functions ShowAccessor.global_status ShowAccessor.indexes ShowAccessor.partitions ShowAccessor.pipelines ShowAccessor.plan ShowAccessor.plancache ShowAccessor.procedures ShowAccessor.processlist ShowAccessor.reproduction ShowAccessor.schemas ShowAccessor.session_status ShowAccessor.status ShowAccessor.table_status ShowAccessor.tables ShowAccessor.warnings
The results of the above methods and attributes are in the form of a :class:`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.
.. currentmodule:: singlestoredb.connection
.. autosummary:: :toctree: generated/ ShowResult
Cursors are used to query the database and download results. They are created using the :meth:`Connection.cursor` method.
.. currentmodule:: singlestoredb.connection
.. autosummary:: :toctree: generated/ Cursor Cursor.callproc Cursor.close Cursor.execute Cursor.executemany Cursor.fetchone Cursor.fetchmany Cursor.fetchall Cursor.nextset Cursor.setinputsizes Cursor.setoutputsize Cursor.scroll Cursor.next Cursor.is_connected
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.
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(),
)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().. currentmodule:: singlestoredb.auth
.. autosummary:: :toctree: generated/ get_jwt
The management objects allow you to create, destroy, and interact with workspaces in the SingleStoreDB Cloud.
The :func:`manage_workspaces` function will return a :class:`WorkspaceManager` object that can be used to interact with the Management API.
.. currentmodule:: singlestoredb
.. autosummary:: :toctree: generated/ manage_workspaces
WorkspaceManager objects are returned by the :func:`manage_workspaces` function. They allow you to retrieve information about workspaces in your account, or create new ones.
.. currentmodule:: singlestoredb.management.workspace
.. autosummary:: :toctree: generated/ WorkspaceManager WorkspaceManager.organization WorkspaceManager.workspace_groups WorkspaceManager.starter_workspaces WorkspaceManager.regions WorkspaceManager.shared_tier_regions WorkspaceManager.create_workspace_group WorkspaceManager.create_workspace WorkspaceManager.create_starter_workspace WorkspaceManager.get_workspace_group WorkspaceManager.get_workspace WorkspaceManager.get_starter_workspace
WorkspaceGroup objects are retrieved from :meth:`WorkspaceManager.get_workspace_group` or by retrieving an element from :attr:`WorkspaceManager.workspace_groups`.
.. autosummary:: :toctree: generated/ WorkspaceGroup WorkspaceGroup.workspaces WorkspaceGroup.stage WorkspaceGroup.create_workspace WorkspaceGroup.refresh WorkspaceGroup.update WorkspaceGroup.terminate
Workspaces are created within WorkspaceGroups. They can be created using either :meth:`WorkspaceGroup.create_workspace` or retrieved from :attr:`WorkspaceManager.workspaces`.
.. autosummary:: :toctree: generated/ Workspace Workspace.connect Workspace.refresh Workspace.update Workspace.terminate
Region objects are accessed from the :attr:`WorkspaceManager.regions` attribute.
.. currentmodule:: singlestoredb.management.region
.. autosummary:: :toctree: generated/ Region
To interact with files in your Stage, use the :attr:`WorkspaceGroup.stage` attribute. It will return a :class:`Stage` object which defines the following methods and attributes.
.. currentmodule:: singlestoredb.management.workspace
.. autosummary:: :toctree: generated/ Stage Stage.open Stage.download_file Stage.download_folder Stage.upload_file Stage.upload_folder Stage.info Stage.listdir Stage.exists Stage.is_dir Stage.is_file Stage.mkdir Stage.rename Stage.remove Stage.removedirs Stage.rmdir
To manage personal files, shared files, and model files in an organization, you use the :func:`singlestoredb.manage_files` function. This will return a :class:`FilesManager` instance to access each location. You then use the returned :class:`FileSpace` to manage the files in each location. These classes are described below.
.. currentmodule:: singlestoredb
.. autosummary:: :toctree: generated/ manage_files
The :class:`FilesManager` gives you access to each of the personal, shared, and model file locations.
.. currentmodule:: singlestoredb.management.files
.. autosummary:: :toctree: generated/ FilesManager FilesManager.personal_space FilesManager.shared_space FilesManager.models_space
The :class:`FileSpace` contains the methods for interacting with the personal, shared, or model files.
.. currentmodule:: singlestoredb.management.files
.. autosummary:: :toctree: generated/ FileSpace FileSpace.open FileSpace.download_file FileSpace.download_folder FileSpace.upload_file FileSpace.upload_folder FileSpace.info FileSpace.listdir FileSpace.exists FileSpace.is_dir FileSpace.is_file FileSpace.mkdir FileSpace.rename FileSpace.remove FileSpace.removedirs FileSpace.rmdir
:class:`FilesObject`s are returned by the :meth:`StageObject.upload_file` :meth:`FilesObject.upload_folder`, :meth:`FilesObject.mkdir`, :meth:`FilesObject.rename`, and :meth:`FilesObject.info` methods.
.. currentmodule:: singlestoredb.management.workspace
.. autosummary:: :toctree: generated/ FilesObject FilesObject.open FilesObject.download FilesObject.exists FilesObject.is_dir FilesObject.is_file FilesObject.abspath FilesObject.basename FilesObject.dirname FilesObject.getmtime FilesObject.getctime FilesObject.rename FilesObject.remove FilesObject.removedirs FilesObject.rmdir
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.
.. currentmodule:: singlestoredb.notebook
.. autosummary:: :toctree: generated/ organization secrets workspace_group stage workspace
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.
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.
.. currentmodule:: singlestoredb.server.docker
.. autosummary:: :toctree: generated/ start SingleStoreDB.logs SingleStoreDB.connect SingleStoreDB.connect_kai SingleStoreDB.connection_url SingleStoreDB.http_connection_url SingleStoreDB.kai_url SingleStoreDB.studio_url SingleStoreDB.open_studio SingleStoreDB.open_shell SingleStoreDB.open_mongosh SingleStoreDB.stop
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.
.. currentmodule:: singlestoredb.server.free_tier
.. autosummary:: :toctree: generated/ start SingleStoreDB.connect SingleStoreDB.connection_url SingleStoreDB.http_connection_url SingleStoreDB.kai_url SingleStoreDB.open_shell SingleStoreDB.open_mongosh SingleStoreDB.stop
The following functions are used to get and set package configuration settings. Execute the :func:`describe_option` function with no parameters to see the documentation for all options.
.. currentmodule:: singlestoredb
.. autosummary:: :toctree: generated/ get_option set_option describe_option
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.
.. ipython:: python
import singlestoredb as s2
s2.describe_option('local_infile')
s2.options.local_infile
s2.options.local_infile = True
s2.describe_option('local_infile')
.. ipython:: python :suppress: s2.options.local_infile = False