-
Notifications
You must be signed in to change notification settings - Fork 3
/
db.py
157 lines (110 loc) · 3.68 KB
/
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
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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
"""Address Book Database
Author: Travis Barnes, January 16 2016
This program creates, modifies (insert, delete, update), and queries a
SQLite database filled with address book entries.
"""
import sqlite3 as sql
import os.path as path
import config as cfg
def db_init(db_name):
"""Create/Open and initialize a database.
Keyword arguments:
db_name -- Name of the new database file
"""
if db_exists(db_name) == True:
cfg.DB = sql.connect(db_name + '.ab')
cfg.C = cfg.DB.cursor()
print('Database already exists')
else:
cfg.DB = sql.connect(db_name + '.ab')
cfg.C = cfg.DB.cursor()
create_table = '''CREATE TABLE Contacts(First TEXT,
Last TEXT, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT,
Zip TEXT, Home TEXT, Mobile TEXT, Email TEXT, Birthday TEXT,
Notes TEXT) '''
cfg.C.execute(create_table)
cfg.DB.commit()
print('New table created')
def db_exists(db_name):
"""Checks whether db exists.
Keyword arguments:
db_name -- Name of a database file
"""
if (path.isfile(db_name + '.ab')):
return True
else:
return False
def get_id(entry):
"""Gets database ID number from an entry
Keyword arguments:
entry - A list object
"""
entry_id = "SELECT rowid, * FROM Contacts WHERE First = ? AND Last = ?"
cfg.C.execute(entry_id, [entry[0], entry[1]])
for row in cfg.C:
return row[0]
def insert_entry(entry):
"""Inserts a new entry into database.
Keyword arguments:
entry -- A list object
"""
cfg.C.execute('INSERT INTO Contacts VALUES (?,?,?,?,?,?,?,?,?,?,?,?)',
entry)
cfg.DB.commit()
def delete_entry(entry_id):
"""Removes entry from database.
Keyword arguments:
entry_id -- Corresponding ID number for an entry
"""
cfg.C.execute("DELETE FROM Contacts WHERE rowid = ?", [entry_id])
def get_entry(entry_id):
"""Queries entry from database.
Keyword arguments:
entry_id -- Corresponding ID number for an entry
"""
cfg.C.execute("SELECT * FROM Contacts WHERE rowid = ?", [entry_id])
return cfg.C
def db_commit():
"""Commits all changes to database."""
cfg.DB.commit()
def edit_entry(entry_id, entry):
"""Updates entry in database.
Keyword arguments:
entry_id -- Corresponding ID number for an entry
entry -- A list object
"""
entry_update = '''UPDATE Contacts SET First = ?, Last = ?, Street1 = ?,
Street2 = ?, City = ?, State = ?, Zip = ?, Home = ?, Mobile = ?,
Email = ?, Birthday = ?, Notes = ? WHERE rowid = ? '''
cfg.C.execute(entry_update, [entry[0], entry[1], entry[2], entry[3],
entry[4], entry[5], entry[6], entry[7], entry[8], entry[9], entry[10],
entry[11], '{}'.format(entry_id)])
def query_entrylist(sort):
"""Prints the full list of entries in database.
Keyword arguments:
sort -- String containing sorting method
"""
if sort == 'Last Name':
last_name = '''SELECT * FROM Contacts ORDER BY Last ASC, First ASC'''
cfg.C.execute(last_name)
elif sort == 'Zip':
zip_code = '''SELECT * FROM Contacts ORDER BY Zip ASC, Last ASC'''
cfg.C.execute(zip_code)
return cfg.C
def search_entry(str, sort):
"""Searches the database for an entry.
Keyword arguments:
str -- A string containing search term
sort -- String containg sorting method
"""
if sort == 'Last Name':
search_last = '''SELECT * FROM Contacts WHERE (First || Last || Street1 ||
Street2 || City || State || Zip || Home || Mobile || Email ||
Birthday || Notes) LIKE '%' || ? || '%' ORDER BY Last ASC, First ASC'''
cfg.C.execute(search_last, [str])
elif sort == 'Zip':
search_zip = '''SELECT * FROM Contacts WHERE (First || Last || Street1 ||
Street2 || City || State || Zip || Home || Mobile || Email ||
Birthday || Notes) LIKE '%' || ? || '%' ORDER BY Zip ASC, Last ASC'''
cfg.C.execute(search_zip, [str])
return cfg.C