# Connect using the SingleStore Python Client

The SingleStore Python client ("the client") is a [Python Database API](https://peps.python.org/pep-0249/) compliant database connector that can be used to connect to your SingleStore database. The client also supports the [SingleStore Data API](https://docs.singlestore.com/db/v9.1/reference/data-api.md). It provides an interface to connect to SingleStore clusters. The client supports Python version 3.9+.

> **📝 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](https://docs.singlestore.com/#section-idm4572746330209633133147633943.md) for more information.

## Install the SingleStore Python Client

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

```python
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. Default is `3306`.
* `user`: Username of the SingleStore database user.
* `password`: Password for the SingleStore database user.
* `database` (optional): Name of the SingleStore database to connect with.

For a complete list of supported parameters, see [singlestoredb.connect() Reference](https://singlestoredb-python.labs.singlestore.com/generated/singlestoredb.connect.html). Here's an example:

```python
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:

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

You can also connect to the [SingleStore Data API](https://docs.singlestore.com/db/v9.1/reference/data-api.md), as follows:

```python
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:
  ```python
  conn = s2.connect('https://user:password@host:port/database?local_infile=True')
  ```
* Specify the connection parameter in the connection string:
  ```python
  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](https://singlestoredb-python.labs.singlestore.com/api.html#cursor) 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%'`:

```python
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%'`:

```python
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:

* `Cursor.fetchone()`: Use this function to fetch a single row from the result set.
* `Cursor.fetchall()`: Use this function to fetch all rows in a result set.
* `Cursor.fetchmany([size])`: Use this function to fetch a specified number of rows.

## 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:
  ```python
  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:
  ```python
  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:
  ```python
  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](https://singlestoredb-python.labs.singlestore.com/api.html) lists the Python Database API objects, functions, and properties that the SingleStore Python client supports. Here's some additional references:

* [Connection Objects](https://singlestoredb-python.labs.singlestore.com/api.html#connection)
* [Cursor Objects](https://singlestoredb-python.labs.singlestore.com/api.html#cursor)
* [Configuration Objects](https://singlestoredb-python.labs.singlestore.com/api.html#configuration)

## Data Type Conversion

The following table maps the [SingleStore data types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) to the corresponding HTTP and Python data type. Empty cells indicate that you must cast that SingleStore data type to another data type.

| **SingleStoreType**                              | **Type Code**                                  | **HTTP Type**                 | **Python Type**    |
| ------------------------------------------------ | ---------------------------------------------- | ----------------------------- | ------------------ |
| TINYINTBOOLBOOLEAN                               | 1                                              | TINYINT or UNSIGNED TINYINT   | int                |
| SMALLINT                                         | 2                                              | SMALLINT or UNSIGNED SMALLINT | int                |
| MEDIUMINTINT24                                   | 9                                              | MEDIUMINT                     | int                |
| INTINTEGER                                       | 3                                              | INT or UNSIGNED INT           | int                |
| BIGINT                                           | 8                                              | BIGINT or UNSIGNED BIGINT     | int                |
| FLOAT                                            | 4                                              | FLOAT                         | float              |
| DOUBLEREAL                                       | 5                                              | DOUBLE                        | float              |
| DECIMALDECFIXEDNUMERIC                           | 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              |
| BSON                                             | 251                                            | LONGBLOB                      | bytes              |
| VECTOR                                           | If`vector_type_project_format`is set to`JSON`: |                               |                    |
| 253                                              | VARCHAR                                        | str                           |                    |
| If`vector_type_project_format`is set to`BINARY`: |                                                |                               |                    |
| 253                                              | BINARY                                         | bytes                         |                    |

Refer to [List of Engine Variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for information on `vector_type_project_format`. To distinguish `VECTOR` and `BSON` data types from `LONGBLOB` and `VARCHAR`/`BINARY`, refer to [Extended Protocol Packet Metadata](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/extended-protocol-packet-metadata.md). 

## 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:

```python
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:

```shell
python3 pTest.py

```

```output

True
```

## Create Operation Example

Add the following code to a `.py` file (say **pCTest.py**):

```python
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:

```shell
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:

```sql
DESC testID;

```

```output

+-------+------------+------+------+---------+-------+
| 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**):

```python
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:

```shell
python3 pRTest.py

```

```output

(2, 'PamY')
(1, 'KorE')
(3, 'TabK')
```

## Update Operation Example

Add the following code to a `.py` file (say **pUTest.py**):

```python
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:

```shell
python3 pUTest.py

```

```output

(2, 'PamY')
(3, 'DanI')
(1, 'KorE')
```

## Delete Operation Example

Add the following code to a `.py` file (say **pDTest.py**):

```python
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:

```shell
python3 pDTest.py

```

```output

(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:

```python
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:

```shell
python3 pTest.py

```

```output

(2, 'RegG')
(1, 'KorE')
(3, 'DanI')
```

## Using Named Substitutions

The following example shows how to use named substitutions:

```python
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:

```shell
python3 pTest.py

```

```output

(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**):

```python
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:

```shell
python3 testURL.py

```

```output

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:

```python
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:

```shell
python3 testFormat.py

```

```output

{'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.

***

Modified at: October 31, 2025

Source: [/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestore-python-client/](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestore-python-client/)

(An index of the documentation is available at /llms.txt)