- Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdata_base.py
More file actions
Latest commit
299 lines (234 loc) · 8.74 KB
/
Copy pathdata_base.py
File metadata and controls
299 lines (234 loc) · 8.74 KB
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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
importopenpyxl
importos
def_db_exists(db_name):
'''Returns true if the given excel file exists '''
#checks if file exists or not
ifos.path.exists(db_name):
returnTrue
returnFalse
defcreate_db(db_name, list_ot_headers):
''' Creates the excel sheet and with the given parameters as headers of excel
file
Usage; create_db('Database_Name', ['Header1', 'Header2', 'Header3']) '''
#yield each item of header at a time
definsert_value():
foriteminlist_ot_headers:
yielditem
table_generator=insert_value()
db_name=db_name+'.xlsx'
if_db_exists(db_name):
raiseException('The database with that name already exits!')
ifnotisinstance(list_ot_headers, list):
raiseException(' Make sure that the headers is in list format')
excel_file=openpyxl.Workbook()
active_sheet=excel_file.active
number_of_columns=len(list_ot_headers)
forindexinrange(1, number_of_columns+1):
active_sheet.cell(row=1, column=index).value=next(table_generator) #use of generator
excel_file.save(db_name)
print'Database {} created successfully'.format(db_name)
definsert_into_db(db_name, list_of_value):
''' Saves the given list of values into excel sheet
Usage; insert_into_db('Database_Name', ['Value1', 'Value2', 'Value3']) '''
db=db_name
db_name=db_name+'.xlsx'
ifnot_db_exists(db_name):
raiseException('Database with name "{}" does not exist'.format(db))
wb=openpyxl.load_workbook(db_name)
active_sheet=wb.active
column=active_sheet.max_column
ifnotisinstance(list_of_value, list):
raiseException('Enter the data to be inserted as a list')
my_gen= (itemforiteminlist_of_value) #generator to yield each value to be inserted at a time
#see if the data length corresponds to number of columns or not
iflen(list_of_value) %column!=0:
raiseException('Make sure the data has correct length')
outer_loop_counter=len(list_of_value)/column
forcounterinrange(outer_loop_counter):
row=active_sheet.max_row
foriinrange(1, column+1):
active_sheet.cell(row=row+1, column=i).value=next(my_gen)
wb.save(db_name)
print'Successfully inserted data'
defdelete_from_db(db_name, list_of_value, field=None):
''' Deletes the record with matching value and field
Usage: delete_from_db('Database_Name', 'first_name', 'First Name' '''
db=db_name
db_name=db_name+'.xlsx'
ifnot_db_exists(db_name):
raiseException('Database with name "{}" does not exist'.format(db))
wb=openpyxl.load_workbook(db_name)
active_sheet=wb.active
headers=show_tables(db_name)
#if no field is given make one default
ifnotfield:
field=active_sheet.cell(row=1,column=1).value
iffieldinheaders:
column_value=headers.index(field) +1
row, col=active_sheet.max_row, active_sheet.max_column
match_results= []
#get the matching cells and append it to list
foriinrange(1,row+1):
ifactive_sheet.cell(row=i, column=column_value).value==list_of_value:
match_results.append((i,column_value))
iflen(match_results) >1:
print'''There are more than one match data in database. Which one do you want to delete?
Press the required number. '''
col_into_letter='{}'.format(openpyxl.utils.get_column_letter(col))
match_column_slice=''
#Enumerate match_reslts and concetenate each cell's value and print it
forindex,iinenumerate(match_results, start=1):
s=''
match_column_slice='A{}:{}{}'.format(i[0], col_into_letter, i[0] )
formatchinactive_sheet[match_column_slice]:
foriteminmatch:
s=s+' '+str(item.value)
print'{}. {}'.format(index, s)
#See that input is valid
try:
prm=input('Enter the number to delete item >')
ifnot1<=prm<=len(match_results):
raiseException('Enter a valid number')
except (NameError, TypeError):
raiseException('Enter a valid number')
#delete the value, assign None
list_index_to_delete=match_results[prm-1 ]
column_slice_to_delete=list_index_to_delete[0]
slice_val='A{0}:{1}{0}'. format(column_slice_to_delete, col_into_letter)
foriteminactive_sheet[slice_val]:
foriinitem:
i.value=None
print'Successfully deleted !!'
eliflen(match_results) ==1:
print'Found one match'
col_into_letter='{}'.format(openpyxl.utils.get_column_letter(col))
foriinmatch_results:
match_column_slice='A{0}:{1}{0}'.format(i[0], col_into_letter)
formatchinactive_sheet[match_column_slice]:
foreach_tupinmatch:
each_tup.value=None
print'Deleted value'
else:
print'"{}" does not match any record '.format(list_of_value)
else:
print'"{}" is not a valid column name'.format(field)
wb.save(db_name)
defupdate_from_db(db_name, old_data, data_to_be_updated):
''' Updates the last matching item
Usage; update_from_db('Database_Name','This, is, old, data', 'This, is new, data')
'''
db=db_name
db_name=db_name+'.xlsx'
ifnot_db_exists(db_name):
raiseException('Database with name "{}" does not exist'.format(db))
wb=openpyxl.load_workbook(db_name)
active_sheet=wb.active
row, col=active_sheet.max_row, active_sheet.max_column
old_data=old_data.split(',')
od=''.join(old_data)
print'The od is ', od
col_into_letter='{}'.format(openpyxl.utils.get_column_letter(col))
new_data=data_to_be_updated.split(',')
replace_new_data= (itemforiteminnew_data)
ifnotlen(old_data) ==len(new_data) ==col:
raiseException('Make sure the data length is correct.')
match_slice=''
forrinrange(2,row+1):
s=''
forcinrange(1,col+1):
val=active_sheet.cell(row=r, column=c).value
ifvalisNone:
continue
s=s+' '+str(active_sheet.cell(row=r, column=c).value)
ifs.strip() ==od.strip():
match_slice='A{0}:{1}{0}'.format(r,col_into_letter)
foriteminactive_sheet[match_slice]:
foreach_tupinitem:
each_tup.value=next(replace_new_data)
printeach_tup.value
print'Successfully updated data'
wb.save(db_name)
defsearch_data(db_name, search_keywords, field):
''' Searches the given databse with the key word an look up field
Usage: search_data('Database_name', 'someword', 'Look_Up_Field')
E.g. search_data('Database_School', 'Jhon', 'First Name')
'''
db=db_name
db_name=db_name+'.xlsx'
ifnot_db_exists(db_name):
raiseException('Database with name "{}" does not exist'.format(db))
wb=openpyxl.load_workbook(db_name)
active_sheet=wb.active
headers=show_tables(db_name)
iffieldinheaders:
column_value=headers.index(field) +1
row, col=active_sheet.max_row, active_sheet.max_column
match_results= []
foriinrange(1,row+1):
ifactive_sheet.cell(row=i, column=column_value).value==search_keywords:
match_results.append((i,column_value))
print' Your search for {} gives this result'.format(search_keywords)
col_into_letter='{}'.format(openpyxl.utils.get_column_letter(col))
match_column_slice=''
forindex,iinenumerate(match_results, start=1):
s=''
match_column_slice='A{}:{}{}'.format(i[0], col_into_letter, i[0] )
formatchinactive_sheet[match_column_slice]:
foriteminmatch:
s=s+' '+item.value
print'{}. {}'.format(index, s)
else:
print'"{}" is not a valid field in this database'.format(field)
defshow_db():
'''Lists out all the database available '''
data_bases= []
forfilesinos.listdir('.'):
iffiles.endswith('xlsx'):
filename, _=files.split('.')
data_bases.append(filename)
iflen(data_bases) !=0:
print'Found following database\n'
foritemindata_bases:
printitem
else:
print'Found no database'
defshow_tables(db_name):
''' Lists out all the columns of the database '''
ifnotdb_name.endswith('.xlsx'):
db_name=db_name+'.xlsx'
else:
db_name=db_name
db=db_name
ifnot_db_exists(db_name):
raiseException('Database with name "{}" does not exist'.format(db))
wb=openpyxl.load_workbook(db_name)
sheet=wb.active
number_of_columns=sheet.max_column
headers= []
#print 'The tables in {} are as follows: '.format(db_name)
foriinrange(1, number_of_columns+1):
column_head=sheet.cell(row=1, column=i).value
#print '{}. {}'.format(i,column_head)
headers.append(column_head)
returnheaders
defshow_all_data(db_name):
''' Lists all the data/record from a database '''
db=db_name
db_name=db_name+'.xlsx'
ifnot_db_exists(db_name):
raiseException('Database with name "{}" does not exist'.format(db))
wb=openpyxl.load_workbook(db_name)
sheet=wb.active
row, col=sheet.max_row, sheet.max_column
forrinrange(2, row+1):
result=''
forcinrange(1,col+1):
record=sheet.cell(row=r, column=c).value
ifrecordisNone:
continue
result=result+' '+str(record)
ifresult:
printresult,
print
if'__name__'=='__main__':
main()