-
Notifications
You must be signed in to change notification settings - Fork 0
/
postgres01.py
52 lines (41 loc) · 1.32 KB
/
postgres01.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import psycopg2
# Opening DB function, given a name
def open_db(name):
conn=psycopg2.connect(name)
cur=conn.cursor()
return conn, cur
#Closing DB function, given a connection
def close_db(conn):
conn.commit()
conn.close()
#Create a table function, given a connection
def create_table(cur):
cur.execute("CREATE TABLE IF NOT EXISTS STORE (item TEXT, quantity INTEGER, price REAL)")
# Insert rows into a table function
def insert_rows(cur,item,quantity,price):
cur.execute("INSERT INTO STORE VALUES(%s,%s,%s)",(item,quantity,price))
# delete all rows
def delete_rows(cur):
cur.execute("DELETE FROM STORE")
def delete_row(cur,item):
cur.execute("DELETE FROM STORE WHERE item=%s",(item,))
# View all rows in the table function
def view_all(cur):
cur.execute("SELECT * FROM STORE")
rows=cur.fetchall()
print(rows)
#Open DB
conn, cur = open_db("dbname='vatsanpys' user='postgres' password='postgres' host='localhost' port='5432'")
create_table(cur)
close_db(conn)
# Insert Rows
conn, cur = open_db("dbname='vatsanpys' user='postgres' password='postgres' host='localhost' port='5432'")
delete_rows(cur)
insert_rows(cur,"Water Glass",5,10.5)
insert_rows(cur,"Wine Glass",10,20.0)
insert_rows(cur,"Coffee Cup",8,6.5)
delete_row(cur,"Wine Glass")
# Print all Rows
view_all(cur)
#Close DB
close_db(conn)