Issue in using Insert Named Query on a Populated Table

Ignition
, , ,
21

Look at the props.data property on the table from your initial view.
Or drop a new table in a view and look at the sample data.

Basically, there are 2 solutions:

  • a dataset
  • json format: a list of dictionaries, where each dictionary has a key for each column.

It can be a bit more complicated in the 2nd case, as you can also add styles to the data structure, but let's not worry about this for now.

22

Fixed the Issue

def onMessageReceived(self, payload):
    user = self.session.props.auth.user.userName
    if payload['confirm']:
    	
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': str(user)
            } for row in data if row['BrassTag']
        ]
        
        # Build the query and values
        q, v = build_insert_string("BrassTag", insert_data)
        
        # Debugging: Print the query and values
        system.perspective.print("Query: {}".format(q))
        system.perspective.print("Values: {}".format(v))
                
        # Specify the database name, it is important.
        database_name = 'test_mdcmaster'
        
        # Execute the query
        if q:
            system.db.runPrepUpdate(q, v, database_name)
        
        # Clear the table data after insert
        self.getChild("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getChild("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]

def build_insert_string(table, data, columns=None):
    if not data:
        return None, None
    if columns is None:
    	# Specify the order explicitly, this should match with the insert query order
        columns = ['BrassTag', 'BrassTag_Desc', 'user']  
    marks = "({})".format(','.join("?" for _ in columns))
    marks = ",".join(marks for _ in data)
    col_names = ','.join(columns)
    q = "insert into {} ({}) values {}".format(table, col_names, marks)
    values = [row[c] for row in data for c in columns]
    return q, values
23

I haven't been following the whole thread so this might not be appropriate advice but, in general, ...

Using relative component paths like this,
self.getChild("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
makes your application rather brittle as if, for example, you wrap your table in a container then the script will break and you'll have to edit that path.

The fix is to add the messagehandler to the table itself, not the root container as you seem to have done. Then your script becomes,
self.custom.BrassTag_Ins = []
This will always work, no matter where you drag the table! It's one of the messageHandler's selling points.

1 Like
24

Yeah... My original solution had a custom property on the view and the table's data was bound to it.

@Farhan_Basheer :
Again, you should move the build_insert_string function to a library script, and keep it as I posted it. The column assignation you hardcoded inside it can be done by passing the columns list to the function through the columns parameter. But you don't even need that, since the keys of your dictionary already match the table's columns.

1 Like