def execute(c: psycopg2.extensions.cursor) -> None:
c.execute(q, v)
# Check query UUIDs against rows that would actually be deleted.
deleted_uuid_set = set(r[0] for r in c.fetchall())
for uid in str_uuid_set:
if uid not in deleted_uuid_set:
raise KeyError(uid)
def _get_id_list(cur: psycopg2.extensions.cursor, column: str, table: str,
table_val: str, val: str):
"""Return a stringified list of ids for the given table linked to the business_id."""
val = val.replace('(', '').replace(')', '')
cur.execute(f'select {column} from {table} where {table_val} in ({val})')
id_list = []
for _id in cur.fetchall():
id_list.append(_id[0])
return str(id_list).replace('[', '(').replace(']', ')')
def task_10_list_first_10_customers(cur: psycopg2.extensions.cursor) -> list:
"""
List first 10 customers from the table
Results: 10 records
"""
cur.execute("SELECT * FROM customers LIMIT 10;")
return cur.fetchall()
def head_table(table: str, cur: psycopg2.extensions.cursor, n: int = 5):
"""
Return the n-first rows of a given table
"""
try:
cur.execute(f"SELECT * FROM {table} LIMIT {n}")
return [e for e in cur.fetchall()]
except psycopg2.Error as e:
print(f"Error: {e}")
def map_ids_intid(cursor: psycopg2.extensions.cursor, map_dict: dict) -> None:
''' creates a dictionary that maps street segment intid to id '''
query ="""
SELECT
id,
intid
FROM streetcenterlines;
"""
cursor.execute(query)
for i in cursor.fetchall():
map_dict[int(i[1])] = int(i[0])
def task_3_list_customers_in_germany(cur: psycopg2.extensions.cursor) -> list:
"""
List the customers in Germany
Args:
cur: psycopg cursor
Returns: 11 records
"""
cur.execute("SELECT * FROM customers WHERE country = 'Germany'")
return cur.fetchall()
def get_timestamps(cur:psycopg2.extensions.cursor=None)->list:
"""
Get list of timestamps from table
:param:
cur:psycopg2.extensions.cursor - connection to database
table_name:str - table to get timestamps from
:return:
list of timsetamps
"""
get_timestamps_stmt="SELECT DISTINCT(timestamp) FROM aws_downloads ORDER BY timestamp;"
cur.execute(get_timestamps_stmt)
return cur.fetchall()
def task_2_list_all_customers(cur: psycopg2.extensions.cursor) -> list:
"""
Get all records from table Customers
Args:
cur: psycopg cursor
Returns: 91 records
"""
cur.execute("SELECT * FROM customers;")
return cur.fetchall()
def task_11_list_customers_starting_from_11th(
cur: psycopg2.extensions.cursor) -> list:
"""
List all customers starting from 11th record
Args:
cur: psycopg cursor
Returns: 11 records
"""
cur.execute("SELECT * FROM customers ORDER BY customerid OFFSET 11 ROWS;")
return cur.fetchall()
def create_road_geometry(cursor: psycopg2.extensions.cursor, road: list) -> str:
''' create a linestring geom for a road from a list of its segments '''
query ="""
SELECT
ST_LineMerge(geom)
FROM streetcenterlines
WHERE
"""
# modify query list out each relevant segment intid
if road:
query = "{}\n\tintid = {}".format(query,road[0])
for segment in road[1:]:
query = "{}\n\tOR intid = {}".format(query,segment)
query = '{};'.format(query)
cursor.execute(query)
geometries = [ i[0] for i in cursor.fetchall() ]
# join the first two road segment geometries, slice off the first geometry in the list, then overwrite the new first geometry in the list with the joined result
while len(geometries) > 1:
query = """
SELECT ST_LineMerge( ST_Union( ST_LineMerge('{}'), ST_LineMerge('{}') ) ) ;
""".format(geometries[0],geometries[1])
cursor.execute(query)
geometries = geometries[1:]
g = cursor.fetchall()[0][0]
geometries[0] = g
query = """
Select ST_Multi( '{}' );
""".format(geometries[0])
cursor.execute(query)
geometries[0] = cursor.fetchall()[0][0]
return geometries[0]
def task_7_list_supplier_countries_in_desc_order(
cur: psycopg2.extensions.cursor) -> list:
"""
List all supplier countries in descending order
Args:
cur: psycopg cursor
Returns: 29 records in descending order
"""
cur.execute("SELECT country FROM suppliers ORDER BY country DESC;")
return cur.fetchall()
def task_6_list_all_supplier_countries(
cur: psycopg2.extensions.cursor) -> list:
"""
List all supplier countries
Args:
cur: psycopg cursor
Returns: 29 records
"""
cur.execute("SELECT country FROM suppliers;")
return cur.fetchall()
def get_street_length(cursor: psycopg2.extensions.cursor,
street_id: int) -> str:
''' street length in feet '''
query = """
SELECT
ST_Length(ST_AsText(ST_LineMerge(geom)))
FROM streetcenterlines
WHERE id = {};
"""
cursor.execute(query.format(street_id))
record = cursor.fetchall()
if record and record[0] and record[0][0]:
return float(record[0][0])
else:
return None
def add_gps(crash_data: dict, crash: str,
cursor: psycopg2.extensions.cursor) -> None:
''' Adds the lattitude and longitude of each crash to the crash_data dictionary for a specified crash '''
if not crash_data[crash]['distance'] or not crash_data[crash]['direction']:
crash_data[crash]['int_id'] = None
crash_data[crash]['longitude'] = None
crash_data[crash]['latitude'] = None
return
try:
distance = int(crash_data[crash]['distance'])
except:
crash_data[crash]['int_id'] = None
crash_data[crash]['longitude'] = None
crash_data[crash]['latitude'] = None
return
direction = crash_data[crash]['direction']
if direction == 'At':
direction = 'South'
query = """
SELECT
Q.id,
pointy(Q.g) AS y,
pointx(Q.g) AS x
FROM (SELECT
id,
findcrashlocation(id, '{}', {}) AS g
FROM intersections
WHERE
intnum = {}
) AS Q;
""".format(direction, distance, crash_data[crash]['intersection_id'])
cursor.execute(query)
record = cursor.fetchall()
if not record or not record[0] or not record[0][0] or not record[0][
1] or not record[0][2]:
crash_data[crash]['int_id'] = None
crash_data[crash]['longitude'] = None
crash_data[crash]['latitude'] = None
return
crash_data[crash]['int_id'] = record[0][0]
crash_data[crash]['latitude'] = record[0][1]
crash_data[crash]['longitude'] = record[0][2]
def get_intersection_map(cursor: psycopg2.extensions.cursor) -> dict:
''' get a map of street segment intid to (frominteri, tointeri, streetclas) '''
intersection_map = dict()
query ="""
SELECT
intid,
frominteri,
tointerid,
streetclas
FROM streetcenterlines;
"""
cursor.execute(query)
for i in cursor.fetchall():
intersection_map[int(i[0])] = (int(i[1]), int(i[2]), i[3])
return intersection_map
def task_12_list_suppliers_from_specified_countries(
cur: psycopg2.extensions.cursor) -> list:
"""
List all suppliers from the USA, UK, OR Japan
Args:
cur: psycopg cursor
Returns: 8 records
"""
sql = """
SELECT supplierid, suppliername, contactname, city, country
FROM suppliers
WHERE country IN ('USA','UK','Japan');
"""
cur.execute(sql)
return cur.fetchall()
def get_connections(cursor: psycopg2.extensions.cursor) -> list:
''' get a list of all connected street segments (physically connected segments that share a name) where each element contains information on a pair of connected segments '''
query ="""
SELECT
STREETS.name,
STREETS.id,
STREETS.intid,
streetcenterlines.id,
streetcenterlines.intid,
STREETS.a,
STREETS.b,
STREETS.streetclas
FROM
(SELECT
streetcenterlines.id,
streetcenterlines.intid,
streetcenterlines.frominteri AS a,
streetcenterlines.tointerid AS b,
streetcenterlines.fullname AS name,
streetcenterlines.streetclas AS streetclas
FROM streetcenterlines
WHERE
LOWER(streetcenterlines.munileft) = 'sj'
OR LOWER(streetcenterlines.muniright) = 'sj'
) AS STREETS,
streetcenterlines
WHERE
(streetcenterlines.frominteri = STREETS.a
OR streetcenterlines.tointerid = STREETS.a
OR streetcenterlines.frominteri = STREETS.b
OR streetcenterlines.tointerid = STREETS.b
)
AND STREETS.name = streetcenterlines.fullname
AND STREETS.id != streetcenterlines.id
AND (
LOWER(streetcenterlines.munileft) = 'sj'
OR LOWER(streetcenterlines.muniright) = 'sj'
)
AND STREETS.streetclas = streetcenterlines.streetclas
;
"""
cursor.execute(query)
return cursor.fetchall()
def task_8_count_customers_by_city(cur: psycopg2.extensions.cursor) -> list:
"""
List the number of customers in each city
Args:
cur: psycopg cursor
Returns: 69 records in descending order
"""
sql = '''
SELECT COUNT(customerid), city
FROM customers
GROUP BY city
ORDER BY city DESC;
'''
cur.execute(sql)
return cur.fetchall()
def task_9_count_customers_by_country_with_than_10_customers(
cur: psycopg2.extensions.cursor) -> list:
"""
List the number of customers in each country. Only include countries with more than 10 customers.
Args:
cur: psycopg cursor
Returns: 3 records
"""
sql = '''
SELECT COUNT(customerid), country
FROM customers
GROUP BY country
HAVING COUNT(*) > 10;
'''
cur.execute(sql)
return cur.fetchall()
def street_list_from_crash(crash_data: dict, crash: int,
cursor: psycopg2.extensions.cursor) -> list:
''' returns a list of all streets affected by a given crash '''
street_list = list()
int_num = crash_data[crash]['intersection_id']
direction = crash_data[crash]['direction']
if not int_num or not direction:
return street_list
if direction == 'At':
query = all_streets_from_inter(int_num)
else:
query = get_street_from_inter(int_num, direction)
cursor.execute(query)
record = cursor.fetchall()
for street in record:
if street[0]:
street_list.append(int(street[0]))
return street_list
def task_15_list_customers_with_any_order_or_not(
cur: psycopg2.extensions.cursor) -> list:
"""
List all customers, whether they placed any order or not.
Args:
cur: psycopg cursor
Returns: 213 records
"""
sql = """
SELECT c.customername, c.contactname, c.country, o.orderid
FROM customers AS c
LEFT JOIN orders AS o
ON c.customerid = o.customerid
"""
cur.execute(sql)
return cur.fetchall()
def task_13_list_products_from_sweden_suppliers(
cur: psycopg2.extensions.cursor) -> list:
"""
List products with suppliers from Sweden.
Args:
cur: psycopg cursor
Returns: 3 records
"""
sql = """
SELECT p.productname
FROM products AS p
INNER JOIN suppliers AS s
ON p.supplierid = s.supplierid
WHERE s.country = 'Sweden';
"""
cur.execute(sql)
return cur.fetchall()
def task_14_list_products_with_supplier_information(
cur: psycopg2.extensions.cursor) -> list:
"""
List all products with supplier information
Args:
cur: psycopg cursor
Returns: 77 records
"""
sql = """
SET lc_monetary TO 'en_US.UTF-8';
SELECT p.productid, p.productname, p.unit, p.price, s.country, s.city, s.suppliername
FROM products AS p
INNER JOIN suppliers AS s
ON p.supplierid = s.supplierid
"""
cur.execute(sql)
return cur.fetchall()
def task_16_match_all_customers_and_suppliers_by_country(
cur: psycopg2.extensions.cursor) -> list:
"""
Match all customers and suppliers by country
Args:
cur: psycopg cursor
Returns: 194 records
"""
sql = """
SELECT c.customername, c.address, c.country AS customercountry,
s.country AS suppliercountry, s.suppliername
FROM customers AS c
FULL JOIN suppliers AS s
ON c.country = s.country
ORDER BY customercountry, suppliercountry
"""
cur.execute(sql)
return cur.fetchall()
def check_db(dbname: str = "dvf",
secrets: str = "../secrets.yml",
with_connection: bool = False,
cur: psycopg2.extensions.cursor = None):
"""
Function to check the tables existing in a specific db
"""
if cur is not None:
with_connection = True
if not with_connection:
conn = open_connection(dbname, secrets)
cur = conn.cursor()
cur.execute("""SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'""")
elements = [element[0] for element in cur.fetchall()]
if not with_connection:
cur.close()
conn.close()
return elements
def road_length(cursor: psycopg2.extensions.cursor, segments: set) -> float:
''' returns length of entire road in miles '''
total_length = 0.0
if not segments:
return total_length
segs = list(segments)
query ="""
SELECT
ST_Length(ST_AsText(ST_LineMerge(geom)))
FROM streetcenterlines
WHERE intid = {}
""".format(segs[0])
segs = segs[1:]
for seg in segs:
query = '{}\n\t OR intid = {}'.format(query,seg)
query = '{};'.format(query)
cursor.execute(query)
for i in cursor.fetchall():
total_length += float(i[0])
return total_length / FEETPERMILE
def db_rows_to_dict(cursor: psycopg2.extensions.cursor) -> List[dict]:
"""Return a dictionary of all row results from a database connection cursor"""
columns = [col[0] for col in cursor.description]
return [dict(zip(columns, row)) for row in cursor.fetchall()]
