Connect using the SingleStore Python Client

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

Note

To facilitate transition from existing MySQL clients, the parameter substitution syntax is changed starting from the client v0.5.0 from :1 for positional and :key for dictionary keys (named) to %s for positional and %(key)s for dictionary keys. See Execute Queries for more information.

Install the SingleStore Python Client

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

pip install singlestoredb

Connect to SingleStore

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

Connect using the Python Database API Parameters

To connect to a SingleStore cluster, use the following parameters:

  • host: hostname or IP address of the SingleStore cluster

  • 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 SingleStore 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)s)

  • Positional: (%s)

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)s with the value 'auto%':

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

Positional Substitutions

When using positional parameters, you must pass a list or a tuple type data structure to the Cursor.execute() function.

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

with conn.cursor() as cur:
cur.execute('show variables like %s', ['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_type 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 or dictionaries.

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

  • Tuples:

    with s2.connect(results_type='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_type='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_type='dict') as conn:
    with conn.cursor() as cur:
    cur.execute('show variables like "auto%"')
    for row in cur.fetchall():
    print(row)

API Reference

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

Data Type Conversion

The following table maps the SingleStore data types to the corresponding HTTP and Python data type. Empty cells indicate that you must cast that SingleStore 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='localhost'

  • port='3306'

  • user='root'

  • password='passkey'

  • database='dbTest'

  • results_type='tuple' (optional)

Example 1

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

Connect to SingleStore

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='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='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 a .py file (say pCTest.py):

import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest')
data = [
(1, "KorE"),
(2, "PamY"),
(3, "TabK"),
]
stmt = 'INSERT INTO testID (ID, Code) VALUES (%s, %s)'
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   | PRI  | NULL    |       |
| Code  | varchar(4) | YES  |      | NULL    |       |
+-------+------------+------+------+---------+-------+

Read Operation Example

Add the following code to a .py file (say pRTest.py):

import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='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 a .py file (say pUTest.py):

import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='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 a .py file (say pDTest.py):

import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='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 Positional Substitutions

The following example shows how to use positional substitutions:

import singlestoredb as s2
conn = s2.connect(host='localhost', port='3306', user='root', password='passkey', database='dbTest')
with conn:
conn.autocommit(True)
with conn.cursor() as cur:
cur.execute('INSERT INTO testID values(%s, %s)', [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='localhost', port='3306', user='root', password='passkey', database='dbTest')
with conn:
conn.autocommit(True)
with conn.cursor() as cur:
cur.execute('INSERT INTO testID (id, code) VALUES (%s, %s)', (4, '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 SingleStore cluster via Python using a URL. Add the following code to a .py file (say testURL.py):

import singlestoredb as s2
conn = s2.connect('root:passkey@localhost: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='localhost', port='3306', user='root', password='passkey', database='dbTest', results_type='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': 'RegG'}
{'ID': 4, 'Code': 'GarY'}
{'ID': 3, 'Code': 'DanI'}
{'ID': 1, 'Code': 'KorE'}

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

Last modified: August 13, 2024

Was this article helpful?