Part 1: Creating databases with SQL

Setup

To complete this tutorial, you’ll need the InterSystems Sandbox, which includes the InterSystems IRIS data platform and a development environment. Create that below, registering and/or logging in as needed. The Sandbox will take about a minute to create, and then you will see a list of sandbox settings and links which will be used later on in the tutorial.

Need InterSystems IRIS?

Get a free, online development sandbox here. Log in with your InterSystems universal account, or register for one below.

Login Register

You’ll also need the GitHub repo where all the code and sample files you’ll need are located. Open the IDE (setting requires sandbox - click here) (or the Sandbox IDE link above) and clone the GitHub repo by typing this in the terminal:

git clone https://github.com/intersystems/quickstarts-full-stack.git

Now you’re ready to get started!

Note:

This IDE you’re using is Theia, which works a lot like Visual Studio Code. The file explorer is on the left. Your code editing panel is on the right. And your terminal window is below the editing panel.

Database creation

IRIS Coffee Company has three major divisions in the company:

• the warehouse stores the raw coffee bean inventory. Its data will be in the table ICO.inventory
• the roastery roasts the beans, and doesn’t need to store data
• the storefront, where the company sells the roasted coffee. Its data will be in the table ICO.catalog

Let’s use the SQL client built into the InterSystems IRIS Terminal to create those two tables using SQL CREATE statements:

1. Open the Sandbox IDE (setting requires sandbox - click here)
2. From the InterSystems menu, select Web Terminal
3. Log in with username tech and password demo
4. You should see a USER > command line prompt.
5. Type /sql.
6. Paste the following SQL CREATE statement and hit return.

   CREATE TABLE ICO.inventory
   (
   vendor_id VARCHAR(128),
   vendor_product_code VARCHAR(128),
   quantity_kg DECIMAL(10,2),
   date_arrival DATE
   )
   

   If your query is successful, you will see Nothing to display, as CREATE statements don’t return anything.

   

7. Copy and paste the following SQL CREATE statement to create ICO.catalog table and hit return.

   CREATE TABLE ICO.catalog
   (
   catalog_id BIGINT IDENTITY,
   product_code VARCHAR(128),
   quantity INTEGER,
   price DECIMAL(10,2),
   time_roasted DATETIME,
   roasting_notes VARCHAR(2048),
   img VARCHAR(2048)
   )
   

8. You can test a bit more by running SELECT * FROM ICO.inventory and SELECT * FROM ICO.catalog and you should get back a table with no rows (yet).
9. Type /sql again to exit SQL mode in the InterSystems IRIS Terminal

Python data loading

Before exploring the details of the Python data loading script, it’s good to have a high-level view of what’s going on. We want to populate the database with raw coffee bean orders, simulating shipments of raw beans from vendors around the world. Assume that all the shipments are consolidated in a single order manifest in JSON format.

Our manifest can be found at quickstarts-full-stack/setup/order_manifest.json.

To load the data, we’ll use a Python program to parse the order manifest file and insert the deliveries into the database, also using SQL, but this time within Python.

After that, you’ll build the program that imports order manifests into the database. You will read JSON in from disk with standard Python. Then you’ll set up your ODBC database connection. And finally, you write standard SQL INSERT commands to write the JSON data to the database.

Tip

If you’re confident reading Python by yourself, just jump to running the script.

Import script walkthrough

The full script is in setup/manifest_importer.py. Follow along below or if you’re confident reading the file yourself, just jump to running the script.

Notice some of the key elements of this script. In the main function definition, we simply import the JSON order manifest file and validate it, checking for the structure needed for the inventory database.

def main():
    with open('./order_manifest.json') as f:
    data = json.load(f)
    data, status, exp = validate_manifest(data)

The next thing we do in main() is read database credentials from the file, connection.config.

connection_detail = get_connection_info("connection.config")
ip = connection_detail["ip"]
port = int(connection_detail["port"])
namespace = connection_detail["namespace"]
username = connection_detail["username"]
password = connection_detail["password"]
driver = "{InterSystems ODBC}"

Then we set up a connection to the database using an ODBC driver.

connection_string = 'DRIVER={};SERVER={};PORT={};DATABASE={};UID={};PWD={}' \
.format(driver, ip, port, namespace, username, password)
connection = pyodbc.connect(connection_string)

If all goes well, we call load_manifest with the JSON data and the database connection object.

msg = load_manifest(data, connection)

In load_manifest, we iterate through all the items in the JSON file, inserting each one into the ICO.inventory table we created previously using SQL INSERT statements. Here’s how that works: First, we define our base SQL INSERT statement:

fieldnamesql = "INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival)"

On the next two lines, we create a new date string based on the current date, for example, ‘2020-06-18’. This will be the date the product arrived in the warehouse.

today = date.today()
mydate = today.strftime("%Y-%m-%d")

Now, the code loops through each item object in the JSON file, and uses the data there to construct the VALUES part of our SQL INSERT statement. The result is a completed SQL INSERT statement that looks something like this:

INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES (ETRADER, ETHIOPIA32, 200, ‘2020-06-18’)

Now the code runs the completed SQL statement. We defined our database cursor on the first line of the function, so now we can run execute on the cursor with the SQL statement as its input.

cursor.execute(valsql)

Run the Python import

With the code explained, let’s run the program.

Set up the ODBC driver.

Open the IDE (setting requires sandbox - click here). Click in the terminal panel and type:

cd /home/project/quickstarts-full-stack/setup
sudo odbcinst -i -d -f pyodbc_wheel/linux/odbcinst.ini

Database connection settings for your sandbox

1. Open /home/project/quickstarts-full-stack/setup/connection.config in your IDE
2. Change ip from localhost to (setting requires sandbox - click here)
3. Change port from 5173 to (setting requires sandbox - click here)
4. Save the file.

Type this in your terminal window:

 
python manifest_importer.py

You should see the following output.

Connected to InterSystems IRIS
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('ETRADER', 'ETHIOPA32', 200, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('BRZ221', 'BRAZILPREM', 100, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('GMLPROD', 'GUATEMALAALT30', 100, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('DKE', 'SUMATRA2', 100, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('DKE', 'SUMATRA3', 200, '[year-month-day]')

Tip

You can also use Java, C#/.NET, or Node.JS as well as Python with InterSystems IRIS. Language-specific QuickStarts are on getting started.

SQL database queries

Let’s make sure the data was inserted by going back into the Web Terminal and running a SQL SELECT query.

1. (setting requires sandbox - click here)
2. From the InterSystems menu, select Web Terminal
3. Log in with username tech and password demo
4. Type /sql.

Run:

select * from ICO.inventory

You should see five rows of raw coffee beans in inventory.

Try a couple queries to play with our inventory in more detail. See all large deliveries — over 100 kilograms. You might want to start roasting these first.

SELECT * FROM ICO.inventory WHERE quantity_kg > 100

Or you may need to see all inventory from a particular vendor.

SELECT * FROM ICO.inventory WHERE vendor_id LIKE 'DKE'

Add your own inventory

Finally, add more inventory on your own. Create your own JSON manifest and make up your own values for vendor id, product code and quantity.

1. Copy order_manifest.json to order_manifest-original.json (don’t try to comment out lines in the original JSON file. JSON doesn’t allow commented lines!)
2. Edit order_manifest.json in the IDE.
3. Change the values as you like.
4. Run python manifest_importer.py again.

Summary

Now you know how to use InterSystems IRIS as a relational database, using SQL either from a SQL client, or from Python. Move onto Part 2 to code REST services to move data throughout the company and onto the web storefront.