Quick tip: Using SQLAlchemy with SingleStoreDB
Tags: singlestoredb, sqlalchemy, sql, json
Published: 2022-11-07 15:15:30

## Abstract
SingleStore has developed an [SQLAlchemy dialect](https://github.com/singlestore-labs/sqlalchemy-singlestoredb) that allows SQLAlchemy APIs to be used with SingleStoreDB. This short article will show how to install and use it.

## Introduction
SQLAlchemy is a popular method to access database systems from Python. It is straightforward to install and use with SingleStoreDB.

## 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 **SQLAlchemy Demo Group** as our Workspace Group Name and **sqlalchemy-demo** as our Workspace Name. We'll make a note of our **password** and **host** name.

## Create the database and table
We'll use a subset of the inventory system example from a [previous article](https://medium.com/@VeryFatBoy/using-singlestore-as-a-json-document-database-bc386fc5c563), as it provides a combination of both Relational and JSON data. In the **SQL Editor** in SingleStoreDB Cloud, we'll create a database and a table:

```sql
CREATE DATABASE e_store;

USE e_store;

CREATE TABLE products (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     name VARCHAR(250) NOT NULL,
     brand_id INT UNSIGNED NOT NULL,
     category_id INT UNSIGNED NOT NULL,
     attributes JSON NOT NULL,
     PRIMARY KEY(id),
     INDEX CATEGORY_ID(category_id ASC),
     INDEX BRAND_ID(brand_id ASC)
);
```

## Populate the database table
Let's now populate the **products** table:

```sql
-- Televisions
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Prime', '1', '1', '{"screen" : "50 inch", "resolution" : "2048 x 1152 pixels", "ports" : {"hdmi" : 1, "usb" : 3}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Octoview', '1', '1', '{"screen" : "40 inch", "resolution" : "1920 x 1080 pixels", "ports" : {"hdmi" : 1, "usb" : 2}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Dreamer', '1', '1', '{"screen" : "30 inch", "resolution" : "1600 x 900 pixels", "ports" : {"hdmi" : 1, "usb" : 1}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Bravia', '1', '1', '{"screen" : "25 inch", "resolution" : "1366 x 768 pixels", "ports" : {"hdmi" : 1, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}'),
('Proton', '1', '1', '{"screen" : "20 inch", "resolution" : "1280 x 720 pixels", "ports" : {"hdmi" : 0, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}');

-- Mobile Phones
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Desire', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["GSM", "CDMA", "HSPA"]', 'EVDO'),
        "body",
        "5.11 x 2.59 x 0.46 inches",
        "weight",
        "143 grams",
        "sim",
        "Micro-SIM",
        "display",
        "4.5 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android Jellybean v4.3"
    )
),
('Passion', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'HSPA'),
        "body",
        "6.11 x 3.59 x 0.46 inches",
        "weight",
        "145 grams",
        "sim",
        "Micro-SIM",
        "display",
        "4.5 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android Jellybean v4.3"
    )
),
('Emotion', '2', '2', JSON_BUILD_OBJECT("network" ,
        JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'EVDO'),
        "body",
        "5.50 x 2.50 x 0.50 inches",
        "weight",
        "125 grams",
        "sim",
        "Micro-SIM",
        "display",
        "5.00 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android KitKat v4.3"
    )
),
('Sensation', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["GSM", "HSPA"]', 'EVDO'),
        "body",
        "4.00 x 2.00 x 0.75 inches",
        "weight",
        "150 grams",
        "sim",
        "Micro-SIM",
        "display",
        "3.5 inches",
        "resolution",
        "720 x 1280 pixels",
        "os",
        "Android Lollipop v4.3"
    )
),
('Joy', '2', '2', JSON_BUILD_OBJECT("network",
        JSON_ARRAY_PUSH_STRING('["CDMA", "HSPA"]', 'EVDO'),
        "body",
        "7.00 x 3.50 x 0.25 inches",
        "weight",
        "250 grams",
        "sim",
        "Micro-SIM",
        "display",
        "6.5 inches",
        "resolution",
        "1920 x 1080 pixels",
        "os",
        "Android Marshmallow v4.3"
    )
);

-- Cameras
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Explorer', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV III", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Runner', '3', '3',   '{"sensor_type" : "CMOS", "processor" : "Digic DV II",  "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Traveler', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV II",  "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Walker', '3', '3',   '{"sensor_type" : "CMOS", "processor" : "Digic DV I",   "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Jumper', '3', '3',   '{"sensor_type" : "CMOS", "processor" : "Digic DV I",   "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}');
```

## Install SQLAlchemy
We can install the software, as follows:

```shell
pip install sqlalchemy-singlestoredb
```

## Read, Update and Delete operations
Let's create a small Python file, `s2_test.py`, as follows:

```python
from sqlalchemy import create_engine

eng = create_engine(
   "singlestoredb://admin:<password>@<host>:3306/e_store"
)

with eng.connect() as conn:

   # Read
   # Find any Televisions that have
   # one or more USB port(s) and one or more HDMI port(s).

   res = conn.execute("""
      SELECT * FROM products
      WHERE category_id = 1
      AND attributes::ports::usb > 0
      AND attributes::ports::hdmi > 0;
   """)
   print("---------- Read ----------")
   for row in res:
      print(row)

   # Update
   # Create a new attribute called body_color for Televisions.

   conn.execute("""
      UPDATE products
      SET attributes::$body_color = 'red'
      WHERE category_id = 1;
   """)

   # Check that body_color has been added.

   res = conn.execute("""
      SELECT attributes
      FROM products
      WHERE category_id = 1;
   """)
   print("---------- Update ----------")
   for row in res:
      print(row)

   # Delete
   # Delete attribute mount_type for Cameras.

   conn.execute("""
      UPDATE products
      SET attributes = JSON_DELETE_KEY(attributes, 'mount_type')
      WHERE category_id = 3;
   """)

   # Check that mount_type has been deleted.

   res = conn.execute("""
      SELECT attributes
      FROM products
      WHERE category_id = 3;
   """)
   print("---------- Delete ----------")
   for row in res:
      print(row)
```

We'll replace the `<password>` and `<host>` with the values from our SingleStoreDB Cloud account.

After running our program:

```shell
python3 s2_test.py
```

the output should be as follows:

```
---------- Read ----------
(1, 'Prime', 1, 1, {'ports': {'hdmi': 1, 'usb': 3}, 'resolution': '2048 x 1152 pixels', 'screen': '50 inch', 'speakers': {'left': '10 watt', 'right': '10 watt'}})
(3, 'Dreamer', 1, 1, {'ports': {'hdmi': 1, 'usb': 1}, 'resolution': '1600 x 900 pixels', 'screen': '30 inch', 'speakers': {'left': '10 watt', 'right': '10 watt'}})
(2, 'Octoview', 1, 1, {'ports': {'hdmi': 1, 'usb': 2}, 'resolution': '1920 x 1080 pixels', 'screen': '40 inch', 'speakers': {'left': '10 watt', 'right': '10 watt'}})
---------- Update ----------
({'body_color': 'red', 'ports': {'hdmi': 1, 'usb': 3}, 'resolution': '2048 x 1152 pixels', 'screen': '50 inch', 'speakers': {'left': '10 watt', 'right': '10 watt'}},)
({'body_color': 'red', 'ports': {'hdmi': 1, 'usb': 1}, 'resolution': '1600 x 900 pixels', 'screen': '30 inch', 'speakers': {'left': '10 watt', 'right': '10 watt'}},)
({'body_color': 'red', 'ports': {'hdmi': 1, 'usb': 2}, 'resolution': '1920 x 1080 pixels', 'screen': '40 inch', 'speakers': {'left': '10 watt', 'right': '10 watt'}},)
({'body_color': 'red', 'ports': {'hdmi': 1, 'usb': 0}, 'resolution': '1366 x 768 pixels', 'screen': '25 inch', 'speakers': {'left': '5 watt', 'right': '5 watt'}},)
({'body_color': 'red', 'ports': {'hdmi': 0, 'usb': 0}, 'resolution': '1280 x 720 pixels', 'screen': '20 inch', 'speakers': {'left': '5 watt', 'right': '5 watt'}},)
---------- Delete ----------
({'monitor_type': 'LCD', 'processor': 'Digic DV III', 'scanning_system': 'progressive', 'sensor_type': 'CMOS'},)
({'monitor_type': 'LCD', 'processor': 'Digic DV II', 'scanning_system': 'progressive', 'sensor_type': 'CMOS'},)
({'monitor_type': 'LED', 'processor': 'Digic DV I', 'scanning_system': 'progressive', 'sensor_type': 'CMOS'},)
({'monitor_type': 'LED', 'processor': 'Digic DV II', 'scanning_system': 'progressive', 'sensor_type': 'CMOS'},)
({'monitor_type': 'LCD', 'processor': 'Digic DV I', 'scanning_system': 'progressive', 'sensor_type': 'CMOS'},)
```

Checking the output with the data we stored initially, we can confirm that all the operations successfully completed.

## Summary
In this short article, we have quickly tested SQLAlchemy with SingleStoreDB using Read, Update and Delete operations.