---
title: "Updating the Database through the Power Table"
---

# Updating the Database through the Power Table

> Suppose your database had a Table like this:

## Updating Table Data
Table components can do more than show data from a database. A properly configured Table can make the data of the Table accessible to the Client and allow the user to edit the data in realtime. This page shows two examples using the Table and Power Table components editing data in a database in realtime.

|Id|UserName|FirstName|LastName|Notes|
|:---|:---|:---|:---|:---|
|1|JS|John|Smith|Likes bikes|
|2|LJ|Luke|Johnson|Lives in town|
|3|PB|Peter|Burke|Enjoys cooking|

## Using a Power Table Component
Let's create a script on a Power Table component and the **onCellEdited** extension function that will write to the cell in the database.

:::warning
This function will not work using default [Client Permissions](../../../../ignition-modules/vision/vision-project-properties/vision-project-properties.md#vision-permissions-properties) settings. The Legacy Database Access will need to be enabled for this to work.
:::

1. Drag a Power Table component to your window, and bind the **Data** property of the Power Table using a SQL Query Binding Type and the query below.
    ```sql title="SQL - Selects All Records"
    SELECT * FROM users
    ```

2. Right-click on the **Power Table** and select **Customizers** > **Table Customizer**.
3. Select the **Editable** checkbox for each of the columns that we want to edit (i.e., same as the above example). Note, the **id** column should not be editable. Click the **OK** button.
4. Right-click the **Power Table** and select **Scripting**.
5. Under the Extension Functions folder, select the **onCellEdited** function, and check the **Enable** box. We can put a script in here that will grab the id column value of the row that we modify, and then use that and the new value that we entered to update the Database table. The extension function makes this easy, because it provides variables for the column name of the row we are editing, the row number that was edited, as well as the new value. Paste the following script in the scripting area.

    ```python title="Python - Writes to a Cell in the Database"
    # Get the id of the row we edited.
    id = self.data.getValueAt(rowIndex, 'id')
    
    # Create our query and arguments. The extension function gives us a colName variable,
    # which we can use in our query. The query will then take two arguments.
    # The value that we are updating and the id of the row we edited.
    query = "UPDATE users SET %s = ? WHERE id = ?" % (colName)
    args = [newValue, id]
 
    # Run the query with the specified arguments.
    system.db.runPrepUpdate(query, args)
 
    # Re-query the database, so we can ensure it properly updated the table.
    system.db.refresh(self, "data")
    ```

    ![](PowerTableonCellEditedScript.png)

6. Now, let's test it out. Put the Designer in **Preview Mode**, select the cell you want to edit, hit enter to commit the change, or tab to the next cell to make additional edits.

    ![](PowerTableEdits.png)

## Using a Table Component
Let's create another script like the example above, but this time we'll use a Table component's **cellEdited** event that will write to the cell in the database when we update it on the component. The script will be slightly different.

:::warning
This function will not work using default [Client Permissions](../../../../ignition-modules/vision/vision-project-properties/vision-project-properties.md#vision-permissions-properties) settings. The Legacy Database Access will need to be enabled for this to work.
:::

1. Drag a Table component to your window. With the Table component selected, bind the **Data** property of the Table using a SQL Query Binding Type and the query below.
    ```sql title="SQL - Select All Records"
    SELECT * FROM users
    ```

    ![](BindDataProptoTable2.png)

2. Right-click on the **Table** and select **Customizers > Table Customizer**.
3. Select the **Editable** checkbox for each of the columns that we will want to edit. Note, the **id** column should not be editable. Click the **OK** button.
    
    ![](TableCustomizer3.png)

4. Right-click the **Table** and select **Scripting**.
5. Create a script in the **cell > cellEdited** event handler using the script provided below. In this script, we have variables that contain the row number that was edited, the column number that was edited, and the new value. Because we do not have the name of the column that was edited, we must first grab the list of columns using the **getColumnHeaders()** system function. We can then use the column number that was edited to find the name of the column that changed and use it in the query.

    ```python title="Python - Writes to a Cell in the Database When Component is Updated"
    # Get the id of the row we edited.
    id = event.source.data.getValueAt(event.row, 'id')
    
    # Get the header names of the dataset.
    headers = system.dataset.getColumnHeaders(event.source.data)
 
    # Build our Update query. The column name is substituted in from the column that was edited.
    # The query will take two arguments. The value we are updating and the id of the row we are editing.
    query = "UPDATE users SET %s = ? WHERE id = ?" % (headers[event.column])
    args = [event.newValue, id]
 
    # Run the query with the specified arguments.
    system.db.runPrepUpdate(query, args)
 
    # Re-query the database, so we can ensure it properly updated the table.
    system.db.refresh(event.source, "data")

    ```

    ![](ScriptEdtiorCellEditEventHandler.png)

6. Now, you're ready to test it out!  Put the Designer in **Preview Mode**, select the cell you want to edit, hit enter to commit the change, or tab to the next cell to make additional edits.

    ![](EditingaCell.png)