Connect using the SingleStoreDB Python Client

The SingleStoreDB Python client ("the client") is a Python Database API compliant database connector that can be used to connect to your SingleStoreDB database. The client also supports the SingleStoreDB Data API. It provides an interface to connect to SingleStoreDB Cloud workspaces. The client supports Python version 3.7+.

Install the SingleStoreDB Python Client

To install the client using pip, run the following command:

pip install singlestoredb

If you are using Anaconda, run the following command instead:

conda install -c singlestore singlestoredb

Connect to SingleStoreDB

You can connect to a SingleStoreDB database using keyword parameters or a URL.

Connect using the Python Database API Parameters

To connect to a SingleStoreDB workspace, use the following parameters:

  • host: hostname or IP address of the SingleStoreDB workspace

  • port: database server port

  • user: name of the database user

  • password: password of the database user

  • database (optional): name of the database

For a complete list of supported parameters, see singlestoredb.connect() Reference. Here's an example:

import singlestoredb as s2
conn = s2.connect(host='...', port='...', user='...',
                  password='...', database='...')

Connect using a URL

You can specify the URL in the connection parameters, as follows:

import singlestoredb as s2
conn = s2.connect('user:password@host:port/database')

You can also connect to the SingleStoreDB Data API, as follows:

import singlestoredb as s2
s2.connect('https://user:password@host:port/database')

You can specify the connection parameters either in the connection string or in the URL. The following examples show how to specify a connection parameter, say local_infile:

  • Specify the connection parameter in the URL:

    conn = s2.connect('https://user:password@host:port/database?local_infile=True')
  • Specify the connection parameter in the connection string:

    conn = s2.connect('https://host:port/database', user='...', password='...',local_infile=True)

Execute Queries

Once a database connection is established, you can use a cursor to execute queries. You can create a cursor using the Connection.cursor() function. See Cursors for a list of supported cursor functions.

If a query parameter requires substitution, it can be specified in the following two formats:

  • Named (:varname)

  • Numbered (:1)

The substituted data values are automatically quoted and escaped.

Named Substitutions

When using named substitutions, you must pass a dictionary type data structure to the Cursor.execute() function, where the keys map to the names specified in the substitution and the values specify the substitute value.

The following example substitutes :pattern with the value 'auto%':

with conn.cursor() as cur:
    cur.execute('show variables like :pattern', dict(pattern='auto%'))
    for row in cur.fetchall():
        print(row)

Numeric Substitutions

When using numbered parameters, you must pass a list or a tuple type data structure to the Cursor.execute() function, where the substitution number corresponds to the 1-based position in the list or tuple.

The following example substitutes :1 with the value 'auto%':

with conn.cursor() as cur:
    cur.execute('show variables like :1', ['auto%'])
    for row in cur.fetchall():
        print(row)

Fetch Results

The Python Database API provides three Cursor functions to fetch results:

Result Formats

You can specify the data structure format for the results using the results_format parameter. By default, each row of data is a tuple with one element per column from the query. You can also get the results as named tuples, dictionaries, or pandas.DataFrame.

Here's some examples to get results in a specific data format:

  • Tuples:

    with s2.connect(results_format='tuple') as conn:
        with conn.cursor() as cur:
            cur.execute('show variables like "auto%"')
            for row in cur.fetchall():
                print(row)
  • Named tuples:

    with s2.connect(results_format='namedtuple') as conn:
        with conn.cursor() as cur:
            cur.execute('show variables like "auto%"')
            for row in cur.fetchall():
                print(row)
  • Dictionaries:

    with s2.connect(results_format='dict') as conn:
        with conn.cursor() as cur:
            cur.execute('show variables like "auto%"')
            for row in cur.fetchall():
                print(row)
  • DataFrame:

    with s2.connect(results_format='dataframe') as conn:
        with conn.cursor() as cur:
            cur.execute('show variables like "auto%"')
            print(cur.fetchall())

API Reference

The SingleStoreDB Python Client API Reference lists the Python Database API objects, functions, and properties that the SingleStoreDB Python client supports. Here's some additional references:

Data Type Conversion

The following table maps the SingleStoreDB data types to the corresponding HTTP and Python data type. Empty cells indicate that you must cast that SingleStoreDB data type to another data type.

Type Name

Type Code

HTTP Type

Python Type

TINYINT

BOOL

BOOLEAN

1

TINYINT or UNSIGNED TINYINT

int

SMALLINT

2

SMALLINT or UNSIGNED SMALLINT

int

MEDIUMINT

INT24

9

MEDIUMINT

int

INT

INTEGER

3

INT or UNSIGNED INT

int

BIGINT

8

BIGINT or UNSIGNED BIGINT

int

FLOAT

4

FLOAT

float

DOUBLE

REAL

5

DOUBLE

float

DECIMAL

DEC

FIXED

NUMERIC

0

DECIMAL

decimal.Decimal

DATE

10

DATE

datetime.date

TIME

11

TIME

datetime.timedelta

TIME(6)

11

TIME

datetime.timedelta

DATETIME

12

DATETIME

datetime.datetime

DATETIME(6)

12

DATETIME

datetime.datetime

TIMESTAMP

7

TIMESTAMP

datetime.datetime

TIMESTAMP(6)

7

TIMESTAMP

datetime.datetime

YEAR

13

