Following a IU educational video - Need help

Grigory_Vogman · February 17, 2025, 3:56pm

Hello everyone,

I am trying to import a csv file into a database table. After my own attempts to create a script to do it, I figure out there is a whole subsection called "Importing CSV Data to a DB Using Scripting" in the "Scripting" part of IU. I followed the first two videos almost to the point (except naming some things) and everything worked as expected. However, when I got to the third video in the subsection ("Inserting PyDataset Records into a Database") I am getting an error. I got to 7:07 of the video, where they are doing a "spot-check" and while their code is working fine in the video, my code gives me a whole bunch of errors. I checked my code multiple times and it seems to be exactly the same as in the video (except, again, for some naming). The only difference I noticed is that the video is using Jython 2.7.2 while my version of Ignition uses 2.7.3. My code is below:

def populate_dbtable_from_pydataset(pds, dbTable, dbConn):
 	cols = pds.getColumnCount()
 	
	#clear DB table each time
 	queryStr = "TRUNCATE TABLE {:s}".format(dbTable)
 	system.db.runUpdateQuery(queryStr, dbConn)
 	
 	#set up insert query string
 	pholders = "({:s})".format(",".join("?"*cols))
 	queryStr = "INSERT INTO {:s} VALUES {:s}".format(dbTable, "{pholders}")
 	
 	#loop over data, perform inserts
 	queryArgs = []
 	queryData = []
 	for row in pds:
 		queryData.extend(row)
 		queryArgs.append(pholders)
 	
	queryStr = queryStr.format(pholders=",".join(queryArgs))
	system.db.runPrepUpdate(queryStr, queryData, dbConn)
 
pds = toolbox_csvtopds.read_csv_to_pydataset()
dbTable = "table"
dbConn = "database"
populate_dbtable_from_pydataset(pds, dbTable, dbConn)

pturmel · February 17, 2025, 4:00pm

Share the error, too. (Complete traceback, using pre-formatted text styling, please.)

Grigory_Vogman · February 17, 2025, 4:05pm

Here it is, apologies for not including it in the OP

>>> 
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 25, in <module>
  File "<input>", line 20, in populate_dbtable_from_pydataset
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?), [HARLEYSVILLE, Harleysville Real Estate Group, 91933-09026, 91933-09026, Unknown Meters, Natural Gas, 0.00, 0, 0, 0, 0, 0, 0, 0], database, , false, false)


	at org.python.core.Py.JavaError(Py.java:545)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)

	at org.python.core.PyObject.__call__(PyObject.java:494)

	at org.python.core.PyObject.__call__(PyObject.java:498)

	at org.python.pycode._pyx54.populate_dbtable_from_pydataset$1(<input>:20)

	at org.python.pycode._pyx54.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyBaseCode.call(PyBaseCode.java:168)

	at org.python.core.PyFunction.__call__(PyFunction.java:437)

	at org.python.pycode._pyx54.f$0(<input>:25)

	at org.python.pycode._pyx54.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1703)

	at org.python.core.Py.exec(Py.java:1747)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:628)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?), [HARLEYSVILLE, Harleysville Real Estate Group, 91933-09026, 91933-09026, Unknown Meters, Natural Gas, 0.00, 0, 0, 0, 0, 0, 0, 0], database, , false, false)

	... 31 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)": Column name or number of supplied values does not match table definition.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:293)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 29 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column name or number of supplied values does not match table definition.

	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)

	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)

	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)

	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)

	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:483)

	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)

	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)

	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:981)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:65)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:26)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:66)

	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)

	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)

	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)

	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:598)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1580)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1553)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)

	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)

	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.server.Server.handle(Server.java:563)

	at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287)

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)

	at org.eclipse.jetty.io.ssl.SslConnection$DecryptedEndPoint.onFillable(SslConnection.java:558)

	at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:379)

	at org.eclipse.jetty.io.ssl.SslConnection$2.succeeded(SslConnection.java:146)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)

	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)

	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<input>", line 25, in <module>
  File "<input>", line 20, in populate_dbtable_from_pydataset
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?), [HARLEYSVILLE, Harleysville Real Estate Group, 91933-09026, 91933-09026, Unknown Meters, Natural Gas, 0.00, 0, 0, 0, 0, 0, 0, 0], database, , false, false)
>>>

pturmel · February 17, 2025, 4:11pm

This.

Grigory_Vogman · February 17, 2025, 4:15pm

I don't understand what it means. The script itself reads the number of columns and their names from the pydataset. At least that's what the video says. I don't set it anywhere in the script as far as I understand.
In other words - if I am doing everything exactly as it is done in the video, why do I get the error while the script in the video works just fine? What part of my script is wrong?

pturmel · February 17, 2025, 4:23pm

The suggested script assumes the order and type of columns in your actual DB table match the order and type of columns in your dataset. If that isn't true, you will need to use explicit SQL column names in your query.

You would replace something like this (which is what the example does)

INSERT INTO [SomeTable] VALUES (?, ?, ?)

with something like this:

INSERT INTO [SomeTable] ([SomeColumnA], [SomeColumnB], [SomeColumnC]) VALUES (?, ?, ?)

JordanCClark · February 17, 2025, 4:27pm

EDIT: nvm, Just verified through a simple script, and it comes out okay, either way.

~~Think your line here:~~

pholders = "({:s})".format(",".join("?"*cols))

should be:

pholders = "({:s})".format(",".join(["?"]*cols))

Note the brackets around the question mark.

Grigory_Vogman · February 17, 2025, 5:51pm

Thank you for the explanation! I found the cause of error. The number of columns in the DB table was one less than number of columns in the CSV I am trying to import. Spent two days trying to solve it...