Updating and Deleting from SQLite Tutorial

Up to this point with our SQLite and Python tutorial series, you have been shown how to create a database, a table, how to insert data, and how to read data. In this tutorial, we're going to talk about how to modify existing data, as well as how to delete data.

It is important to note that there are no undos when it comes to SQL. Once you delete something, or once you modify it, that's that. Take your time, read over, and re-read your queries before you do them!

First, let's run an update. Before that, we'll look at the existing data:

def del_and_update():
    c.execute('SELECT * FROM stuffToPlot')
    data = c.fetchall()
    [print(row) for row in data]

Output:

(1452562314.0, '2016-01-11 17:31:54', 'Python', 1.0)
(1452562315.0, '2016-01-11 17:31:55', 'Python', 4.0)
(1452562316.0, '2016-01-11 17:31:56', 'Python', 8.0)
(1452562317.0, '2016-01-11 17:31:57', 'Python', 3.0)
(1452562318.0, '2016-01-11 17:31:58', 'Python', 9.0)
(1452562319.0, '2016-01-11 17:31:59', 'Python', 3.0)
(1452562320.0, '2016-01-11 17:32:00', 'Python', 3.0)
(1452562321.0, '2016-01-11 17:32:01', 'Python', 3.0)
(1452562322.0, '2016-01-11 17:32:02', 'Python', 4.0)
(1452562323.0, '2016-01-11 17:32:03', 'Python', 2.0)

Now, we can update some data, like:

    c.execute('UPDATE stuffToPlot SET value = 99 WHERE value = 3')
    conn.commit()

    c.execute('SELECT * FROM stuffToPlot')
    data = c.fetchall()
    [print(row) for row in data]

Output:

(1452562314.0, '2016-01-11 17:31:54', 'Python', 1.0)
(1452562315.0, '2016-01-11 17:31:55', 'Python', 4.0)
(1452562316.0, '2016-01-11 17:31:56', 'Python', 8.0)
(1452562317.0, '2016-01-11 17:31:57', 'Python', 99.0)
(1452562318.0, '2016-01-11 17:31:58', 'Python', 9.0)
(1452562319.0, '2016-01-11 17:31:59', 'Python', 99.0)
(1452562320.0, '2016-01-11 17:32:00', 'Python', 99.0)
(1452562321.0, '2016-01-11 17:32:01', 'Python', 99.0)
(1452562322.0, '2016-01-11 17:32:02', 'Python', 4.0)
(1452562323.0, '2016-01-11 17:32:03', 'Python', 2.0)

Finally, we can delete things:

    c.execute('DELETE FROM stuffToPlot WHERE value = 99')
    conn.commit()

    c.execute('SELECT * FROM stuffToPlot')
    data = c.fetchall()
    [print(row) for row in data]

Output:

(1452562314.0, '2016-01-11 17:31:54', 'Python', 1.0)
(1452562315.0, '2016-01-11 17:31:55', 'Python', 4.0)
(1452562316.0, '2016-01-11 17:31:56', 'Python', 8.0)
(1452562318.0, '2016-01-11 17:31:58', 'Python', 9.0)
(1452562322.0, '2016-01-11 17:32:02', 'Python', 4.0)
(1452562323.0, '2016-01-11 17:32:03', 'Python', 2.0)

One thing you can do before you run an update or delete, is mentally consider how many rows you think those changes should affect. Then, rather than saying DELETE FROM, do a SELECT *. Then, do a len(data), and see what the length of the data is. If your entire database is a million rows, you expected the query to affect maybe 100 rows, and instead you see the length is 563,052, then you can check your query again before you cause yourself a lot of pain! In a full SQL deployment, you can also use the LIMIT parameter, but that isn't available in SQLite.

So there you have the basics of SQL and SQLite. Moving from SQLite to MySQL is very simple. The ? values are %s, there are many more data types, and there are a few more commands at your disposal.

Want more tutorials? Head to the Home Page