Computational Thinking + Doing
SQL Relational Databases
Managing relational, OLTP grocery data—using Amazon RDS, Azure SQL Database, and GCP Cloud SQL.
Getting Started
If you are interested in reproducing this work, here are the versions of Python and Python packages used.
import sys
print(sys.version)3.9.6 (v3.9.6:db3ff76da1, Jun 28 2021, 11:49:53)
[Clang 6.0 (clang-600.0.57)]!pip install pandas==1.3.4
!pip install psycopg2-binary==2.9.3import pandas
import psycopg2Importing Data
W3Schools provides a sample dataset in its SQL tutorial, which will be used in this work.
categories_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Categories.csv")
customers_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Customers.csv")
employees_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Employees.csv")
order_details_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/OrderDetails.csv")
orders_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Orders.csv")
products_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Products.csv")
shippers_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Shippers.csv")
suppliers_df = pandas.read_csv(filepath_or_buffer = "../../dataset/w3schools/Suppliers.csv")Exploring Data
categories_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 CategoryID 8 non-null int64
1 CategoryName 8 non-null object
2 Description 8 non-null object
dtypes: int64(1), object(2)
memory usage: 320.0+ bytescategories_df CategoryID CategoryName Description
0 1 Beverages Soft drinks, coffees, teas, beers, and ales
1 2 Condiments Sweet and savory sauces, relishes, spreads, an...
2 3 Confections Desserts, candies, and sweet breads
3 4 Dairy Products Cheeses
4 5 Grains/Cereals Breads, crackers, pasta, and cereal
5 6 Meat/Poultry Prepared meats
6 7 Produce Dried fruit and bean curd
7 8 Seafood Seaweed and fishcustomers_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 91 entries, 0 to 90
Data columns (total 7 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 CustomerID 91 non-null int64
1 CustomerName 91 non-null object
2 ContactName 91 non-null object
3 Address 91 non-null object
4 City 91 non-null object
5 PostalCode 90 non-null object
6 Country 91 non-null object
dtypes: int64(1), object(6)
memory usage: 5.1+ KBcustomers_df.head(n = 10) CustomerID CustomerName ContactName Address City PostalCode Country
0 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
1 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
2 3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico
3 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
4 5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
5 6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
6 7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
7 8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
8 9 Bon app' Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
9 10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canadaemployees_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 EmployeeID 10 non-null int64
1 LastName 10 non-null object
2 FirstName 10 non-null object
3 BirthDate 10 non-null object
4 Photo 10 non-null object
5 Notes 10 non-null object
dtypes: int64(1), object(5)
memory usage: 608.0+ bytesemployees_df EmployeeID LastName FirstName BirthDate Photo Notes
0 1 Davolio Nancy 12/8/68 EmpID1.pic Education includes a BA in psychology from Col...
1 2 Fuller Andrew 2/19/52 EmpID2.pic Andrew received his BTS commercial and a Ph.D....
2 3 Leverling Janet 8/30/63 EmpID3.pic Janet has a BS degree in chemistry from Boston...
3 4 Peacock Margaret 9/19/58 EmpID4.pic Margaret holds a BA in English literature from...
4 5 Buchanan Steven 3/4/55 EmpID5.pic Steven Buchanan graduated from St. Andrews Uni...
5 6 Suyama Michael 7/2/63 EmpID6.pic Michael is a graduate of Sussex University (MA...
6 7 King Robert 5/29/60 EmpID7.pic Robert King served in the Peace Corps and trav...
7 8 Callahan Laura 1/9/58 EmpID8.pic Laura received a BA in psychology from the Uni...
8 9 Dodsworth Anne 7/2/69 EmpID9.pic Anne has a BA degree in English from St. Lawre...
9 10 West Adam 9/19/28 EmpID10.pic An old chum.order_details_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 518 entries, 0 to 517
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 OrderDetailID 518 non-null int64
1 OrderID 518 non-null int64
2 ProductID 518 non-null int64
3 Quantity 518 non-null int64
dtypes: int64(4)
memory usage: 16.3 KBorder_details_df.head(n = 10) OrderDetailID OrderID ProductID Quantity
0 1 10248 11 12
1 2 10248 42 10
2 3 10248 72 5
3 4 10249 14 9
4 5 10249 51 40
5 6 10250 41 10
6 7 10250 51 35
7 8 10250 65 15
8 9 10251 22 6
9 10 10251 57 15orders_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 196 entries, 0 to 195
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 OrderID 196 non-null int64
1 CustomerID 196 non-null int64
2 EmployeeID 196 non-null int64
3 OrderDate 196 non-null object
4 ShipperID 196 non-null int64
dtypes: int64(4), object(1)
memory usage: 7.8+ KBorders_df.head(n = 10) OrderID CustomerID EmployeeID OrderDate ShipperID
0 10248 90 5 7/4/96 3
1 10249 81 6 7/5/96 1
2 10250 34 4 7/8/96 2
3 10251 84 3 7/8/96 1
4 10252 76 4 7/9/96 2
5 10253 34 3 7/10/96 2
6 10254 14 5 7/11/96 2
7 10255 68 9 7/12/96 3
8 10256 88 3 7/15/96 2
9 10257 35 4 7/16/96 3products_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 77 entries, 0 to 76
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 ProductID 77 non-null int64
1 ProductName 77 non-null object
2 SupplierID 77 non-null int64
3 CategoryID 77 non-null int64
4 Unit 77 non-null object
5 Price 77 non-null float64
dtypes: float64(1), int64(3), object(2)
memory usage: 3.7+ KBproducts_df.head(n = 10) ProductID ProductName SupplierID CategoryID Unit Price
0 1 Chais 1 1 10 boxes x 20 bags 18.00
1 2 Chang 1 1 24 - 12 oz bottles 19.00
2 3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00
3 4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22.00
4 5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35
5 6 Grandma's Boysenberry Spread 3 2 12 - 8 oz jars 25.00
6 7 Uncle Bob's Organic Dried Pears 3 7 12 - 1 lb pkgs. 30.00
7 8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.00
8 9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.00
9 10 Ikura 4 8 12 - 200 ml jars 31.00shippers_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 ShipperID 3 non-null int64
1 ShipperName 3 non-null object
2 Phone 3 non-null object
dtypes: int64(1), object(2)
memory usage: 200.0+ bytesshippers_df ShipperID ShipperName Phone
0 1 Speedy Express (503) 555-9831
1 2 United Package (503) 555-3199
2 3 Federal Shipping (503) 555-9931suppliers_df.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 29 entries, 0 to 28
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 SupplierID 29 non-null int64
1 SupplierName 29 non-null object
2 ContactName 29 non-null object
3 Address 29 non-null object
4 City 29 non-null object
5 PostalCode 29 non-null object
6 Country 29 non-null object
7 Phone 29 non-null object
dtypes: int64(1), object(7)
memory usage: 1.9+ KBsuppliers_df.head() SupplierID SupplierName ContactName Address City PostalCode Country Phone
0 1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222
1 2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
2 3 Grandma Kelly's Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA (313) 555-5735
3 4 Tokyo Traders Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo 100 Japan (03) 3555-5011
4 5 Cooperativa de Quesos 'Las Cabras' Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54Implementing Locally/On-Premise With PostgeSQL on Docker
Since Amazon RDS, Azure SQL Database, and GCP Cloud SQL are paid structured query language (SQL) server cloud services, it will be prudent to model your tasks locally (on-premise, i.e., your personal computer), before attempting to replicate the same tasks on the cloud and possibly incurring costs while doing so.
A PostgreSQL (or Postgres) relational database management system (RDBMS) image on Docker will be used as the local SQL server. The Psycopg package will be used as a Python interface to run queries on Postgres.
Connecting to database server
# Environment variables hidden for security purposes
db_connection = psycopg2.connect(
host = localhost_host,
port = localhost_port,
database = localhost_database,
user = localhost_user,
password = localhost_password
)
db_connection.autocommit = True
cursor = db_connection.cursor()
cursor.execute(query = "SELECT version()")
db_version = cursor.fetchone()
print(db_version)('PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit',)Resetting/dropping existing tables
The following tables are arranged in a way that dependent tables are dropped ahead of independent tables. Otherwise, an error will be thrown because you are dropping an independent table that is still being referenced by dependent table.
sql_drop_table = """
DROP TABLE "OrderDetails";
DROP TABLE "Products";
DROP TABLE "Categories";
DROP TABLE "Suppliers";
DROP TABLE "Orders";
DROP TABLE "Shippers";
DROP TABLE "Employees";
DROP TABLE "Customers";
"""
cursor.execute(query = sql_drop_table)Creating tables
The following tables are arranged in a way that independent tables are created ahead of dependent tables. Otherwise, an error will be thrown if you create a table that references an independent table yet to be created.
sql_create_table = """
CREATE TABLE "Customers" (
"CustomerID" INT NOT NULL,
"CustomerName" VARCHAR(100) NOT NULL,
"ContactName" VARCHAR(100) NOT NULL,
"Address" VARCHAR(100) NOT NULL,
"City" VARCHAR(50) NOT NULL,
"PostalCode" VARCHAR(25) NOT NULL,
"Country" VARCHAR(25) NOT NULL,
PRIMARY KEY ("CustomerID")
);
CREATE TABLE "Employees" (
"EmployeeID" INT NOT NULL,
"LastName" VARCHAR(50) NOT NULL,
"FirstName" VARCHAR(50) NOT NULL,
"BirthDate" VARCHAR(100) NOT NULL,
"Photo" VARCHAR(250),
"Notes" VARCHAR(500),
PRIMARY KEY ("EmployeeID")
);
CREATE TABLE "Shippers" (
"ShipperID" INT NOT NULL,
"ShipperName" VARCHAR(50) NOT NULL,
"Phone" VARCHAR(15),
PRIMARY KEY ("ShipperID")
);
CREATE TABLE "Orders" (
"OrderID" INT NOT NULL,
"CustomerID" INT NOT NULL,
"EmployeeID" INT NOT NULL,
"OrderDate" VARCHAR(15),
"ShipperID" INT NOT NULL,
PRIMARY KEY ("OrderID"),
FOREIGN KEY ("CustomerID") REFERENCES "Customers",
FOREIGN KEY ("EmployeeID") REFERENCES "Employees",
FOREIGN KEY ("ShipperID") REFERENCES "Shippers"
);
CREATE TABLE "Suppliers" (
"SupplierID" INT NOT NULL,
"SupplierName" VARCHAR(50) NOT NULL,
"ContactName" VARCHAR(50) NOT NULL,
"Address" VARCHAR(100) NOT NULL,
"City" VARCHAR(50) NOT NULL,
"PostalCode" VARCHAR(25) NOT NULL,
"Country" VARCHAR(25) NOT NULL,
"Phone" VARCHAR(15),
PRIMARY KEY ("SupplierID")
);
CREATE TABLE "Categories" (
"CategoryID" INT NOT NULL,
"CategoryName" VARCHAR(50) NOT NULL,
"Description" VARCHAR(250) NOT NULL,
PRIMARY KEY ("CategoryID")
);
CREATE TABLE "Products" (
"ProductID" INT NOT NULL,
"ProductName" VARCHAR(100) NOT NULL,
"SupplierID" INT NOT NULL,
"CategoryID" INT NOT NULL,
"Unit" VARCHAR(100) NOT NULL,
"Price" FLOAT(2) NOT NULL,
PRIMARY KEY ("ProductID"),
FOREIGN KEY ("SupplierID") REFERENCES "Suppliers",
FOREIGN KEY ("CategoryID") REFERENCES "Categories"
);
CREATE TABLE "OrderDetails" (
"OrderDetailID" INT NOT NULL,
"OrderID" INT NOT NULL,
"ProductID" INT NOT NULL,
"Quantity" INT NOT NULL CHECK ("Quantity" >= 0),
PRIMARY KEY ("OrderDetailID"),
FOREIGN KEY ("OrderID") REFERENCES "Orders",
FOREIGN KEY ("ProductID") REFERENCES "Products"
);
"""
cursor.execute(query = sql_create_table)Inserting Pandas dataframes into SQL tables
# Insert into Categories table
categories_tuples = [tuple(x) for x in categories_df.to_numpy()]
categories_cols = ','.join(list(categories_df.columns))
sql_select = """
INSERT INTO "Categories" VALUES(%s, %s, %s);
"""
cursor.executemany(sql_select, categories_tuples)Repeat this for the other Pandas dataframes that need to be inserted into their respective tables.
Performing basic SELECT-FROM-WHERE queries
Let’s confirm the numbers of rows and the data in the Categories table.
sql_select = """
SELECT * FROM "Categories";
"""
cursor.execute(query = sql_select)
print("The Categories table has", cursor.rowcount, "rows.")The Categories table has 8 rows.row = cursor.fetchone()
while row is not None:
print("CategoryID:", row[0], ", CategoryName:", row[1], ", Description:", row[2])
row = cursor.fetchone()CategoryID: 1 , CategoryName: Beverages , Description: Soft drinks, coffees, teas, beers, and ales
CategoryID: 2 , CategoryName: Condiments , Description: Sweet and savory sauces, relishes, spreads, and seasonings
CategoryID: 3 , CategoryName: Confections , Description: Desserts, candies, and sweet breads
CategoryID: 4 , CategoryName: Dairy Products , Description: Cheeses
CategoryID: 5 , CategoryName: Grains/Cereals , Description: Breads, crackers, pasta, and cereal
CategoryID: 6 , CategoryName: Meat/Poultry , Description: Prepared meats
CategoryID: 7 , CategoryName: Produce , Description: Dried fruit and bean curd
CategoryID: 8 , CategoryName: Seafood , Description: Seaweed and fishLet’s now confirm that the other tables have the correct number of rows.
sql_select = """
SELECT * FROM "Customers";
"""
cursor.execute(query = sql_select)
print("The Customers table has", cursor.rowcount, "rows.")The Customers table has 91 rows.The Employees table has 10 rows.The Shippers table has 3 rows.The Orders table has 196 rows.The Suppliers table has 29 rows.The Products table has 77 rows.The OrderDetails table has 518 rows.Let’s see how many unique OrderDate are in the Orders table
sql_select = """
SELECT DISTINCT "OrderDate" FROM "Orders";
"""
cursor.execute(query = sql_select)
print("The Orders table has", cursor.rowcount, " unique OrderDate.")The Orders table has 160 unique OrderDate.Let’s see if there are products from the Products table that are priced at over $100.00.
sql_select = """
SELECT "ProductID", "ProductName", "Price"
FROM "Products"
WHERE "Price" > 100.00;
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()
while row is not None:
print("ProductID:", row[0], ", ProductName:", row[1], ", Price:", "{:.2f}".format(row[2]))
row = cursor.fetchone()ProductID: 29 , ProductName: Thüringer Rostbratwurst , Price: 123.79
ProductID: 38 , ProductName: Côte de Blaye , Price: 263.50Let’s see if there are orders that include any product priced at over $100.00.
sql_select = """
SELECT "OrderDetails"."OrderID", "Products"."ProductName", "Products"."Price"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "Products"."Price" > 100.00;
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()
while row is not None:
print("Order ID:", row[0], "Product Name:", row[1], ", Price:", "${:.2f}".format(row[2]))
row = cursor.fetchone()Order ID: 10268 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10290 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10305 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10329 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10351 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10353 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10354 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10360 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10360 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10369 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10372 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10382 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10400 Product Name: Thüringer Rostbratwurst , Price: $123.79
Order ID: 10417 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10424 Product Name: Côte de Blaye , Price: $263.50
Order ID: 10440 Product Name: Thüringer Rostbratwurst , Price: $123.79Performing basic UNION, INTERSECT, and EXCEPT set queries
Let’s see if there are any OrderIDs that include either ProductIDs priced at over $100.00. (The resulting set should be the same as above.)
sql_select = """
(SELECT "OrderDetails"."OrderID"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "OrderDetails"."ProductID" = 29)
UNION ALL
(SELECT "OrderDetails"."OrderID"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "OrderDetails"."ProductID" = 38);
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()
while row is not None:
print("Order ID:", row[0])
row = cursor.fetchone()Order ID: 10268
Order ID: 10290
Order ID: 10305
Order ID: 10354
Order ID: 10360
Order ID: 10369
Order ID: 10382
Order ID: 10400
Order ID: 10440
Order ID: 10329
Order ID: 10351
Order ID: 10353
Order ID: 10360
Order ID: 10372
Order ID: 10417
Order ID: 10424Let’s see if there are any OrderIDs that include both ProductIDs priced at over $100.00.
sql_select = """
(SELECT "OrderDetails"."OrderID"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "OrderDetails"."ProductID" = 29)
INTERSECT
(SELECT "OrderDetails"."OrderID"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "OrderDetails"."ProductID" = 38);
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()
while row is not None:
print("Order ID:", row[0])
row = cursor.fetchone()Order ID: 10360Let’s see if there are any OrderIDs that include ProductID 29, but not include ProductID 38.
sql_select = """
(SELECT "OrderDetails"."OrderID"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "OrderDetails"."ProductID" = 29)
EXCEPT
(SELECT "OrderDetails"."OrderID"
FROM "Products", "OrderDetails"
WHERE "Products"."ProductID" = "OrderDetails"."ProductID"
AND "OrderDetails"."ProductID" = 38);
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()
while row is not None:
print("Order ID:", row[0])
row = cursor.fetchone()Order ID: 10354
Order ID: 10382
Order ID: 10305
Order ID: 10268
Order ID: 10440
Order ID: 10400
Order ID: 10369
Order ID: 10290sql_select = """
SELECT *
FROM "Products"
INNER JOIN "Categories"
ON "Products"."CategoryID" = "Categories"."CategoryID"
ORDER BY "ProductName" ASC
LIMIT 10;
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()
while row is not None:
print(
"{",
"\"ProductID\":", row[0], ",",
"\"ProductName\":", "\"" + row[1] + "\",",
"\"SupplierID\":", row[2], ",",
"\"CategoryID\":", row[3], ",",
"\"Unit\":", "\"" + row[4] + "\",",
"\"Price\":", row[5], ",",
"\"CategoryID\":", row[6], ",",
"\"CategoryName\":", "\"" + row[7] + "\",",
"\"CategoryDescription\":", "\"" + row[8] + "\"",
"}")
row = cursor.fetchone(){ "ProductID": 17 , "ProductName": "Alice Mutton", "SupplierID": 7 , "CategoryID": 6 , "Unit": "20 - 1 kg tins", "Price": 39.0 , "CategoryID": 6 , "CategoryName": "Meat/Poultry", "CategoryDescription": "Prepared meats" }
{ "ProductID": 3 , "ProductName": "Aniseed Syrup", "SupplierID": 1 , "CategoryID": 2 , "Unit": "12 - 550 ml bottles", "Price": 10.0 , "CategoryID": 2 , "CategoryName": "Condiments", "CategoryDescription": "Sweet and savory sauces, relishes, spreads, and seasonings" }
{ "ProductID": 40 , "ProductName": "Boston Crab Meat", "SupplierID": 19 , "CategoryID": 8 , "Unit": "24 - 4 oz tins", "Price": 18.4 , "CategoryID": 8 , "CategoryName": "Seafood", "CategoryDescription": "Seaweed and fish" }
{ "ProductID": 60 , "ProductName": "Camembert Pierrot", "SupplierID": 28 , "CategoryID": 4 , "Unit": "15 - 300 g rounds", "Price": 34.0 , "CategoryID": 4 , "CategoryName": "Dairy Products", "CategoryDescription": "Cheeses" }
{ "ProductID": 18 , "ProductName": "Carnarvon Tigers", "SupplierID": 7 , "CategoryID": 8 , "Unit": "16 kg pkg.", "Price": 62.5 , "CategoryID": 8 , "CategoryName": "Seafood", "CategoryDescription": "Seaweed and fish" }
{ "ProductID": 1 , "ProductName": "Chais", "SupplierID": 1 , "CategoryID": 1 , "Unit": "10 boxes x 20 bags", "Price": 18.0 , "CategoryID": 1 , "CategoryName": "Beverages", "CategoryDescription": "Soft drinks, coffees, teas, beers, and ales" }
{ "ProductID": 2 , "ProductName": "Chang", "SupplierID": 1 , "CategoryID": 1 , "Unit": "24 - 12 oz bottles", "Price": 19.0 , "CategoryID": 1 , "CategoryName": "Beverages", "CategoryDescription": "Soft drinks, coffees, teas, beers, and ales" }
{ "ProductID": 39 , "ProductName": "Chartreuse verte", "SupplierID": 18 , "CategoryID": 1 , "Unit": "750 cc per bottle", "Price": 18.0 , "CategoryID": 1 , "CategoryName": "Beverages", "CategoryDescription": "Soft drinks, coffees, teas, beers, and ales" }
{ "ProductID": 4 , "ProductName": "Chef Anton's Cajun Seasoning", "SupplierID": 2 , "CategoryID": 2 , "Unit": "48 - 6 oz jars", "Price": 22.0 , "CategoryID": 2 , "CategoryName": "Condiments", "CategoryDescription": "Sweet and savory sauces, relishes, spreads, and seasonings" }
{ "ProductID": 5 , "ProductName": "Chef Anton's Gumbo Mix", "SupplierID": 2 , "CategoryID": 2 , "Unit": "36 boxes", "Price": 21.35 , "CategoryID": 2 , "CategoryName": "Condiments", "CategoryDescription": "Sweet and savory sauces, relishes, spreads, and seasonings" }sql_select = """
SELECT "CategoryName", AVG("Price")
FROM "Products"
INNER JOIN "Categories"
ON "Products"."CategoryID" = "Categories"."CategoryID"
GROUP BY "CategoryName";
"""
cursor.execute(query = sql_select)
row = cursor.fetchone()Disconnecting from database server
cursor.close()
db_connection.close()Implementing on the Cloud With Amazon RDS
Implementing on the Cloud With Azure SQL Database
Implementing on the Cloud With GCP Cloud SQL
Applied Computing