sqlite3 operational error on insert query

[jonesin1974]

Hi,

I'm having issues with my insert query. I keep getting the error <class 'sqlite3.OperationalError'>

Have tried several things as suggested on Google but nothing works.

My data values are populated in a list throughout my script and is the only way I can plug the values into my query.

The fields numbers and values align; I've tried all strings; I've tried using (sql_formatted,); and lastly [sql_formatted]

If I manually submit the query as one big long string in sqlite browser, it works. It's just this poncy sqlite3 library forcing me to use the (?,?,?....)format that's causing me grief.

For simplicity, I've completely broken it down:

sql_formatted = []
sql_formatted.append("2018-06-26")
sql_formatted.append("my test")
sql_formatted.append("1.0")
sql_formatted.append("runperiod")
sql_formatted.append("Transaction 1")
sql_formatted.append(1.776)
sql_formatted.append(8.803)
sql_formatted.append(15.161)
sql_formatted.append(4.138)
sql_formatted.append(13.999)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append(15.161)
sql_formatted.append(23)
sql_formatted.append(0)
sql_formatted.append(0)
sql_formatted.append('N')

try:

cur.execute("insert into lr_rundata (current_date, test_name, app_version, test_run_period, transaction_name, '\
'sla_status, minimum, average, maximum, std_deviation, percentile80, percentile81, percentile82, percentile83, percentile84, '\
'percentile85, percentile86, percentile87, percentile88, percentile89, percentile90, percentile91, percentile92, percentile93,'\
'percentile94, percentile95, percentile96, percentile97, percentile98, percentile99, pass, fail, stop, deleted) values '\
'(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", sql_formatted)

Any suggestions would be more than welcome!!

Kind regards,
Me

[buran]

obviously not tested, but try

cur.execute("insert into lr_rundata (current_date, test_name, app_version, test_run_period, transaction_name, \
sla_status, minimum, average, maximum, std_deviation, percentile80, percentile81, percentile82, percentile83, percentile84, \
percentile85, percentile86, percentile87, percentile88, percentile89, percentile90, percentile91, percentile92, percentile93, \
percentile94, percentile95, percentile96, percentile97, percentile98, percentile99, pass, fail, stop, deleted) values \
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", sql_formatted)

[jonesin1974]

Hi,

Many thanks for getting back!

Unfortunately it didn't like that. Now I'm getting : <class 'sqlite3.ProgrammingError'>

Cheers,
J

[Maverick494]

When I did a query for MySQL I couldn't get around having everything on the same line.

Try

cur.execute("insert into lr_rundata (current_date, test_name, app_version, test_run_period, transaction_name, sla_status, minimum, average, maximum, std_deviation, percentile80, percentile81, percentile82, percentile83, percentile84, 'percentile85, percentile86, percentile87, percentile88, percentile89, percentile90, percentile91, percentile92, percentile93, percentile94, percentile95, percentile96, percentile97, percentile98, percentile99, pass, fail, stop, deleted) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", sql_formatted)

[jonesin1974]

Interesting as when I try it on a smaller table with only a few fields it works and that was all on one line.

However, I initially had it on one line and it comes back with 'Programming Error'. When I hover over the line (when it's as one line), it says: PEP 8:line too long (570 > 120 characters).

This is really annoying as if it would just let me run the query without all of this (?,?,?) malarkey, I would be OK....probably ;)

Cheers,
Me

---

Right, got there via another source. Turns out I need to use all single quotes and it's fine:

cur.execute(
                'insert into lr_rundata (current_date, test_name, app_version, test_run_period, transaction_name, ' \
                'sla_status, minimum, average, maximum, std_deviation, percentile80, percentile81, percentile82, percentile83, percentile84, ' \
                'percentile85, percentile86, percentile87, percentile88, percentile89, percentile90, percentile91, percentile92, percentile93,' \
                'percentile94, percentile95, percentile96, percentile97, percentile98, percentile99, pass, fail, stop, deleted) values ' \
                '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', sql_formatted)

[Larz60+]

you can simplify the test sql statement to:

def add_zeros(list_name, num):
    nlist = [0] * num
    list_name = list_name + nlist
    return list_name

sql_formatted = ['2018-06-26', 'my test', '1.0', 'runperiod', 'Transaction 1', 1.776, 8.803, 15.161, 4.138, 13.999]
sql_formatted += add_zeros(sql_formatted, 18)
sql_formatted +=  [15.161, 23, 0, 0, 'N']

print('sql_formatted: {}'.format(sql_formatted))