Python Forum

I wrote this code. When I run it I don't get errors but I can't get the values inserted in the table, I end up with a database with table1 that's empty. Any help?

TabelName = 'table1'
values = [1, 1, 1.0, 1.0, 0]
columnsNmes =['Day', 'Month', 'Hour', 'HOY', 'Sun Falling on Tower Facade']
fieldTypes = [REAL,REAL,REAL,REAL,REAL]
filepath = 'C:\xampp\htdocs\FT-test9.db'
conn = sqlite3.connect(filePath)

c = conn.cursor()
try:
    c.execute('CREATE TABLE IF NOT EXISTS {tn} ({nf} {ft})'\
                .format(tn=tableName, nf=columnsNames[0], ft=fieldTypes[0]))
        
       
    for i, j in zip(columnsNames[1:], fieldTypes[1:]):
        c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}"\
                    .format(tn=tableName, cn=i, ct=j))
    
    except:
        pass

newColumnsNmes = []

for d in data:
    m =float(d)
    newColumnsNames.append(m)
        
for m,n in zip(newColumnsNames, data):     
    c.execute("INSERT INTO {tn} {cn} VALUES {vn}"\
        .format(tn=tableName, cn=m, vn= n))
conn.commit()
conn.close()

how do you know you're not getting errors when your except clause is ignoring any errors?

replace the pass (temporarilly ... replace exception with actual exception later) with:

#at start or program
import sys
# ...
#Replace pass with:
print("Unexpected error:", sys.exc_info()[0])

This will show all errors.

(May-24-2018, 02:39 PM)Larz60+ Wrote: [ -> ]how do you know you're not getting errors when your except clause is ignoring any errors?

The part of the code that has the "except" clause gets created successfully, I get a table with the right structure

Ok, Add the print statement here:

for m,n in zip(newColumnsNames, data):
    sqlstr = "INSERT INTO {tn} {cn} VALUES {vn}".format(tn=tableName, cn=m, vn= n))
    print('sqlstr: {}'.format(sqlstr))
    c.execute(sqlstr)
#    c.execute("INSERT INTO {tn} {cn} VALUES {vn}"\
#        .format(tn=tableName, cn=m, vn= n))

(May-24-2018, 02:46 PM)Larz60+ Wrote: [ -> ]Ok, Add the print statement here:

for m,n in zip(newColumnsNames, data):
    sqlstr = "INSERT INTO {tn} {cn} VALUES {vn}".format(tn=tableName, cn=m, vn= n))
    print('sqlstr: {}'.format(sqlstr))
    c.execute(sqlstr)
#    c.execute("INSERT INTO {tn} {cn} VALUES {vn}"\
#        .format(tn=tableName, cn=m, vn= n))

Aha, that's what I got, but I still don't understand what the problem is:

Runtime error (SyntaxErrorException): unexpected token ')'
File "", line 32
sqlstr = "INSERT INTO {tn} {cn} VALUES {vn}".format(tn=tableName, cn=m, vn= n))

^
SyntaxError: unexpected token ')'

add print to see what values of m, n, tn are

I had an extra close parentheses

also try:

sqlstr = "INSERT INTO {} {} VALUES {}".format(tableName, m, n)

(May-24-2018, 03:00 PM)Larz60+ Wrote: [ -> ]add print to see what values of m, n, tn are

I had an extra close parentheses

also, try:

sqlstr = "INSERT INTO {} {} VALUES {}".format(tableName, m, n)

I tried both suggestions and I don't get errors and also the values don't get inserted, but I figured out that something is odd in the iteration process because when I run this:

for m,n in zip(newColumnsNames, data):
    print m, n 

I don't get anything printed, however when I run this:

for m,n in zip(newColumnsNames, data):
    pass
print m, n 

I get the correct values of m and n printed
It's worth mentioning that I use IronPython inside a 3D graphics application environment

---

Also, I figured out that I accidentally was trying to convert my columns' names into float instead of doing this into to the data(the values). So I corrected this, then I put the print statement that @Larz60+ recommended and this is the error message I got:

sqlstr: INSERT INTO table1 Day VALUES 1.0
Runtime error (Exception): near "Day": syntax error
Traceback:
line 35, in script




Please note Day is a column name