# Script won't delete rows in database **URL:** \ **Category:** Ignition\ **Tags:** database, scripting\ **Created:** [August 6, 2020, 11:16pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485 "2020-08-06T23:16:17Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 6, 2020, 11:16pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/1 "2020-08-06T23:16:17Z")
Hello, I’m really new to Ignition 8 and programming in general. I’m trying to follow the instructions as per [Simple Database Editor - Ignition User Manual 8.0 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC80/Simple+Database+Editor) with some changes as appropriate to my database (column names, etc). So far the function to add a line is working fine, but I can’t seem to make headway on the delete function. When I test the page and select lines and delete them, nothing happens. No error message, no rows get deleted. I tried using the Database Query browser and the Query itself works fine (deletes a row if I specify the Name or ID, or such). I’ve added my customized script here. ![Customer Delete Script](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/a/0a4f80520e56e96d44365a76efeff09c1a6731a9.jpeg) Hoping someone can see what I’ve done… ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [August 7, 2020, 1:31am UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/2 "2020-08-07T01:31:02Z")
The code looks ok. It relies on the format of the table and the named query to be correct though. To debug I’d put in a few print statements to see where its failing. E.g. “print id” statement after it gets the id to make sure it’s getting the right value. Also, have you tried testing the named query by itself in the Named Query testing tab, or from the ScriptConsole? ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 7, 2020, 1:36pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/3 "2020-08-07T13:36:11Z")
On top of what @DavidWisely said, I would also add a variable to your system.db.runNamedQuery() function. For an update query, it should return an int representing how many records are affected. ```auto nq = system.db.runNamedQuery("Customers/Customers Deleting", {"Customer_ID":id}) print nq ``` I also agree with @DavidWisely about testing the named query. I would test it in the Script Console to make sure it functions correctly when called from a script. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 7, 2020, 2:45pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/4 "2020-08-07T14:45:31Z")
Are you sure your logic is right in line 8… should it not be; Are you Sure?, 1 ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 7, 2020, 2:52pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/5 "2020-08-07T14:52:58Z")
Unless I’m wrong, the 0 isn’t needed, if he had a 1 then it would give a yes/no/cancel option instead of just yes/no. The no still returns a 0 which would send him to the else statement. The cancel option would just allow a None to be returned. If I had to guess, I think it probably has to do with the named query. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 7, 2020, 3:02pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/6 "2020-08-07T15:02:18Z")
[https://docs.inductiveautomation.com/display/DOC80/system.gui.confirm](https://docs.inductiveautomation.com/display/DOC80/system.gui.confirm) When he clicks Yes (Yes I want to delete X rows), it returns a 1, If he clicks no, it returns a 0, If he clicks cancel, none is returned So surely the logic should be Yes (1), delete my rows ---
**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\ **Post date:** [August 7, 2020, 3:09pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/7 "2020-08-07T15:09:45Z")
That is what he has on line 8. He just didn’t call out the == 1 a the end of the if so anything other than 0 or none will be true, 0 or none will go to the else. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 7, 2020, 3:12pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/8 "2020-08-07T15:12:29Z")
Yep sorry only skimmed what I linked, read and comprehended while you were typing. Agree with you 🙂 ---
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 10, 2020, 10:50pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/9 "2020-08-10T22:50:09Z")
re: DavidWisely yes I did test the query itself in both the database console and in Ignition, both will delete a single row. When I test the script in Tools \> Script Console (Is there another tool somewhere else? I am new to all this…) I get an error that I don’t get when running the test page: Traceback (most recent call last): File “”, line 2, in NameError: name ‘event’ is not defined Is this an error that will silently end the script or something? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [August 11, 2020, 1:50am UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/10 "2020-08-11T01:50:22Z")
The `event` comes from the component event handler, so this won’t exist in the script console. You can use the script console to test parts of your code that don’t require anything from the event or that references components from the Window, e.g. your function that deletes your rows ---
**Author:** ![DavidWisely](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davidwisely/32/10482_2.png) [@DavidWisely](https://forum.inductiveautomation.com/u/DavidWisely)\ **Post date:** [August 11, 2020, 6:40am UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/11 "2020-08-11T06:40:02Z")
@Matrix_Engineering, yes Tools -\> Script Console is correct. As @nminchin said, you can test just parts of your script in the console. I sugget this to test your named query: > id = 5 # use a number for a Customer\_ID that you can delete > rowsAffected = system.db.runNamedQuery("Customers/Customers Deleting", {"Customer\_ID":id}) > print rowsAffected Check that it prints 1 if the named query is correct. Once this is confirmed, put some print statements in your existing script to work out where it's failing. Something like this (paraphrasing): > selRows = event.source.parent.getCompent('Customer Power Table' ... > print "selRows", selRows > > if len(selRows) \> 0: > if system.gui.confirm("Are you sure you want to delete ... > > ``` > for row in selRows: > > id = event.source.aprent.getComponent('Customer Power Table' .... > print "id", id > > rowsAffected = system.db.runNamedQuery('Customers/Customers Deleting" ... > print "rowsAffected", rowsAffected > > system.db.refresh(event.source.parent.getComponent(.... > > ``` > > else: > system.gui.messageBox( ... > > else: > system.gui.messageBox( ... Then execute the script in the designer. You can see the print outs of the print statements in the Output Console: View -\> Panels -\> Output Console Or execute it in a client and view the output in the Diagnostic Window. Display the diagnostic widow by pressing Ctrl+Shift+F7 ---
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 17, 2020, 5:38pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/12 "2020-08-17T17:38:13Z")
> [@DavidWisely](#): > > I sugget this to test your named query: > > > id = 5 # use a number for a Customer\_ID that you can delete > > rowsAffected = system.db.runNamedQuery(“Customers/Customers Deleting”, {“Customer\_ID”:id}) > > print rowsAffected > > Check that it prints 1 if the named query is correct. The print statement returns a 0, so I checked the Query internally in the database (mariaDB) using: DELETE FROM `Customers` WHERE `Customers`.`Customer_ID` = :id (substituted 50 for :id) and it deletes just row 50, successfully. This leaves me a little confused: the query works fine, but the script calling the query doesn't? When I added the print statements to see what's being done, I got: array('i', [0]) 52 #row being deleted 0 #number of rows deleted The scripting for those results looks like: # If a row is selected, ask for confirmation before deleting the rows. if system.gui.confirm("Are you sure you want to delete " + str(len(selRows)) + " row(s)?", "Are You Sure?", 0): print selRows # If the user gave confirmation, we need to loop through all of them. for row in selRows: ``` # For each selected row, we need to get the value of the id column in that row. id = event.source.parent.getComponent('Customer Power Table').data.getValueAt(row, "Customer_ID") print id # Use the name of the row to delete it from the database table. Print the number of rows deleted. nq = system.db.runNamedQuery("Customers/Customers Deleting", {"Customer_ID":id}) print nq ``` I'm confused why the script can identify the correct row (52) to delete, but then fails to delete it, using a query that is tested to work. I really really appreciate everyone's input on this, I know these must seem like baby steps. I enjoy learning new stuff, even including the frustrations! Robb edit\* not sure why the formatting is all wrong, hopefully it's readable. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 17, 2020, 5:47pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/13 "2020-08-17T17:47:54Z")
Make sure that in your named query that you have it set as an **Update Query** and not just a **Query** ---
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 17, 2020, 5:50pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/14 "2020-08-17T17:50:36Z")
@dkhayes117 Yes, I confirmed that it is an update query. When I run the Query directly in Ignition it works, though I have to specify the row to be deleted, rather than the scripted :id but it does work. Here is the Named Query: DELETE FROM `Customers` WHERE `Customers`.`Customer_ID` = :id ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 17, 2020, 5:59pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/15 "2020-08-17T17:59:55Z")
It seems if the query works with a hardcoded id number, then you customer Ids are not what you are expecting. As @DavidWisely mentioned, print the id numbers with the script without running the query. You may also want to verify the id types with `print(type(id))` and make sure your column names and parameter names are all correct. ---
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 17, 2020, 7:22pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/16 "2020-08-17T19:22:57Z")
I’m sure my types are correct, both in database (int2) and query (int2). I added the print type command, and it returns type ‘int’. I’m wondering if the first returned line has some information: array(‘i’, [0]) \<-- is the 0 here the id that is being deleted, or is it just the element number of the array?For example if I select 4 items from the list, it returns as: array(‘i’,[0, 1, 2, 3] which is the four items. Edit: Found the reference in python, it is the elements of the array. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 17, 2020, 7:41pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/17 "2020-08-17T19:41:52Z")
I use this exact code in a couple of projects and it works fine. The only possibility for the error has to be the your ids in the for loop imo. I recommend you post the dataset you are testing with if not sensitive. ---
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 17, 2020, 7:48pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/18 "2020-08-17T19:48:09Z")
[Customers.sql](https://forum.inductiveautomation.com/uploads/short-url/lEkSm2hh5y9e05yacjR8QFhzgyy.sql) (1.6 KB) I think this is the table. Do you want to see the whole ignition project too? I’ve posted screen shots of what I thought was the relevant parts. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 17, 2020, 8:01pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/19 "2020-08-17T20:01:28Z")
No don’t need the whole project, but I would like the actual dataset. When you select the table or whatever component it is, select the data property. When it is open use the copy button then paste that here ---
**Author:** ![robb](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@robb](https://forum.inductiveautomation.com/u/robb)\ **Post date:** [August 17, 2020, 8:03pm UTC](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485/20 "2020-08-17T20:03:27Z")
“#NAMES” “Customer\_ID”,“Customer\_Name”,“Comments” “#TYPES” “I”,“str”,“str” “#ROWS”,“3” “52”,“Koppers”,“Koppers Corporate” “53”,“CPR”,“Canadian Pacific Railway” “55”,“Testing”,“Testing - To delete” [Next page](https://forum.inductiveautomation.com/t/script-wont-delete-rows-in-database/37485.md?page=2)