forked from itucsdb1515/itucsdb1515
-
Notifications
You must be signed in to change notification settings - Fork 1
/
contacts.py
105 lines (99 loc) · 4.14 KB
/
contacts.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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
import psycopg2 as dbapi2
class contact:
def __init__(self, ticket_id, subject, name, surname, email, message):
self.ticket_id = ticket_id
self.subject=subject
self.name = name
self.surname = surname
self.email = email
self.message = message
class Contact:
def __init__(self, app):
self.app = app
def initialize_table(self):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS CONTACT (
TICKET_ID INT PRIMARY KEY NOT NULL,
SUBJECT VARCHAR(20) NOT NULL,
NAME VARCHAR(20) NOT NULL,
SURNAME VARCHAR(20) NOT NULL,
EMAIL VARCHAR(30) NOT NULL,
MESSAGE VARCHAR(200) NOT NULL
)""")
connection.commit()
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()
def drop_table(self):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
query = """DROP TABLE IF EXISTS CONTACT"""
cursor.execute(query)
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()
def list_contacts(self):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
query = """ SELECT * FROM CONTACT ORDER BY TICKET_ID"""
cursor.execute(query)
contacts = cursor.fetchall()
return contacts
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()
def add_contact(self, ticket_id, subject, name, surname, email, message):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
query = "INSERT INTO CONTACT (TICKET_ID, SUBJECT,NAME,SURNAME,EMAIL,MESSAGE) VALUES (%s, %s, %s, %s, %s, %s)"
cursor.execute(query, (ticket_id, subject, name, surname, email, message))
connection.commit()
cursor.close()
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()
def get_contact(self, ticket_id):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
query = """ SELECT * FROM CONTACT WHERE TICKET_ID = %s """
cursor.execute(query, (ticket_id))
contact = cursor.fetchall()
return contact
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()
def update_contact(self, subject, name, surname, email, message, ticket_id):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
query = """ UPDATE CONTACT SET SUBJECT = %s,NAME = %s,SURNAME = %s,EMAIL = %s,MESSAGE = %s WHERE TICKET_ID = %s"""
cursor.execute(query, (subject, name, surname, email, message, ticket_id))
connection.commit()
cursor.close()
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()
def delete_contact(self, ticket_id):
with dbapi2.connect(self.app.config['dsn']) as connection:
try:
cursor = connection.cursor()
query = """DELETE FROM CONTACT WHERE TICKET_ID = %s"""
cursor.execute(query, (ticket_id))
connection.commit()
cursor.close()
except dbapi2.DatabaseError:
connection.rollback()
finally:
connection.commit()