Table of Contents

• 1  Wikibooks SQL Exercise: Pieces and Proviers
• 2  Load the libraries
• 3  Utility Functions
• 4  Connect to postgres using psycopg2
• 5  Create Tables in the database
  • 5.1  data: Pieces
  • 5.2  data: Providers
• 6  data: Provides
  • 6.1  data: combined data
  • 6.2  merge two dataframes using pyspark
  • 6.3  spark dataframe attributes
• 7  Create SQL table from spark dataframes
• 8  Exercise Questions
  • 8.1  1. Select the name of all the pieces.
  • 8.2  Select all the providers' data.
  • 8.3  Obtain the average price of each piece (show only the piece code and the average price).
  • 8.4  4. Obtain the names of all providers who supply piece 1.
  • 8.5  5. Select the name of pieces provided by provider with code "HAL".
  • 8.6  6. For each piece, find the most expensive offering of that piece and include the piece name, provider name, and price
  • 8.7  7. Add an entry to the database to indicate that "Skellington Supplies" (code "TNBC") will provide sprockets (code "1") for 7 cents each.
  • 8.8  8. Increase all prices by one cent.
  • 8.9  9. Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply bolts (code 4).
  • 8.10  10. Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply any pieces (the provider should still remain in the database).
• 9  Time Taken

Wikibooks SQL Exercise: Pieces and Proviers

• https://en.wikibooks.org/wiki/SQL_Exercises
• https://en.wikibooks.org/wiki/SQL_Exercises/Pieces_and_providers

Load the libraries

import time
import os
time_start_notebook = time.time()

# my local library
import sys
sys.path.append("/Users/poudel/Dropbox/a00_Bhishan_Modules/")
sys.path.append("/Users/poudel/Dropbox/a00_Bhishan_Modules/bhishan")
from bhishan import bp

import numpy as np
import pandas as pd

import sqlite3
import psycopg2
import findspark
import pyspark

%load_ext watermark
%watermark -a "Bhishan Poudel" -d -v -m
print()
%watermark -iv

Author: Bhishan Poudel

Python implementation: CPython
Python version       : 3.7.7
IPython version      : 7.16.1

Compiler    : Clang 4.0.1 (tags/RELEASE_401/final)
OS          : Darwin
Release     : 19.6.0
Machine     : x86_64
Processor   : i386
CPU cores   : 4
Architecture: 64bit


findspark: 1.4.2
bhishan  : 0.4
pyspark  : 3.1.2
sqlite3  : 2.6.0
sys      : 3.7.7 (default, May  6 2020, 04:59:01) 
[Clang 4.0.1 (tags/RELEASE_401/final)]
numpy    : 1.17.5
psycopg2 : 2.8.5
pandas   : 1.0.5

# pyspark settings
import os
import findspark

HOME = os.path.expanduser('~')
findspark.init(HOME + "/Softwares/Spark/spark-3.1.2-bin-hadoop3.2")

# Set spark environments
os.environ['PYSPARK_PYTHON'] = f'{HOME}/opt/miniconda3/envs/spk/bin/python'
os.environ['PYSPARK_DRIVER_PYTHON'] = f'{HOME}/opt/miniconda3/envs/spk/bin/python'

import pyspark
from pyspark import SparkConf, SparkContext, SQLContext
from pyspark.sql.session import SparkSession
from pyspark.sql.functions import col
from pyspark.sql.functions import col as _col
from pyspark.sql.functions import udf # @udf("integer") def myfunc(x,y): return x - y
from pyspark.sql import functions as F # stddev format_number date_format, dayofyear, when
from pyspark.sql.window import Window
from pyspark.sql.types import StructField, StringType, IntegerType,  FloatType, StructType
from pyspark.sql.functions import (mean as _mean, min as _min,
                                   max as _max, avg as _avg,
                                   when as _when
                                  )


spark = SparkSession\
        .builder\
        .appName("bhishan")\
        .getOrCreate()

sc = spark.sparkContext
sqlContext = pyspark.SQLContext(sc) # spark_df = sqlContext.createDataFrame(pandas_df)
sc.setLogLevel("INFO")
print(pyspark.__version__)

3.1.2

from pyspark import SparkConf, SparkContext, SQLContext

spark = pyspark.sql.SparkSession.builder.appName('app').getOrCreate()
sc = spark.sparkContext
sqlContext = SQLContext(sc) # spark_df = sqlContext.createDataFrame(pandas_df)
sc.setLogLevel("INFO")

