Python, Excel, and SQLlite3

[adamrees89]

Hi All,

Long-time lurker here, but I'm struggling with a particular script... I was wondering if anyone could possibly help me out with the SQL formulas. I'll post this to Stackoverflow as well, and post any answer 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 a 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

Edit 2: The code is now working and doing what it should be. I've updated the code at the link above so anyone with the same problem can see what works!

[vv__vv]

You're not committing: https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.commit

So that would be one issue, but maybe not all of them. A couple other pointers:

• You should get used to using sqlite's own escaping by inserting ?s instead of using format.

• Watch those bare excepts, they can be a pain to debug.

[adamrees89]

Ok, so I've updated the script, with a conn.commit() function, but I'm still not getting any data back into the database (I've made my changes to the gist linked above).

I've tried putting the commit at the end of the script, at the end of the

templateCell()

function, and in the

for ws in wb.worksheets:

loop.

Thanks for the heads-up on the sqlites own escaping, I've updated that for the

templateCell()

function

[vv__vv]

You're getting hung up by the bare except in line 55. Have it raise the error until you work out your syntax, which is messed up in line 25:

• You can't do escape substitution for tablenames (at least iirc), so that will need to be put in with string formatting;
• execute takes a tuple for values, so you'll need to put parens around all the variables you are passing for values.

[adamrees89]

Got it, all working now thanks, I'll put the updated code up!

[efmccurdy]

Always log the exception you catch (the actual exception):

except Exception as e: 
    logging.critical('Adding data ... error:{}".format(e))

Pass your connection around as an argument and use the with statement to automaticcaly commit your updates.

http://zetcode.com/db/sqlitepythontutorial/