This SQLAlchemy ORM example, running on Python, implements a basic REST API server for an e-commerce application scenario. Database access in this application is managed through SQL Alchemy ORM. The e-commerce database (ysql-sqlalchemy) includes the following tables:

  • users: the users of the e-commerce site
  • products: the products being sold
  • orders: the orders placed by the users
  • orderline: each line item of an order

The source for this application can be found in the python/sqlalchemy directory of Yugabyte's Using ORMs with YugabyteDB GitHub repository.

Before you begin

To configure and run this application, make sure that you've completed these prerequisites.

YugabyteDB

YugabyteDB is up and running. Download and install YugabyteDB by following the steps in Quick start.

Python

Python 3 is installed

Python packages (dependencies)

Python packages (dependencies) are installed

To quickly install these three packages, run the following command.

$ pip3 install psycopg2-binary sqlalchemy jsonpickle

Clone the "orm-examples" repository

Clone the Yugabyte orm-examples repository by running the following command.

$ git clone https://github.com/YugabyteDB-Samples/orm-examples.git

Update the database settings in the src/config.py file to match the following. If YSQL authentication is enabled, add the password (default for the yugabyte user is yugabyte).

import logging

listen_port = 8080
db_user = 'yugabyte'
db_password = 'yugabyte'
database = 'ysql_sqlalchemy'
schema = 'ysql_sqlalchemy'
db_host = 'localhost'
db_port = 5433

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s:%(levelname)s:%(message)s"
    )

Start the REST API server

Run the following Python script to start the server.

python3 ./src/rest-service.py

The REST API server will start and listen for your requests at http://localhost:8080.

Send requests to the application

Create 2 users.

$ curl --data '{ "firstName" : "John", "lastName" : "Smith", "email" : "jsmith@example.com" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users
$ curl --data '{ "firstName" : "Tom", "lastName" : "Stewart", "email" : "tstewart@example.com" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users

Create 2 products.

$ curl \
  --data '{ "productName": "Notebook", "description": "200 page notebook", "price": 7.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products
$ curl \
  --data '{ "productName": "Pencil", "description": "Mechanical pencil", "price": 2.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products

Create 2 orders.

$ curl \
  --data '{ "userId": "2", "products": [ { "productId": 1, "units": 2 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders
$ curl \
  --data '{ "userId": "2", "products": [ { "productId": 1, "units": 2 }, { "productId": 2, "units": 4 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders

Query results

Using the YSQL shell

$ ./bin/ysqlsh
ysqlsh (11.2)
Type "help" for help.

yugabyte=#
yugabyte=# SELECT count(*) FROM users;
 count
-------
     2
(1 row)
yugabyte=# SELECT count(*) FROM products;
 count
-------
     2
(1 row)
yugabyte=# SELECT count(*) FROM orders;
 count
-------
     2
(1 row)

Using the REST API

$ curl http://localhost:8080/users
{
  "content": [
    {
      "userId": 2,
      "firstName": "Tom",
      "lastName": "Stewart",
      "email": "tstewart@example.com"
    },
    {
      "userId": 1,
      "firstName": "John",
      "lastName": "Smith",
      "email": "jsmith@example.com"
    }
  ],
  ...
}
$ curl http://localhost:8080/products
{
  "content": [
    {
      "productId": 2,
      "productName": "Pencil",
      "description": "Mechanical pencil",
      "price": 2.5
    },
    {
      "productId": 1,
      "productName": "Notebook",
      "description": "200 page notebook",
      "price": 7.5
    }
  ],
  ...
}
$ curl http://localhost:8080/orders
{
  "content": [
    {
      "orderTime": "2019-05-10T04:26:54.590+0000",
      "orderId": "999ae272-f2f4-46a1-bede-5ab765bb27fe",
      "user": {
        "userId": 2,
        "firstName": "Tom",
        "lastName": "Stewart",
        "email": "tstewart@example.com"
      },
      "userId": null,
      "orderTotal": 25,
      "products": []
    },
    {
      "orderTime": "2019-05-10T04:26:48.074+0000",
      "orderId": "1598c8d4-1857-4725-a9ab-14deb089ab4e",
      "user": {
        "userId": 2,
        "firstName": "Tom",
        "lastName": "Stewart",
        "email": "tstewart@example.com"
      },
      "userId": null,
      "orderTotal": 15,
      "products": []
    }
  ],
  ...
}

Explore the source

The source for the application above can be found in the Yugabyte orm-examples repository.