DB Software v7.8

Connect with Standard Python

You can get started with SingleStore (formerly MemSQL) and Python quickly. SingleStore is wire-compliant with MySQL, meaning connecting to SingleStore is as easy as connecting to a MySQL database.

The sections below describe how to connect your application.

Prerequisites

Connection Details

To connect your Python application to your SingleStore cluster (SingleStore DB or SingleStore Managed Service) you'll need the following:

  • Host: the endpoint (SingleStore Managed Service) or IP Address (SingleStore DB) for your cluster. You can find the endpoint in the SingleStore Portal.

  • Port: default is 3306

  • User: admin (SingleStore Managed Service) or root (SingleStore DB).

  • Password

  • Database: name of the database to connect.

Here is an example of a Python connection for a SingleStore Managed Service cluster:

# SingleStore Managed Service
import pymysql

conn = pymysql.connect(
    user='admin',
    password='<your password here>',
    host='svc-bb7a5b18-6123-47f8-a5fb-ccbe9c75d237-ddl.aws-oregon-1.svc.singlestore.com',
    port=3306,
    database='test')

conn.close()

Here is an example of a Python connection for a SingleStore DB cluster:

# SingleStore DB
import pymysql

conn = pymysql.connect(
    user='root',
    password='<your password here>',
    host='127.0.0.1',
    port=3306,
    database='test')

conn.close()

Auto-Commit and Stored Procedures

Please note that if you are using stored procedures with PyMySQL or mysqlclient, you must enable auto-commit. Both of these client libraries disable auto-commit by default. This can lead to unexpected results when comparing client behavior to an IDE or the MySQL CLI (which just uses the server default which for SingleStore is enabled).

When auto-commit is enabled:

  • Calling a stored procedure which does not explicitly use START TRANSACTION/COMMIT will not be atomic (i.e. each query in the stored procedure will execute in separate transactions).

  • A stored procedure can use START TRANSACTION/COMMIT to ensure that multiple operations execute atomically.

When auto-commit is disabled:

  • Effectively everything will be in a running transaction. For any changes to be committed you, or the client (PyMySQL, etc.) needs to run COMMIT periodically.

  • Since everything runs in a transaction, running any operations against multiple databases will not work since SingleStore does not support cross-database transactions.

Here's an example that shows how to create your connection and enable auto-commit in PyMySQL and mysqlclient:

import pymysql.cursors
import MySQLdb

def test(dbapi):
    conn = dbapi.connect(
        host='127.0.0.1', user='root', password='p',
        database='mydb', charset="utf8", autocommit=True)
    with conn:
        with conn.cursor() as cursor:
            print(cursor.execute("call myproc()"))

test(pymysql)
test(MySQLdb)
In this section: