-
Notifications
You must be signed in to change notification settings - Fork 0
/
postgres_db.py
70 lines (53 loc) · 1.72 KB
/
postgres_db.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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
import psycopg2
conn = psycopg2.connect(database="dbname",
user="postgres",
password="password",
host="localhost",
port="5432")
cursor = conn.cursor()
# creating the table
cursor.execute('''CREATE TABLE mytablename
(ID INT PRIMARY KEY,
col2 TEXT,
col3 TEXT,
col4 FLOAT);''')
# (manual) inserting a value into the table
cursor.execute('''INSERT INTO mytablename(ID,
col2, col3, col4) VALUES
(1105910, 'Value1', 'Value2', 'Value3',
'Value4')''')
# secure (manual) inserting a value into the table
insert_data = (id, col2, col3, col4)
cursor.execute('''INSERT INTO mytablename(id, col2, col3, col4
# Filtering data
cursor.execute("SELECT col2, col3 from mytablename WHERE ID=1105910")
rows = cursor.fetchall()
for row in rows:
print("col2 =", row[0])
print("col3 =", row[1],"\n")
-----
# deleting records
cursor.execute('''DELETE FROM mytablename WHERE id=xxxx''')
# inserting with functions with input
def insert_record(conn, id, col2, col3, col4,...):
new_record = (id, col2, col3, col4)
cursor = conn.cursor()
cursor.execute('''INSERT INTO tablename(id, col2, col3, col4)
VALUES (%s, %s, %s, %s)''', new_record)
conn.commit()
## inserting can also be more complex, like calculated columns
## just add in the insert_record function (this is a mock example)
order_total = quantity * price
if discount != 0:
order_total = order_total * discount
if __name__ == '__main__':
id = int(input('text\n"))
col2 = input('text\n")
col3 = input('text\n")
col4 = float(input('text\n")
insert_data(conn, id, col2, col3, col4)
print('Data inserted!')
conn.close()
# the commit part will be present within the function
conn.commit()
conn.close()