YEAR

int

CHAR(LEN)

254

CHAR

str

BINARY(LEN)

254

BINARY

bytes

VARCHAR

15 / 253

VARCHAR

str

VARBINARY

15 / 253

VARBINARY

bytes

LONGTEXT

251

LONGTEXT

str

MEDIUMTEXT

250

MEDIUMTEXT

str

TEXT

252

TEXT

str

TINYTEXT

249

TINYTEXT

str

LONGBLOB

251

LONGBLOB

bytes

MEDIUMBLOB

250

MEDIUMBLOB

bytes

BLOB

252

BLOB

bytes

TINYBLOB

249

TINYBLOB

bytes

JSON

245

JSON

dict / list

GEOGRAPHYPOINT

GEOGRAPHY

ENUM

247

ENUM

SET

248

SET

list[str]

BIT

16

BIT

bytes

Examples

We'll use the following connection parameters in the examples:

  • host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com'

  • port='3306'

  • user='admin'

  • password='passkey'

  • database='dbTest'

  • results_format='tuple' (optional)

Example 1

The following example connects a SingleStoreDB database to Python and performs CRUD operations in the database using Python.

Connect to SingleStoreDB

Create a new .py file (say pTest.py), and add the following code to test the connection:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest', results_format='tuple')

with conn:
    with conn.cursor() as cur:
        flag = cur.is_connected();
        print(flag)

Run the following command:

python3 pTest.py
****
True

Create Operation Example

Add the following code to pCTest.py file:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest')

data = [
  (1, "KorE"),
  (2, "PamY"),
  (3, "TabK"),
]
stmt = 'INSERT INTO testID (ID, Code) VALUES (:1, :2)'

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('CREATE TABLE testID (ID INT PRIMARY KEY, Code VARCHAR(4));')
        cur.executemany(stmt, data)

Run the following command:

python3 pCTest.py

This example executes the INSERT query against multiple sets of parameters using the Cursor.executemany() function.

Run the following command in your SQL tool:

DESC testID;
****
+-------+------------+------+------+---------+-------+
| Field | Type       | Null | Key  | Default | Extra |
+-------+------------+------+------+---------+-------+
| ID    | int(11)    | NO   | UNI  | NULL    |       |
| Code  | varchar(4) | YES  |      | NULL    |       |
+-------+------------+------+------+---------+-------+

Read Operation Example

Add the following code to pRTest.py file:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest', results_format='tuple')

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('SELECT * FROM testID')
        for row in cur.fetchall():
            print(row)

Run the following command:

python3 pRTest.py
****
(2, 'PamY')
(1, 'KorE')
(3, 'TabK')

Update Operation Example

Add the following code to pUTest.py file:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest', results_format='tuple')

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('UPDATE testID SET Code = "DanI" WHERE ID =3')
        cur.execute('SELECT * FROM testID')
        for row in cur.fetchall():
            print(row)

Run the following command:

python3 pUTest.py
****
(2, 'PamY')
(3, 'DanI')
(1, 'KorE')

Delete Operation Example

Add the following code to pDTest.py file:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest', results_format='tuple')

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('DELETE FROM testID WHERE ID = 2')
        cur.execute('SELECT * FROM testID')
        for row in cur.fetchall():
            print(row)

Run the following command:

python3 pDTest.py
****
(1, 'KorE')
(3, 'DanI')

Example 2: Using Substitutions

The following examples show how to use substitutions.

Using Numeric Substitutions

The following example shows how to use numeric substitutions:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest')

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('INSERT INTO testID values(:1, :2)', [2, 'RegG'])
        cur.execute('SELECT * FROM testID')
        for row in cur.fetchall():
            print(row)

Run the following command:

python3 pTest.py
****
(2, 'RegG')
(1, 'KorE')
(3, 'DanI')

Using Named Substitutions

The following example shows how to use named substitutions:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest')

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('INSERT INTO testID values(:id, :code)', dict(id=4, code='GarY'))
        cur.execute('SELECT * FROM testID')
        for row in cur.fetchall():
            print(row)

Run the following command:

python3 pTest.py
****
(4, 'GarY')
(2, 'RegG')
(1, 'KorE')
(3, 'DanI')

Example 3: Connect using URL

The following example shows how to connect to a SingleStoreDB workspace via Python using a URL. Add the following code to the testURL.py file:

import singlestoredb as s2
conn = s2.connect('admin:passkey@svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com:3306/dbTest')

with conn:
    with conn.cursor() as cur:
        flag = cur.is_connected();
        print(flag)

Run the following command to test the connection:

python3 testURL.py
****
True

Example 4: Specify Result Format

The following example returns the result in the dictionary format. Add the following code to the testFormat.py file:

import singlestoredb as s2
conn = s2.connect(host='svc-b093fe56-7d6e-499f-b960-7913862facc4-ddl.aws-oregon-2.svc.singlestore.com ', port='3306', user='admin', password='passkey', database='dbTest', results_format='dict')

with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute('SELECT * FROM testID')
        for row in cur.fetchall():
            print(row)

Run the following command:

python3 testFormat.py
****
{'ID': 2, 'Code': 'PamY'}
{'ID': 1, 'Code': 'KorE'}
{'ID': 3, 'Code': 'TabK'}

You can specify other supported formats using the results_format parameter as shown in this example.