SingleStore DB

Python Client Library Setup Instructions

The following instructions are intended for a Red Hat Enterprise Linux Based Distribution, but the concepts may be applied elsewhere.

The first step is to install a Python client library. There are a few choices depending on your requirements.

Note: When using the GSSAPI authentication functionality, you must use a library that links against the MariaDB 10.1.11 or greater C bindings.

mysqlclient

mysqlclient-python

pip install mysqlclient

Recommended for SQLAlchemy users.

Code Sample for GSSAPI Authentication

The following section details the use of GSSAPI authentication via the mysqlclient library. It is assumed that SingleStore DB is already configured to use GSSAPI. You can read about that in Kerberos Authentication.

To validate, ensure that your MariaDB packages are version 10.1.11 or greater, and that you do not have conflicting MySQL packages:

RHEL MariaDB Version Check

[ec2-user ~]$ rpm -qa | egrep -i 'mysql|maria'

MariaDB-compat-10.2.23-1.el7.centos.x86_64
MariaDB-common-10.2.23-1.el7.centos.x86_64
MariaDB-client-10.2.23-1.el7.centos.x86_64
MariaDB-server-10.2.23-1.el7.centos.x86_64
MariaDB-common-debuginfo-10.2.23-1.el7.centos.x86_64
MariaDB-devel-10.2.23-1.el7.centos.x86_64

GSSAPI authentication has been validated with the mysqlclient-python client library. At the time of writing, there are no known pure python MySQL connector implementations which support the mariadb GSSAPI client-side protocol.

The following outlines an example Python program that will authenticate to SingleStore DB via GSSAPI using mysqlclient-python.

Note that the code used to connect for regular connections and Kerberos connections should be the same since the connector is porting the C extensions. This is because the SingleStore DB server checks grants and sees that the user is authenticated via GSSAPI, and then the client C extensions have an attached handler for GSSAPI, which runs independently of the third-party connector.

gssapi-test.py

import MySQLdb

# Substitute 'kerberos_principal_name' and change the host ip as required.
conn = MySQLdb.connect(
    user='root',
    host='127.0.0.1',
    database='information_schema')

if conn:
    print("Successfully connected via Kerberos authentication")

cursor = conn.cursor()

query = "SELECT query_text FROM mv_queries"

cursor.execute(query)
for qtext in cursor:
    print("{}".format(qtext))

cursor.close()
conn.close()

The output should look like this:

[ec2-user ~]$ python gssapi-test.py

Successfully connected via Kerberos authentication
('SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment',)
('SELECT @@memsql_id',)
('SELECT @@memsql_version',)
('SELECT ACTIVITY_NAME, QUERY_TEXT, PLAN_WARNINGS, PLAN_INFO FROM INFORMATION_SCHEMA.LMV_QUERIES',)
('SELECT WITH(binary_serialization=1, binary_serialization_internal=1) `_WM_AGG_TABLE`.`AGGREGATOR_ACTIVITY_NAME` AS `aggregator_activity_name`, SUM(`_WM_AGG_TABLE`.`ELAPSED_TIME_MS`) AS `elapsed_time_ms`, SUM(`_WM_AGG_TABLE`.`SUCCESS_COUNT`) AS `numRuns` FROM `_WM_AGG_TABLE` as `_WM_AGG_TABLE` GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=LLVM)',)
('SELECT query_text FROM mv_queries',)
('select query_text from mv_queries',)
('SELECT HEARTBEAT_NO_LOGGING agg.AGGREGATOR_ACTIVITY_NAME, coalesce(sum(leaves.memory_bs)*1000/agg.elapsed_time_ms/0x400/0x400, 0):>bigint,coalesce(agg.elapsed_time_ms/agg.numRuns, @):>bigint as avg_runtime, coalesce(sum(leaves.cpu_time_ms)/agg.numRuns, @):>bigint as avg_cpu_time FROM (select aggregator_activity_name, sum(elapsed_time_ms) as elapsed_time_ms, sum(success_count) as numRuns from information_schema._WM_AGG_TABLE group by 1) agg join information_schema._WM_LEAF_TABLE leaves on agg.aggregator_activity_name = leaves.aggregator_activity_name group by agg.AGGREGATOR_ACTIVITY_NAME',)
('select @@version_comment limit @',)
('SELECT @@memsql_id',)
('SELECT @@memsql_version',)
('SELECT AVAILABILITY_GROUP FROM information_schema.LEAVES\nJOIN information_schema.LMV_NODES\nON information_schema.LEAVES.NODE_ID=information_schema.LMV_NODES.NODE_ID',)
('SELECT WITH(binary_serialization=1, binary_serialization_internal=1) `leaves_0`.`MEMORY_BS` AS `MEMORY_BS`, `leaves_0`.`CPU_TIME_MS` AS `CPU_TIME_MS`, `leaves_0`.`AGGREGATOR_ACTIVITY_NAME` AS `AGGREGATOR_ACTIVITY_NAME` FROM `_WM_LEAF_TABLE` as `leaves_0` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=LLVM)',)
('SELECT SQL_NO_LOGGING 1',)
('SELECT SQL_NO_LOGGING @@maximum_table_memory, @@maximum_memory',)

[ec2-user ~]$
PyMySQL

PyMySQL

pip install PyMySQL

Note: Because this is a pure python implementation, it will not work with GSSAPI / Kerberos.

MySQL Connector/Python

MySQL Connector/Python

yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-8.0.15-1.el7.x86_64.rpm
yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-cext-8.0.15-1.el7.x86_64.rpm

Caution

The MySQL Connector/Python library is not recommended.

SingleStore recommends the PyMySQL and mysqlclient libraries.

Other Python Options