while during the software is upload the data to my database, the client is not able to do other things
path = system.file.openFile("csv")
data_string = system.file.readFileAsString(path)
# Convert the string to a dataset and store in a variable
data = system.dataset.fromCSV(data_string)
# Assign the dataset to a table
query="""INSERT INTO [dbo].[HYText]
([THBH]
,[Project]
,[ZCBH]
,[ZCMX]
,[UpdateTime])
VALUES
(?,?,?,?,SYSDATETIME())
"""
py_TableData = system.dataset.toPyDataSet(data)
system.gui.messageBox(u'请注意,数据在上传中, 请稍后')
for row in py_TableData:
args = [row[2], row[3],row[4],row[5]]
system.db.runPrepUpdate(query, args)
the upside is the logic, how to optimize the logic then while I am upload the data, I can do some other things?
This seems like a good candidate to be doing in a gateway script. Vision clients have one GUI thread. If you’re running this on button or something, then until it completes, the GUI will be unusable.
@nader.chinichian is correct, to call this on a background thread you would use system.util.invokeAsynchronous(). as for optimization, consider inserting multiple rows with a single query, as opposed to calling the query each time through a loop.
I would also avoid the un-needed conversion to a pyDataset, but that’s more personal preference than any real performance gain.
I would refactor the code to look something like this:
path = system.file.openFile('csv')
data_string = system.file.readFileAsString(path)
# Convert the string to a dataset and store in a variable
data = system.dataset.fromCSV(data_string)
#build the query
query = """INSERT INTO [dbo].[HYText]
([THBH]
,[Project]
,[ZCBH]
,[ZCMX]
,[UpdateTime])
VALUES %s"""
valueArgs = ','.join(['(?,?,?,?,SYSDATETIME())'] * data.rowCount)
system.gui.messageBox(u'请注意,数据在上传中, 请稍后')
argList = [data.getValueAt(row,col) for row in range(data.rowCount) for col in range(2,6)]
system.db.runPrepUpdate(query % valueArgs,argList)