Utility Functions

def f(q,n=None):
    return pd.read_sql(q,con).head(n)

def s(q,n=5):
    sdf = spark.sql(q)
    print(f'nrows: {sdf.count()}, ncols: {len(sdf.columns)}')
    return sdf.show(n)

def l(table,n=1):
    if isinstance(table,str):
        q = f'select * from {table} limit {n}'
        display (pd.read_sql(q,con))
    
    if isinstance(table,list):
        for t in table:
            q = f'select * from {t} limit {n}'
            display(pd.read_sql(q,con).style.set_caption(t))

def c(q,table):          
    q = q.strip()
    cur.execute(q)
    con.commit()

    dfx = pd.read_sql(f'select * from {table}',con)
    display(dfx)

Connect to postgres using psycopg2

# postgres configs
import os, json
data = json.load(open(os.path.expanduser('~/.config/postgres/credentials.json')))
pw,port = data['password'], data['port'] # port is 5432
dbname = 'wbwarehouse' # make sure pgadmin is running and given database is there.

con = psycopg2.connect(host = "localhost",database=dbname,
                       user = "postgres",password = pw)

cur = con.cursor()

Create Tables in the database

cur.execute(
"""
 CREATE TABLE Pieces (
   Code INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL
 )
""")

cur.execute(
"""
 CREATE TABLE Providers (
  Code TEXT PRIMARY KEY NOT NULL,
  Name TEXT NOT NULL
 )
""")

cur.execute(
"""
 CREATE TABLE Provides (
   Piece INTEGER  
     CONSTRAINT fk_Pieces_Code REFERENCES Pieces(Code),
   Provider TEXT
     CONSTRAINT fk_Providers_Code REFERENCES Providers(Code),
   Price INTEGER NOT NULL,
   PRIMARY KEY(Piece, Provider)
 )
""")

data = """
 INSERT INTO Providers(Code, Name) VALUES('HAL','Clarke Enterprises');
 INSERT INTO Providers(Code, Name) VALUES('RBT','Susan Calvin Corp.');
 INSERT INTO Providers(Code, Name) VALUES('TNBC','Skellington Supplies');
 
 INSERT INTO Pieces(Code, Name) VALUES(1,'Sprocket');
 INSERT INTO Pieces(Code, Name) VALUES(2,'Screw');
 INSERT INTO Pieces(Code, Name) VALUES(3,'Nut');
 INSERT INTO Pieces(Code, Name) VALUES(4,'Bolt');
 
 INSERT INTO Provides(Piece, Provider, Price) VALUES(1,'HAL',10);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(1,'RBT',15);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(2,'HAL',20);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(2,'RBT',15);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(2,'TNBC',14);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(3,'RBT',50);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(3,'TNBC',45);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(4,'HAL',5);
 INSERT INTO Provides(Piece, Provider, Price) VALUES(4,'RBT',7);
""".split('\n')

for i,line in enumerate(data):
    if line.strip():
        cur.execute(line.strip())
        
        
con.commit()

data: Pieces

pd.read_sql("select * from Pieces", con).pipe(print)

   code      name
0     1  Sprocket
1     2     Screw
2     3       Nut
3     4      Bolt

dfp = pd.DataFrame({'Code': [1, 2, 3, 4],
          'Name': ['Sprocket', 'Screw', 'Nut', 'Bolt']})
dfp

	Code	Name
0	1	Sprocket
1	2	Screw
2	3	Nut
3	4	Bolt

dfp.dtypes

Code     int64
Name    object
dtype: object

schema = StructType([
    StructField('Code',IntegerType(),True),
    StructField('Name',StringType(),True)
    ])

sdfp = sqlContext.createDataFrame(dfp, schema)
sdfp.show()

+----+--------+
|Code|    Name|
+----+--------+
|   1|Sprocket|
|   2|   Screw|
|   3|     Nut|
|   4|    Bolt|
+----+--------+

data: Providers

pd.read_sql("select * from Providers", con).pipe(print)

   code                  name
0   HAL    Clarke Enterprises
1   RBT    Susan Calvin Corp.
2  TNBC  Skellington Supplies

dfr = pd.DataFrame({'Code': ['HAL', 'RBT', 'TNBC'],
          'Name': ['Clarke Enterprises', 'Susan Calvin Corp.', 'Skellington Supplies']})
dfr

	Code	Name
