InterSystems IRIS® data platform provides a strong backbone for the IT architecture and initiatives for any company.
It provides multi-model (SQL and NoSQL) and multi-workload (transactions and real time analytics) DBMS capabilities, deep embedded integration and analytics capabilities to handle integration, transformation, API management, business logic, and a range of powerful analytics capabilities including BI, machine learning and natural language processing, all without moving data or requiring additional products. And it supports all major programming environments including Python, Java, .NET, Node.js, and R, enabling application developers to be productive quickly with their existing skills, both for client- and server-side development.
In this tutorial, we’re going to create the basic information management infrastructure for a small manufacturing company. In this case, our company will be roasting, packaging and selling delicious, freshly roasted coffee beans. Along the way, you’ll learn how the InterSystems IRIS data platform can serve as the backbone of your IT architecture.
The tutorial is divided into three parts and introduces the processes that you, as a coffee manufacturer, follow to set up everything from inventorying raw coffee beans to selling them in an online portal.
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.
You’ll also need the GitHub repo where all the code and sample files you’ll need are located. Open the IDE -- cannot display value - please provision a sandbox (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!
IRIS Coffee Company has three major divisions in the company:
ICO.inventory
ICO.catalog
Let’s use the SQL client built into the InterSystems IRIS Terminal to create those two tables using SQL CREATE statements:
tech
and password demo
USER >
command line prompt./sql
.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.
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) )
SELECT * FROM ICO.inventory
and SELECT * FROM ICO.catalog
and you should get back a table with no rows (yet)./sql
again to exit SQL mode in the InterSystems IRIS TerminalBefore 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.
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)
With the code explained, let’s run the program.
Open the IDE -- cannot display value - please provision a sandbox. Click in the terminal panel and type:
cd /home/project/quickstarts-full-stack/setup sudo odbcinst -i -d -f pyodbc_wheel/linux/odbcinst.ini
/home/project/quickstarts-full-stack/setup/connection.config
in your IDE-- cannot display value - please provision a sandbox
-- cannot display value - please provision a sandbox
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]')
Let’s make sure the data was inserted by going back into the Web Terminal and running a SQL SELECT query.
tech
and password demo
/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'
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.
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!)order_manifest.json
in the IDE.python manifest_importer.py
again.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.
To give you the best possible experience, this site uses cookies and by continuing to use the site you agree that we can save them on your device.