Python Sql Insert Code

Hello all,

I have a problem about “insert into” in SQL… You can see the output of error and my script code in below.

image

Code:

if retVal ['success']:
	datas = retVal['data']
	system.db.runUpdateQuery("INSERT INTO AutoDispatchTRA VALUES('%(lineabbreviation)s', '%(areacode)s', '%(linecode)s', '%(tradecode)s', '%(dispatchtypecode)s', '%(started)s', '%(closed)s', '%(machinecode)s', '%(reason)s', '%(dispatchnumber)i')" %datas, database_name)

I request API call and I get “success”… After that, the script continue with “IF” condition as above. But then, I get this error.

Actually, I can see the my “INSERT” information in the end of diagnostic lines and each values are correct. But I couldn’t understand what’s going on.

Thanks in advance…

you really should use runPrepQuery or runPrepUpdate instead of trying to convert the value to a string by putting it in between ‘’

https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate

1 Like

Thanks for your answer.
But even if I try with “runPrepUpdate”, I get similar error.

if retVal ['success']:
       datas = retVal['data']
       system.db.runPrepUpdate("INSERT INTO AutoDispatchTRA (LineAbbreviation, AreaCode, LineCode, TradeCode, DispatchTypeCode, StartedTime, ClosedTime, MachineCode, Reason, DispatchNumber) VALUES (?,?,?,?,?,?,?,?,?)", [datas['lineabbreviation'], datas['areacode'], datas['linecode'], datas['tradecode'], datas['dispatchtypecode'], datas['started'], datas['closed'], datas['machinecode'], datas['reason'], datas['dispatchnumber']], database_name)

image

Then im guessing its somethign wrong with the query?

try putting this sql query directly in your db manager, they might give you a more detailed error meessage

2 Likes

You have 10 columns inserted to, you have 9 question marks

Also when SQL reports back the values it appears some are null, there are 2 instances with 2 commas in a row

2 Likes

Yes you are right. I noticed that too before I checked here. Thank you

1 Like