Python ORM example application

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

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 source for this application can be found in the python/sqlalchemy directory of Yugabyte's Using ORMs with YugabyteDB GitHub repository.

Prerequisites

This tutorial assumes that you have:

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

Set up the database connection

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": []
    }
  ],
  ...
}