· 6 years ago · Apr 10, 2019, 06:30 PM
1import psycopg2
2import configparser
3from urllib.parse import urlparse, uses_netloc
4
5config = configparser.ConfigParser()
6config.read('config.ini')
7connection_string = config['database']['postgres_connection']
8
9def connect_to_db(conn_str):
10 uses_netloc.append("postgres")
11 url = urlparse(conn_str)
12 conn = psycopg2.connect(database=url.path[1:],
13 user=url.username,
14 password=url.password,
15 host=url.hostname,
16 port=url.port)
17 return conn
18
19
20def initialize():
21 with conn.cursor() as cursor:
22 # foreign key for the productID, how?
23 cursor.execute('CREATE TABLE IF NOT EXISTS Customers (id SERIAL UNIQUE, firstName text, lastName text, street text, city text, state text, zip int, CONSTRAINT nameConstraint UNIQUE (firstName, lastName));')
24 cursor.execute('CREATE TABLE IF NOT EXISTS Products (id SERIAL UNIQUE, name text, price real, CONSTRAINT pNameConstraint UNIQUE (name));')
25 cursor.execute('CREATE TABLE IF NOT EXISTS Orders (id SERIAL UNIQUE, CustomerId int, ProductID int, date text);')
26 conn.commit()
27
28# displays customers on project1/templates/customers/index
29def get_customers():
30 with conn.cursor() as cursor:
31 cursor.execute('SELECT * FROM Customers;')
32 customers = list()
33 for result in cursor:
34 customers.append({'id':result[0],'firstName':result[1],'lastName':result[2],'street':result[3],'city':result[4],'state':result[5],'zip':result[6]})
35 return customers
36
37def get_customer(id):
38 with conn.cursor() as cursor:
39 cursor.execute('SELECT * FROM Customers WHERE id = %s',(id,))
40 result = cursor.fetchone()
41 return {'id':result[0],'firstName':result[1],'lastName':result[2],'street':result[3],'city':result[4],'state':result[5],'zip':result[6]}
42
43
44def upsert_customer(customer):
45 with conn.cursor() as cursor:
46 cursor.execute('INSERT INTO Customers(firstName, lastName, street, city, state, zip) VALUES (%s,%s,%s,%s,%s,%s) ON CONFLICT (firstName, lastName) DO UPDATE SET street = excluded.street, city = excluded.city, state = excluded.state, zip = excluded.zip',
47 (customer['firstName'],customer['lastName'],customer['street'],customer['city'],customer['state'],customer['zip']))
48 conn.commit()
49
50def delete_customer(id):
51 with conn.cursor() as cursor:
52 cursor.execute('DELETE FROM Customers WHERE id = %s', (id,))
53 conn.commit()
54
55# displays products on project1/templates/products/index
56def get_products():
57 with conn.cursor() as cursor:
58 cursor.execute('SELECT * FROM Products;')
59 products = list()
60 for result in cursor:
61 products.append({'id':result[0],'name':result[1],'price':result[2]})
62 return products
63
64# this works correctly now
65def get_product(id):
66 with conn.cursor() as cursor:
67 cursor.execute('SELECT * FROM Products WHERE id = %s',(id,))
68 result = cursor.fetchone()
69 return {'id':result[0],'name':result[1],'price':result[2]}
70
71# allows the price for a product to be changed. If the name is changed, the product is new (inserted).
72# the conflict could be changed to allow for a product to be renamed, but name seems pretty important for identifying a product
73# and could be part of the PK.
74def upsert_product(product):
75 with conn.cursor() as cursor:
76 cursor.execute('INSERT INTO Products(name, price) VALUES(%s, %s) ON CONFLICT (name) DO UPDATE SET price = excluded.price;', (product['name'], product['price']) )
77 conn.commit()
78
79def delete_product(id):
80 with conn.cursor() as cursor:
81 cursor.execute('DELETE FROM Products WHERE id = %s',(id,))
82 conn.commit()
83
84# needs to be joined with other tables, see index.html for orders
85def get_orders():
86 with conn.cursor() as cursor:
87 cursor.execute('SELECT * FROM Orders join customers on Orders.customerid = customers.id join products on orders.productid = products.id;')
88 orders = list()
89 for result in cursor:
90 orders.append({'product': {'id': result[11],'name': result[12] , 'price': result[13]}, 'customer': {'id':result[4], 'firstName':result[5],
91 'lastName':result[6],
92 'street':result[7], 'city':result[8], 'state': result[9], 'zip': result[10]},
93 'id': result[0], 'customerid': result[1], 'productid':result[2], 'date': result[3]})
94 return orders
95
96def get_order(id):
97 with conn.cursor() as cursor:
98 cursor.execute('SELECT * FROM Orders WHERE id = %s;',(id,))
99 result = cursor.fetchone()
100 return {'id':result[0], 'customerid':result[1], 'productId':result[2], 'date':result[3]}
101
102# should order be upsert, or just insert? There does not exist an edit feature on the page.
103def upsert_order(order):
104 with conn.cursor() as cursor:
105 cursor.execute('INSERT INTO Orders(customerid, productId, date) VALUES(%s, %s, %s);', (order['customerId'], order['productId'], order['date']) )
106 conn.commit()
107
108def delete_order(id):
109 with conn.cursor() as cursor:
110 cursor.execute('DELETE FROM Orders WHERE id = %s;',(id,))
111 conn.commit()
112
113# Return the customer, with a list of orders. Each order should have a product
114# property as well.
115
116# renders the added report page
117def customer_report(id):
118 customer = get_customer(id)
119 orders = get_orders()
120 customer['orders'] = [o for o in orders if o['customerid'] == id]
121 return customer
122
123# Return a list of products. For each product, build
124# create and populate a last_order_date, total_sales, and
125# gross_revenue property. Use JOIN and aggregation to avoid
126# accessing the database more than once, and retrieving unnecessary
127# information
128
129# appears to be working
130def sales_report():
131 products = get_products()
132 for product in products:
133 orders = [o for o in get_orders() if o['productid'] == product['id']]
134 orders = sorted(orders, key=lambda k: k['date'])
135 product['last_order_date'] = orders[-1]['date']
136 product['total_sales'] = len(orders)
137 product['gross_revenue'] = product['price'] * product['total_sales']
138 return products
139
140conn = connect_to_db(connection_string)