· 8 years ago · Mar 21, 2018, 11:54 PM
1import sqlite3
2import pandas as pd
3import os, sys, re, string
4import itertools
5
6# """
7# This module can be used to add from a parent folder all the excel files to a sqlite3 database.
8# The class requires the database name and the path where you have the excel files.
9
10# Warning: If it founds an .csv file it will save to database only the first sheet!
11# Requires pandas module to be installed.
12
13# """
14
15class Df2db:
16
17 def __init__(self, dbname, root_path):
18 self.dbname = dbname
19 self.root_path = root_path
20
21 def connect_db(self):
22 #Connect to a db and if it not exists creates one with the name given
23 connection = sqlite3.connect(self.dbname)
24 cursor = connection.cursor()
25 return connection, cursor
26
27 def close(self):
28 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
29 connection.commit()
30 connection.close()
31
32 def norm_pctmarks(self, astring):
33 #Removing punctuation marks from the string, necessary for making compatible table names
34 punctuation_marks = list(str(string.punctuation).replace('_', ''))+[' ']
35 try:
36 for char in punctuation_marks:
37 astring = astring.replace(char, '_')
38 except:
39 pass
40 return astring
41
42 def rename_duplicate_dfcols(self, df):
43 #Rename DF columns if found duplicates (credit to SO"Lamakaha")
44 try:
45 cols=pd.Series(df.columns)
46 for dup in df.columns.get_duplicates():
47 cols[df.columns.get_loc(dup)]=[dup+'.'+str(d_idx) if d_idx!=0 else dup for d_idx in range(df.columns.get_loc(dup).sum())]
48 df.columns=cols
49 except Exception as e:
50 print("Got ", e)
51 pass
52 return df
53
54
55 def save_tosql(self,connection, df_sht, sht, path_to_xlname):
56
57 xlname = path_to_xlname.strip().split('\\')[-1]
58 try:
59 tablename_insql = str(xlname + '_ONSHEET_' + sht)
60 except:
61 tablename_insql = str(xlname)
62
63 #Remove punctuation marks from table name
64 tablename_insql = Df2db(self.dbname, self.root_path).norm_pctmarks(tablename_insql)
65
66 #Dealing with txt files
67 ext = path_to_xlname.split('.')[-1]
68 if ext == 'txt':
69 print(path_to_xlname)
70 txt = open(path_to_xlname).read().splitlines()
71 df = pd.Series(txt)
72 df = pd.DataFrame({tablename_insql: df})
73 #Saving df to sqlite3 db
74 df.to_sql(tablename_insql, connection, if_exists="replace", index=False)
75 connection.commit()
76 print('{} ok'.format(tablename_insql))
77
78 elif ext == 'csv':
79
80 df = pd.read_csv(path_to_xlname)
81
82 #Replacing incompatible with sqlite3 chars with underscores
83 cols_names = df.columns.values
84 new_cols_names = []
85 for col in cols_names:
86 newcol_name = Df2db(self.dbname, self.root_path).norm_pctmarks(col)
87 new_cols_names.append(newcol_name)
88 new_cols_names = [str(n).replace('\n', '_') for n in new_cols_names]
89 new_cols_names = [str(n).replace('___', '_') for n in new_cols_names]
90 new_cols_names = [str(n).replace('__', '_') for n in new_cols_names]
91 df.columns = new_cols_names
92
93 #Rename duplicate columns from df
94 df = Df2db(self.dbname, self.root_path).rename_duplicate_dfcols(df)
95
96 #Saving df to sqlite3 db
97 df.to_sql(tablename_insql, connection, if_exists="replace", index=False)
98 connection.commit()
99 print('{} ok'.format(tablename_insql))
100
101 else:
102 #Replacing incompatible with sqlite3 chars with underscores
103 cols_names = df_sht.columns.values
104 new_cols_names = []
105 for col in cols_names:
106 newcol_name = Df2db(self.dbname, self.root_path).norm_pctmarks(col)
107 new_cols_names.append(newcol_name)
108 new_cols_names = [str(n).replace('\n', '_') for n in new_cols_names]
109 new_cols_names = [str(n).replace('___', '_') for n in new_cols_names]
110 new_cols_names = [str(n).replace('__', '_') for n in new_cols_names]
111 df_sht.columns = new_cols_names
112
113 #Rename duplicate columns from df
114 df_sht = Df2db(self.dbname, self.root_path).rename_duplicate_dfcols(df_sht)
115
116 #Saving df to sqlite3 db
117 df_sht.to_sql(tablename_insql, connection, if_exists="replace", index=False)
118 connection.commit()
119 print('{} ok'.format(tablename_insql))
120
121
122
123
124 def xl2sql(self, path_to_xlname):
125 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
126 df = pd.ExcelFile(path_to_xlname)
127 for sht in df.sheet_names:
128 df_sht = df.parse(sht)
129 if df_sht.shape == (0,0):
130 pass
131 else:
132 print("has")
133 Df2db(self.dbname, self.root_path).save_tosql(connection, df_sht,sht,path_to_xlname)
134
135 def df2sql(self, df, dfname):
136 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
137 #print('conn si cursor ok\n', type(df), dfname)
138 #Saving df to sqlite3 db
139 df.to_sql(dfname, connection, if_exists="replace", index=False)
140 connection.commit()
141 print('{} ok'.format(dfname))
142 #Df2db(self.dbname, self.root_path).save_tosql(connection, df_sht,sht,path_to_xlname)
143
144 def csv2sql(self, path_to_xlname, df='', sht='Sheet1'):
145 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
146 Df2db(self.dbname, self.root_path).save_tosql(connection, df, sht, path_to_xlname)
147
148 def txt2sql(self,path_to_txt, df='', sht=''):
149 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
150 Df2db(self.dbname, self.root_path).save_tosql(connection, df, sht, path_to_txt)
151
152
153 def df_tosql(self, path_to_xlname, dfname=''):
154 #Get thru all sheets and if it has data save it to db
155 try:
156 filename = path_to_xlname.split('\\')[-1]
157 except:
158 pass
159
160 #input('trecut de trypass')
161
162 if str(type(path_to_xlname)) == "<class 'pandas.core.frame.DataFrame'>":
163
164 #input('recunoscut ca df')
165
166 Df2db(self.dbname, self.root_path).df2sql(path_to_xlname, dfname)
167
168 elif re.search('.xls', filename):
169 print('.xls')
170 Df2db(self.dbname, self.root_path).xl2sql(path_to_xlname)
171
172
173 elif re.search('.csv', filename):
174 print('.csv')
175 Df2db(self.dbname, self.root_path).csv2sql(path_to_xlname)
176
177 elif re.search('.txt', filename):
178 print('.txt')
179 Df2db(self.dbname, self.root_path).txt2sql(path_to_xlname)
180
181
182
183 def getdf_fromdb(self, table_name):
184 #gets the table from the db
185 #the table name must have this format excel_xlname_sheet_sheetname
186 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
187 query = "SELECT * FROM {}".format(table_name)
188 df = pd.read_sql_query(query, connection)
189 return df
190
191 def show_db_tables(self):
192 #Shows all tables names from the db
193 connection, cursor = Df2db(self.dbname, self.root_path).connect_db()
194 cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
195 all_tb = cursor.fetchall()
196 all_tb = [x[0] for x in all_tb] # from [(table1, ) etc] makes [table1, etc]
197 column_name = '{}_TABLES'.format(self.dbname)
198 df = pd.DataFrame({column_name : all_tb})
199 try:
200 df.to_csv(column_name+'.csv', index=False)
201 except Exception as e:
202 input('CSV already openned please close it..\n {}'.format(e))
203 sys.exit()
204 print('{} tables in {}\n'.format(len(all_tb), self.dbname))
205 return all_tb
206
207
208
209 def drop_tablefrom_db(self, table_name):
210 #Using the cursor created in connect_db func executes statement
211 connection = sqlite3.connect(self.dbname)
212 cursor = connection.cursor()
213 query = 'DROP TABLE {};'.format(table_name)
214 try:
215 cursor.execute(query)
216 except Exception as e:
217 print("Table does not exist!\nPress Enter to exit...")
218 input(e)
219 sys.exit()
220
221
222 def getfilespath_from(self):
223 #Walk thru a start path and return a list of paths to files
224 allfiles = []
225 for root, dirs, files in os.walk(self.root_path):
226 for file in files:
227 path_tofile = root + '\\' + file
228 allfiles.append(path_tofile)
229 return allfiles
230
231
232 def get_dfpaths(self):
233 #get a list of paths to excel files (.xlsx, .xls, .xlsm, .csv, .txt)
234 paths_tofiles = Df2db(self.dbname, self.root_path).getfilespath_from()
235 filtered_extensions = []
236 files_with_issues = []
237 for path in paths_tofiles:
238 file = path.split('\\')[-1]
239 tempfile = "".join(list(file)[:2])
240
241 if tempfile == "~$": # if file open then append to list the path
242 print('This file {} is open'.format(path))
243 files_with_issues.append(path)
244
245 elif re.search('.xls', file) or re.search('.csv', file) or re.search('.txt', file):
246 filtered_extensions.append(path)
247
248 #Create a txt file with files that where open
249 if len(files_with_issues) == 0:
250 pass
251 else:
252 files_with_errors = '\n'.join(files_with_issues)
253 error_file = open("Files that were open.txt", 'w')
254 error_file.write(files_with_errors)
255 error_file.close()
256
257 return filtered_extensions
258
259
260 def dfs_tosql(self):
261 #Run thru alldfs and save them to db
262 path_to_dfs = Df2db(self.dbname, self.root_path).get_dfpaths()
263 print('\nThere are ',len(path_to_dfs), ' files to be added..\n\n')
264 for df in path_to_dfs:
265 #print(df)
266 Df2db(self.dbname, self.root_path).df_tosql(df)
267 #tab = Df2db(self.dbname, self.root_path).show_db_tables()
268
269# #### Instantiating the class
270# todf = Df2db('mydata.db', r'E:\sqlite3')
271
272
273# # In[4]:
274
275
276# orase = pd.read_csv('orase.csv')
277# jud_loc = orase[['JUDET', 'LOCALITATE']]
278
279
280# # In[22]:
281
282
283# todf.df_tosql(jud_loc, 'judloc')
284
285
286# # In[35]:
287
288
289# todf.show_db_tables()
290
291
292# # In[36]:
293
294
295# todf.getdf_fromdb("judloc")
296
297
298# # In[26]:
299
300
301# todf.drop_tablefrom_db("orase_csv_ONSHEET_Sheet1")
302
303
304# In[27]:
305
306
307# todf.show_db_tables()
308
309
310# In[ ]:
311
312
313"""
314#Import class Df2db from df2db module
315from df2db import Df2db
316#Instantiate with the database name and the path where you got excel files
317todf = Df2db('dbname.db', r'D:\alot_of_xlfiles')
318#Call dfs_tosql function to search in the path you give for excel files
319#and save them to the database with the name given
320todf.dfs_tosql()
321#Show tables from the database
322todf.show_db_tables()
323#Get a table from the database as a dataframe object
324#in order to use it in pandas for manipulation
325todf.getdf_fromdb("EXCEL_xl4_xlsx_SHEET_Sheet1")
326#Delete a table from the database
327todf.drop_tablefrom_db("EXCEL_xl4_xlsx_SHEET_Sheet1")
328#Show tables from db, now you see one it's gone
329todf.show_db_tables()
330#Close the connection when you are done.
331todf.close()
332#Instantiate with the database name and the path where you got excel files
333todf = Df2db('dbname.db', r'D:\alot_of_xlfiles')
334#Make a dataframe
335import numpy as np
336import pandas as pd
337dates = pd.date_range('20130101', periods=6)
338df = pd.DataFrame(np.random.randn(6,4), index=dates, columns=list('ABCD'))
339#Save it to sql, you must give the df and a name for the df
340todf.df_tosql(df, 'test')
341#Show current tables from db
342todf.show_db_tables()
343#Close when you are done
344todf.close()
345#Each table saved in database will have this form
346tablname = "EXCEL_test_xlsx_SHEET_Sheet1"
347tablname.split('_')
348'EXCEL' - all tables in db will start with this prefix
349'test' - the name if the excel or df
350'xlsx' - the extension of the file
351'SHEET' - the next folowing this will be the Sheet name of the excel
352That's because it looks in the workbook in all sheets for tables and saves the sheet name also
353'Sheet1' - The sheet where the table was found
354If the excel or df has punctuation marks or spaces those will be replaced with underscore "_"
355This replace is done in order to be compatible with sqlite3 database
356Also, if the column names contains spaces those will be replaced with "_"
357"""