Python, Excel, and SQLlite3

I’m struggling with a particular script, I’ve tried googling the problem but I’ve become stuck…

I was wondering if anyone could possibly help me out with the SQL formulas. I’ve posted this to Reddits r/python as well, and post any answer found there, here for those who may need it in the future!

The current script is here: https://gist.github.com/adamrees89/b73a60bc01170601ced7adc7a8e971f7

At the moment, the script reads a sample excel file, reads the value and formatting of all the cells on each sheet and records the values and formatting to an SQLlite3 file.

Currently, the script reads all the values and formatting fine, creates a table within SQL for each sheet with no problems (Confirmed this with DB Browser for SQLite), but there are no rows of data within the tables.

I think there is a problem with my ‘templateCell()’ function:

def templateCell(s,sn,c,r):
#__init__ will gather the cell format data
cellRef = c + r
val = s[cellRef].value
fname = s[cellRef].font.name
fsize = s[cellRef].font.size
fbold = int(s[cellRef].font.bold == 'true')
fital = int(s[cellRef].font.italic == 'true')
ccolour = s[cellRef].fill.start_color.index
print("Cell Reference is: {},".format(cellRef))
c.execute("INSERT INTO {tn} VALUES ({a},{b},{c},{d},{e},{f})".format(tn=sn,a=cellRef,b=val,c=fname,d=fbold,e=fital,f=ccolour))

Or more specifically the c.execute function on line 11, where it is supposed to create the row in the database for each cell. Variables are:

s = sheet
sn =  sheetname (function of sheet)
c = column
r = row

Any ideas?

Edit 1: The Excel file is available here: testExcel.xlsx

Question: … but there are no rows of data within the tables.

You are probably missing a c.commit() after you have done one Sheet.

Note: Check out the executemany(sql, seq_of_parameters) function.¶`


class sqlite3.Connection

commit()
This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections.
If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.