0	HAL	Clarke Enterprises
1	RBT	Susan Calvin Corp.
2	TNBC	Skellington Supplies

dfr.dtypes

Code    object
Name    object
dtype: object

schema = StructType([
    StructField('Code',StringType(),True),
    StructField('Name',StringType(),True)
    ])

sdfr = sqlContext.createDataFrame(dfr, schema)
sdfr.show()

+----+--------------------+
|Code|                Name|
+----+--------------------+
| HAL|  Clarke Enterprises|
| RBT|  Susan Calvin Corp.|
|TNBC|Skellington Supplies|
+----+--------------------+

data: Provides

pd.read_sql("select * from Provides", con).pipe(print)

   piece provider  price
0      1      HAL     10
1      1      RBT     15
2      2      HAL     20
3      2      RBT     15
4      2     TNBC     14
5      3      RBT     50
6      3     TNBC     45
7      4      HAL      5
8      4      RBT      7

dfd = pd.DataFrame({'Piece': [1, 1, 2, 2, 2, 3, 3, 4, 4],
          'Provider': ['HAL', 'RBT', 'HAL', 'RBT', 'TNBC', 'RBT', 'TNBC', 'HAL', 'RBT'],
          'Price': [10, 15, 20, 15, 14, 50, 45, 5, 7]})
# dfd

dfd.dtypes

Piece        int64
Provider    object
Price        int64
dtype: object

schema = StructType([
    StructField('Piece',IntegerType(),True),
    StructField('Provider',StringType(),True),
    StructField('Price',IntegerType(),True),
    ])

sdfd = sqlContext.createDataFrame(dfd, schema)
sdfd.show()

+-----+--------+-----+
|Piece|Provider|Price|
+-----+--------+-----+
|    1|     HAL|   10|
|    1|     RBT|   15|
|    2|     HAL|   20|
|    2|     RBT|   15|
|    2|    TNBC|   14|
|    3|     RBT|   50|
|    3|    TNBC|   45|
|    4|     HAL|    5|
|    4|     RBT|    7|
+-----+--------+-----+

data: combined data

def ll(n=1):
    l(['Pieces', 'Providers','Provides'],n)
    
ll()

Pieces

	code	name
0	1	Sprocket

Providers

	code	name
0	HAL	Clarke Enterprises

Provides

	piece	provider	price
0	1	HAL	10

df = dfp.merge(dfd,left_on='Code', right_on='Piece',suffixes=['_p','_d'])
df = df.merge(dfr,left_on='Provider', right_on='Code',suffixes=['_pd','_r'])
df = df.rename(columns={'Name_pd': 'Name_p', 'Code_pd': 'Code_p'})

df

	Code_p	Name_p	Piece	Provider	Price	Code_r	Name_r
0	1	Sprocket	1	HAL	10	HAL	Clarke Enterprises
1	2	Screw	2	HAL	20	HAL	Clarke Enterprises
2	4	Bolt	4	HAL	5	HAL	Clarke Enterprises
3	1	Sprocket	1	RBT	15	RBT	Susan Calvin Corp.
4	2	Screw	2	RBT	15	RBT	Susan Calvin Corp.
5	3	Nut	3	RBT	50	RBT	Susan Calvin Corp.
6	4	Bolt	4	RBT	7	RBT	Susan Calvin Corp.
7	2	Screw	2	TNBC	14	TNBC	Skellington Supplies
8	3	Nut	3	TNBC	45	TNBC	Skellington Supplies

merge two dataframes using pyspark

• we need to rename common columns of two tables.

cols1 = sdfp.columns
cols2  = sdfd.columns
cols_common = set(cols1) & set(cols2)
cols1, cols2, cols_common

(['Code', 'Name'], ['Piece', 'Provider', 'Price'], set())

# rename common columns of first spark dataframe.
# there no common columns

# now join two spark dataframes which have different column names.

sdf = sdfp.join(sdfd,sdfp.Code==sdfd.Piece)
sdf.show()

+----+--------+-----+--------+-----+
|Code|    Name|Piece|Provider|Price|
+----+--------+-----+--------+-----+
|   1|Sprocket|    1|     HAL|   10|
|   1|Sprocket|    1|     RBT|   15|
|   3|     Nut|    3|     RBT|   50|
|   3|     Nut|    3|    TNBC|   45|
|   4|    Bolt|    4|     HAL|    5|
|   4|    Bolt|    4|     RBT|    7|
|   2|   Screw|    2|     HAL|   20|
|   2|   Screw|    2|     RBT|   15|
|   2|   Screw|    2|    TNBC|   14|
+----+--------+-----+--------+-----+

