Connect using the SingleStore Python Client

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:

  Python

  Copy

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

• Specify the connection parameter in the connection string:

  Python

  Copy

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

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:

• 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

  Copy

  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

  Copy

  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

  Copy

  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)

Example 1

The following example connects a SingleStore database to Python and performs CRUD operations in the database using 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)

python3 pTest.py

True

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)

python3 pCTest.py

DESC testID;

+-------+------------+------+------+---------+-------+
| Field | Type       | Null | Key  | Default | Extra |
+-------+------------+------+------+---------+-------+
| ID    | int(11)    | NO   | PRI  | NULL    |       |
| Code  | varchar(4) | YES  |      | NULL    |       |
+-------+------------+------+------+---------+-------+

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)

python3 pRTest.py

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

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)

python3 pUTest.py

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

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)

python3 pDTest.py

(1, 'KorE')
(3, 'DanI')

Example 2: Using Substitutions

The following examples show how to use 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)

python3 pTest.py

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

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)

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.