DEV Community

Akmal Chaudhri for SingleStore

Posted on • Updated on

Quick tip: Using SQLAlchemy with SingleStoreDB

Abstract

SingleStore has developed an SQLAlchemy dialect 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 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, 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:

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)
);
Enter fullscreen mode Exit fullscreen mode

Populate the database table

Let's now populate the products table:

-- 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"}');
Enter fullscreen mode Exit fullscreen mode

Install SQLAlchemy

We can install the software, as follows:

pip install sqlalchemy-singlestoredb
Enter fullscreen mode Exit fullscreen mode

Read, Update and Delete operations

Let's create a small Python file, s2_test.py, as follows:

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)
Enter fullscreen mode Exit fullscreen mode

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

After running our program:

python3 s2_test.py
Enter fullscreen mode Exit fullscreen mode

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'},)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)