# rename common columns of first spark dataframe.

cols1 = sdf.columns
cols2  = sdfr.columns
cols_common = set(cols1) & set(cols2)
cols1, cols2, cols_common

(['Code', 'Name', 'Piece', 'Provider', 'Price'],
 ['Code', 'Name'],
 {'Code', 'Name'})

sdf2 = sdf.withColumnRenamed('Code','Code_p').withColumnRenamed('Name','Name_p')
sdfr2 = sdfr.withColumnRenamed('Code','Code_r').withColumnRenamed('Name','Name_r')
sdf2.show(1)

+------+--------+-----+--------+-----+
|Code_p|  Name_p|Piece|Provider|Price|
+------+--------+-----+--------+-----+
|     1|Sprocket|    1|     HAL|   10|
+------+--------+-----+--------+-----+
only showing top 1 row

sdf = sdf2.join(sdfr2,sdf2.Provider==sdfr2.Code_r)
sdf.show()

+------+--------+-----+--------+-----+------+--------------------+
|Code_p|  Name_p|Piece|Provider|Price|Code_r|              Name_r|
+------+--------+-----+--------+-----+------+--------------------+
|     1|Sprocket|    1|     HAL|   10|   HAL|  Clarke Enterprises|
|     4|    Bolt|    4|     HAL|    5|   HAL|  Clarke Enterprises|
|     2|   Screw|    2|     HAL|   20|   HAL|  Clarke Enterprises|
|     3|     Nut|    3|    TNBC|   45|  TNBC|Skellington Supplies|
|     2|   Screw|    2|    TNBC|   14|  TNBC|Skellington Supplies|
|     1|Sprocket|    1|     RBT|   15|   RBT|  Susan Calvin Corp.|
|     3|     Nut|    3|     RBT|   50|   RBT|  Susan Calvin Corp.|
|     4|    Bolt|    4|     RBT|    7|   RBT|  Susan Calvin Corp.|
|     2|   Screw|    2|     RBT|   15|   RBT|  Susan Calvin Corp.|
+------+--------+-----+--------+-----+------+--------------------+

spark dataframe attributes

bp.show_methods(sdf,4)
# count, fillna, filter, where, groubpy, first, corr,
# missing than pandas: max, min, sum, etc.

	0	1	2	3
0	agg	drop_duplicates	localCheckpoint	sql_ctx
1	alias	dropna	mapInPandas	stat
2	approxQuantile	dtypes	na	storageLevel
3	cache	exceptAll	orderBy	subtract
4	checkpoint	explain	persist	summary
5	coalesce	fillna	printSchema	tail
6	colRegex	filter	randomSplit	take
7	collect	first	rdd	toDF
8	columns	foreach	registerTempTable	toJSON
9	corr	foreachPartition	repartition	toLocalIterator
10	count	freqItems	repartitionByRange	toPandas
11	cov	groupBy	replace	transform
12	createGlobalTempView	groupby	rollup	union
13	createOrReplaceGlobalTempView	head	sameSemantics	unionAll
14	createOrReplaceTempView	hint	sample	unionByName
15	createTempView	inputFiles	sampleBy	unpersist
16	crossJoin	intersect	schema	where
17	crosstab	intersectAll	select	withColumn
18	cube	isLocal	selectExpr	withColumnRenamed
19	describe	isStreaming	semanticHash	withWatermark
20	distinct	is_cached	show	write
21	drop	join	sort	writeStream
22	dropDuplicates	limit	sortWithinPartitions	writeTo

Create SQL table from spark dataframes

• https://spark.apache.org/docs/2.2.0/sql-programming-guide.html

Global Temporary View

Temporary views in Spark SQL are session-scoped and will disappear if the session that creates it terminates. If you want to have a temporary view that is shared among all sessions and keep alive until the Spark application terminates, you can create a global temporary view. Global temporary view is tied to a system preserved database global_temp, and we must use the qualified name to refer it, e.g. SELECT * FROM global_temp.view1.

# Register the DataFrame as a SQL temporary view
sdfp.createOrReplaceTempView("Pieces")
sdfd.createOrReplaceTempView("Provides")
sdfr.createOrReplaceTempView("Providers")
sdf.createOrReplaceTempView("sdf")

