Quick tip: Accessing SingleStoreDB using the Python Client
Tags: singlestoredb, python
Published: 2022-10-04 12:12:00

## Abstract
The [Python Client](https://docs.singlestore.com/managed-service/en/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestoredb-python-client.html) enables developers to get up and running with SingleStoreDB quickly. In this short article, we'll discuss how to configure and use the Python Client. We'll use Deepnote as our development environment.

## Introduction
Python is one of the most popular programming languages today. It is also widely used by Data Scientists. We'll look at several small code examples of how we can use Python with SingleStoreDB and some of the data analyses we might be interested in performing.

## Create a Deepnote account
We'll create a free [account](https://deepnote.com/sign-up) on the Deepnote website. Once logged in, we'll create a new Deepnote project to give us a new notebook. We'll also need to create a `data` folder. In the `data` folder we'll store a [CSV](https://gist.github.com/VeryFatBoy/9af771d443f5ec4dd6eec8d69a062638) file containing the [Iris flower data set](https://en.wikipedia.org/wiki/Iris_flower_data_set).

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **Iris Demo Group** as our Workspace Group Name and **iris-demo** as our Workspace Name. We'll make a note of our **password** and **host** name. Finally, we'll create a new database using the **SQL Editor**:

```sql
CREATE DATABASE iris_demo;
```

## Deepnote notebook
Let's now start to fill out our notebook.

First, we'll need to install the Python Client:

```shell
!pip install singlestoredb
```

Next, we'll import the library:

```python
import singlestoredb as s2
```

We'll now create a connection to SingleStoreDB:

```python
conn = s2.connect(
            host = "<host>",
            port = "3306",
            user = "admin",
            password = "<password>",
            database = "iris_demo",
            results_type = "tuples"
        )
```

We'll replace the `<host>` and `<password>` with the values from our SingleStoreDB Cloud account. The `results_type` provides several options and, in this example, we'll work with tuples.

Let's now read our CSV data into a Pandas Dataframe, as follows:

```python
import pandas as pd

df = pd.read_csv("data/iris.csv", index_col = False)
```

and we can view the data, as follows:

```python
print(df.head(5))
```

The output should be similar to the following:

```
   sepal_length  sepal_width  petal_length  petal_width      species
0           5.1          3.5           1.4          0.2  Iris-setosa
1           4.9          3.0           1.4          0.2  Iris-setosa
2           4.7          3.2           1.3          0.2  Iris-setosa
3           4.6          3.1           1.5          0.2  Iris-setosa
4           5.0          3.6           1.4          0.2  Iris-setosa
```

Let's now prepare the SQL statement to insert the data into SingleStoreDB:

```python
stmt = """
    INSERT INTO iris (
        sepal_length,
        sepal_width,
        petal_length,
        petal_width,
        species
    ) VALUES (%s, %s, %s, %s, %s)
"""
```

Next, we'll create the table, insert the data from the Pandas Dataframe into SingleStoreDB and then retrieve the data back from SingleStoreDB:

```python
with conn:
    conn.autocommit(True)
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE iris (
                sepal_length FLOAT,
                sepal_width FLOAT,
                petal_length FLOAT,
                petal_width FLOAT,
                species VARCHAR(20)
            )
        """)
        cur.executemany(stmt, df)
        cur.execute("""
            SELECT *
            FROM iris
        """)
        rows = cur.fetchall()
```
Next, we'll convert the returned data into a new Pandas Dataframe:

```python
iris_df = pd.DataFrame(rows, columns = [
    "sepal_length",
    "sepal_width",
    "petal_length",
    "petal_width",
    "species"
])
```

We can now perform some data analysis, such as [PCA](https://en.wikipedia.org/wiki/Principal_component_analysis):

```python
# https://plotly.com/python/pca-visualization/

import plotly.express as px
from sklearn.decomposition import PCA

X = iris_df[[
    "sepal_length",
    "sepal_width",
    "petal_length",
    "petal_width"
]]

pca = PCA(n_components = 2)
components = pca.fit_transform(X)

pca_fig = px.scatter(
    components,
    x = 0,
    y = 1,
    color = iris_df["species"]
)

pca_fig.show()
```

The output should be similar to Figure 1.

![Figure 1. PCA](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4vmvvgoc1f2c5z59p0t8.png)

<center>Figure 1. PCA</center>

We can also check for correlations:

```python
import matplotlib.pyplot as plt
import seaborn as sns

sns.heatmap(
    iris_df.corr(),
    cmap = "OrRd",
    annot = True
)

plt.title("Correlations")
plt.plot()
```

The output should be similar to Figure 2.

![Figure 2. Correlations.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/drdpg7fsemdutaq94j9u.png)

<center>Figure 2. Correlations.</center>

And so on.

## Summary
The Python Client provides a wide range of options and, in this article, we have used tuples as they are a convenient way to work with bulk data. The [documentation](https://docs.singlestore.com/managed-service/en/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestoredb-python-client.html) provides examples of how to use the other options.