Getting Started¶

Database connections can be made using either keyword parameters or a URL as described in the following sections.

Connect using DB-API Parameters¶

Connections to SingleStoreDB can be made using the parameters described in the Python DB-API. The host= parameter can be either a hostname or IP address (it can also be a URL as shown in the following section). The port= parameter is an integer value of the database server port number. The user= and password= parameters specify the database user credentials. The database= parameter, optionally, specifies the name of the database to connect to.

A full list of connection parameters can be seen in the API documentation for the singlestoredb.connect() function.

In [1]: import singlestoredb as s2
In [2]: conn = s2.connect(host='...', port='...', user='...',
   ...:                   password='...', database='...')
   ...: 

Connect using a URL¶

In addition, you can user a URL like in the SQLAlchemy package.

In [3]: conn = s2.connect('user:password@host:port/database')

URLs work equally well to connect to the Data API.

In [4]: conn = s2.connect('https://user:password@host:port/database')

Specifying Additional Connection Parameters¶

Connection parameters can be set either in the URL or as parameters. Here local_infile= is set as a URL parameter.

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

In this example, local_infile= and user credentials are specified as keyword parameters.

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

Executing Queries¶

Once you have a connection established, you can query the database. As defined in the DB-API, a cursor is used to execute queries and fetch the results.

In [7]: with conn.cursor() as cur:
   ...:      cur.execute('show variables like "auto%"')
   ...:      for row in cur.fetchall():
   ...:          print(row)
   ...: 
('auto_attach', 'ON')
('auto_increment_increment', '1')
('auto_increment_offset', '1')
('auto_profile_type', 'LITE')
('auto_replicate', 'OFF')
('autocommit', 'ON')
('autostats_flush_interval_secs', '600')

Parameter Substitution¶

If your queries require parameter substitutions, they can be specified in one of two formats: named (%(name)s) or positional (%s).

Warning

As of v0.5.0, the substition parameter has been changed from :1, :2, etc. for list parameters and :foo, :bar, etc. for dictionary parameters to %s and %(foo)s, %(bar)s, etc. respectively, to ease the transition from other MySQL Python packages.

Named Substitution¶

When named parameters are used, the data structure passed to the Cursor.execute() method must be a dictionary, where the keys map to the names given in the substitutions and the values are the values to substitute.

In the example below, %(pattern)s is replaced with the value "auto%". All escaping and quoting of the substituted data values is done automatically.

In [8]: with conn.cursor() as cur:
   ...:     cur.execute('show variables like %(pattern)s', dict(pattern='auto%'))
   ...:     for row in cur.fetchall():
   ...:         print(row)
   ...: 
('auto_attach', 'ON')
('auto_increment_increment', '1')
('auto_increment_offset', '1')
('auto_profile_type', 'LITE')
('auto_replicate', 'OFF')
('autocommit', 'ON')
('autostats_flush_interval_secs', '600')

Positional Substitution¶

If positional parameters are used, the data structure passed to the Cursor.execute() method must be a list or tuple with the same number of elements as there are %s values in the query string.

In the example below, %s is replaced with the value "auto%". All escaping and quoting of the substituted data values is done automatically.

In [9]: with conn.cursor() as cur:
   ...:     cur.execute('show variables like %s', ['auto%'])
   ...:     for row in cur.fetchall():
   ...:         print(row)
   ...: 
('auto_attach', 'ON')
('auto_increment_increment', '1')
('auto_increment_offset', '1')
('auto_profile_type', 'LITE')
('auto_replicate', 'OFF')
('autocommit', 'ON')
('autostats_flush_interval_secs', '600')

Fetching Results¶

Fetching results can be done in a number of ways. The DB-API specifies three methods that can be used to fetch results: Cursor.fetchone(), Cursor.fetchall(), and Cursor.fetchmany().

The Cursor.fetchone() method fetches a single row of data returned by a query. The Cursor.fetchall() method fetches all of the results of a query. The Cursor.fetchmany() method fetches a specified number of rows to retrieve. The choice of which one to use depends mostly on the expected size of the result. If the result is expected to be fairly small, fetching the entire result in one call may be fine. However, if the query result will be large enough to put a strain on the client computer’s memory, it may be a better idea to fetch smaller batches using Cursor.fetchmany().

In additon to the DB-API methods for fetching results, a Cursor can be iterated over itself.

In [10]: with conn.cursor() as cur:
   ....:     cur.execute('show variables like "auto%"')
   ....:     for row in cur:
   ....:         print(row)
   ....: 
('auto_attach', 'ON')
('auto_increment_increment', '1')
('auto_increment_offset', '1')
('auto_profile_type', 'LITE')
('auto_replicate', 'OFF')
('autocommit', 'ON')
('autostats_flush_interval_secs', '600')

Result Type¶

In addition to being able to specify the amount of data to be retrieved, you can also specify the data structure that the results are returned in. By default, each row of data is a tuple with one element per column from the query. However, it is also possible to get results back as named tuples or dicts.

Tuples (Default)¶

In [11]: with s2.connect(results_type='tuples') as conn:
   ....:     with conn.cursor() as cur:
   ....:         cur.execute('show variables like "auto%"')
   ....:         for row in cur.fetchall():
   ....:             print(row)
   ....: 
('auto_attach', 'ON')
('auto_increment_increment', '1')
('auto_increment_offset', '1')
('auto_profile_type', 'LITE')
('auto_replicate', 'OFF')
('autocommit', 'ON')
('autostats_flush_interval_secs', '600')

Named Tuples¶

In [12]: with s2.connect(results_type='namedtuples') as conn:
   ....:     with conn.cursor() as cur:
   ....:         cur.execute('show variables like "auto%"')
   ....:         for row in cur.fetchall():
   ....:             print(row)
   ....: 
Row(Variable_name='auto_attach', Value='ON')
Row(Variable_name='auto_increment_increment', Value='1')
Row(Variable_name='auto_increment_offset', Value='1')
Row(Variable_name='auto_profile_type', Value='LITE')
Row(Variable_name='auto_replicate', Value='OFF')
Row(Variable_name='autocommit', Value='ON')
Row(Variable_name='autostats_flush_interval_secs', Value='600')

Dictionaries¶

In [13]: with s2.connect(results_type='dicts') as conn:
   ....:     with conn.cursor() as cur:
   ....:         cur.execute('show variables like "auto%"')
   ....:         for row in cur.fetchall():
   ....:             print(row)
   ....: 
{'Variable_name': 'auto_attach', 'Value': 'ON'}
{'Variable_name': 'auto_increment_increment', 'Value': '1'}
{'Variable_name': 'auto_increment_offset', 'Value': '1'}
{'Variable_name': 'auto_profile_type', 'Value': 'LITE'}
{'Variable_name': 'auto_replicate', 'Value': 'OFF'}
{'Variable_name': 'autocommit', 'Value': 'ON'}
{'Variable_name': 'autostats_flush_interval_secs', 'Value': '600'}