spark.sql('select * from Pieces limit 2').show()

+----+--------+
|Code|    Name|
+----+--------+
|   1|Sprocket|
|   2|   Screw|
+----+--------+

Exercise Questions

1. Select the name of all the pieces.

q = 'select * from Pieces'
s(q)

nrows: 4, ncols: 2
+----+--------+
|Code|    Name|
+----+--------+
|   1|Sprocket|
|   2|   Screw|
|   3|     Nut|
|   4|    Bolt|
+----+--------+

Select all the providers' data.

q = 'select * from Providers'
s(q)

nrows: 3, ncols: 2
+----+--------------------+
|Code|                Name|
+----+--------------------+
| HAL|  Clarke Enterprises|
| RBT|  Susan Calvin Corp.|
|TNBC|Skellington Supplies|
+----+--------------------+

Obtain the average price of each piece (show only the piece code and the average price).

sdfd.groupby('Piece').agg({'Price':'mean'}).show()

+-----+------------------+
|Piece|        avg(Price)|
+-----+------------------+
|    1|              12.5|
|    3|              47.5|
|    4|               6.0|
|    2|16.333333333333332|
+-----+------------------+

q = """
select piece, avg(price)
from Provides
group by Piece
"""
# f(q)
s(q)

nrows: 4, ncols: 2
+-----+------------------+
|piece|        avg(price)|
+-----+------------------+
|    1|              12.5|
|    3|              47.5|
|    4|               6.0|
|    2|16.333333333333332|
+-----+------------------+

4. Obtain the names of all providers who supply piece 1.

sdf.show(1)

+------+--------+-----+--------+-----+------+------------------+
|Code_p|  Name_p|Piece|Provider|Price|Code_r|            Name_r|
+------+--------+-----+--------+-----+------+------------------+
|     1|Sprocket|    1|     HAL|   10|   HAL|Clarke Enterprises|
+------+--------+-----+--------+-----+------+------------------+
only showing top 1 row

sdf[sdf.Piece==1].select('Name_r').show(2)

+------------------+
|            Name_r|
+------------------+
|Clarke Enterprises|
|Susan Calvin Corp.|
+------------------+

l(['Provides','Providers'])

Provides

	piece	provider	price
0	1	HAL	10

Providers

	code	name
0	HAL	Clarke Enterprises

q = """
select name from Providers where code in
(select provider from Provides where piece=1)
"""
s(q)

nrows: 2, ncols: 1
+------------------+
|              name|
+------------------+
|Clarke Enterprises|
|Susan Calvin Corp.|
+------------------+

q = """
select r.name from Providers r
join Provides d
on r.code=d.provider
and d.piece=1
"""
s(q)

nrows: 2, ncols: 1
+------------------+
|              name|
+------------------+
|Clarke Enterprises|
|Susan Calvin Corp.|
+------------------+

5. Select the name of pieces provided by provider with code "HAL".

# ll()

# using join
q = """
select p.name
from Pieces p
join Provides d on p.code=d.piece
where d.provider='HAL'
"""

s(q)

nrows: 3, ncols: 1
+--------+
|    name|
+--------+
|Sprocket|
|    Bolt|
|   Screw|
+--------+

# using IN subquery
q = """
select name
from Pieces
where code in

(select piece from Provides where provider='HAL')
"""

s(q)

nrows: 3, ncols: 1
+--------+
|    name|
+--------+
|Sprocket|
|    Bolt|
|   Screw|
+--------+

# using EXISTS subquery
q = """
select name
from Pieces
where exists

(select * from Provides
 where provider='HAL'
 and piece=Pieces.code
 )
"""

s(q)

nrows: 3, ncols: 1
+--------+
|    name|
+--------+
|Sprocket|
|    Bolt|
|   Screw|
+--------+

6. For each piece, find the most expensive offering of that piece and include the piece name, provider name, and price

(note that there could be two providers who supply the same piece at the most expensive price).

df

	Code_p	Name_p	Piece	Provider	Price	Code_r	Name_r
0	1	Sprocket	1	HAL	10	HAL	Clarke Enterprises
1	2	Screw	2	HAL	20	HAL	Clarke Enterprises
2	4	Bolt	4	HAL	5	HAL	Clarke Enterprises
3	1	Sprocket	1	RBT	15	RBT	Susan Calvin Corp.
4	2	Screw	2	RBT	15	RBT	Susan Calvin Corp.
5	3	Nut	3	RBT	50	RBT	Susan Calvin Corp.
6	4	Bolt	4	RBT	7	RBT	Susan Calvin Corp.
7	2	Screw	2	TNBC	14	TNBC	Skellington Supplies
8	3	Nut	3	TNBC	45	TNBC	Skellington Supplies

df.groupby('Piece')['Price'].max()

Piece
1    15
2    20
3    50
4     7
Name: Price, dtype: int64

df['Piece_Price_max'] = df.groupby('Piece')['Price'].transform('max')
df

	Code_p	Name_p	Piece	Provider	Price	Code_r	Name_r	Piece_Price_max
0	1	Sprocket	1	HAL	10	HAL	Clarke Enterprises	15
1	2	Screw	2	HAL	20	HAL	Clarke Enterprises	20
2	4	Bolt	4	HAL	5	HAL	Clarke Enterprises	7
3	1	Sprocket	1	RBT	15	RBT	Susan Calvin Corp.	15
4	2	Screw	2	RBT	15	RBT	Susan Calvin Corp.	20
5	3	Nut	3	RBT	50	RBT	Susan Calvin Corp.	50
6	4	Bolt	4	RBT	7	RBT	Susan Calvin Corp.	7
7	2	Screw	2	TNBC	14	TNBC	Skellington Supplies	20
8	3	Nut	3	TNBC	45	TNBC	Skellington Supplies	50

df.query("Price==Piece_Price_max")

	Code_p	Name_p	Piece	Provider	Price	Code_r	Name_r	Piece_Price_max
1	2	Screw	2	HAL	20	HAL	Clarke Enterprises	20
3	1	Sprocket	1	RBT	15	RBT	Susan Calvin Corp.	15
5	3	Nut	3	RBT	50	RBT	Susan Calvin Corp.	50
6	4	Bolt	4	RBT	7	RBT	Susan Calvin Corp.	7

ll(1)

Pieces

	code	name
0	1	Sprocket

Providers

	code	name
0	HAL	Clarke Enterprises

Provides

	piece	provider	price
0	1	HAL	10

q = """
select p.name, r.name, d.price
from Pieces p
join Provides  d on p.code = piece
join Providers r on r.code = provider

where d.price =
   (
     select max(price) from Provides 
     where piece = p.code
   )


"""

f(q)

	name	name	price
0	Sprocket	Susan Calvin Corp.	15
1	Screw	Clarke Enterprises	20
2	Nut	Susan Calvin Corp.	50
3	Bolt	Susan Calvin Corp.	7

7. Add an entry to the database to indicate that "Skellington Supplies" (code "TNBC") will provide sprockets (code "1") for 7 cents each.

q = """
insert into Provides
values (1,'TNBC',7)
"""

c(q,'Provides')

	piece	provider	price
0	1	HAL	10
1	1	RBT	15
2	2	HAL	20
3	2	RBT	15
4	2	TNBC	14
5	3	RBT	50
6	3	TNBC	45
7	4	HAL	5
8	4	RBT	7
9	1	TNBC	7

8. Increase all prices by one cent.

q = """
update Provides set price=price+1;
"""

c(q,'Provides')

	piece	provider	price
0	1	HAL	11
1	1	RBT	16
2	2	HAL	21
3	2	RBT	16
4	2	TNBC	15
5	3	RBT	51
6	3	TNBC	46
7	4	HAL	6
8	4	RBT	8
9	1	TNBC	8

9. Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply bolts (code 4).

q = """
delete from Provides
where provider='RBT' and piece=4
"""

c(q,'Provides')

	piece	provider	price
0	1	HAL	11
1	1	RBT	16
2	2	HAL	21
3	2	RBT	16
4	2	TNBC	15
5	3	RBT	51
6	3	TNBC	46
7	4	HAL	6
8	1	TNBC	8

10. Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply any pieces (the provider should still remain in the database).

q = """
delete from Provides
where provider='RBT'
"""

c(q,'Provides')

	piece	provider	price
0	1	HAL	11
1	2	HAL	21
2	2	TNBC	15
3	3	TNBC	46
4	4	HAL	6
5	1	TNBC	8

Time Taken

time_taken = time.time() - time_start_notebook
h,m = divmod(time_taken,60*60)
print('Time taken: {:.0f} hr {:.0f} min {:.0f} secs'.format(h, *divmod(m,60)))

Time taken: 0 hr 0 min 34 secs