Python sqlite3 Select and Insert

Now let’s look at a Python sqlite3 Select and Insert example. Recall that a SQL INSERT statement is used to insert rows into a table and a SQL SELECT query is used to return a set of rows from a table.

Let’s begin with some code:

import sqlite3
con = sqlite3.connect('data/sample.db')
cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE IF NOT EXISTS Playlist
              (createdate text, 
               id integer, 
               artist text, 
               songTitle text, 
               albumTitle text, 
               duration text, 
               songprice real)''')

# Insert rows of data
cur.execute("INSERT INTO PlayList VALUES ('2022-01-31',1,'Kanye West','Fade','Life of Pablo','3:13',2.99)")
cur.execute("INSERT INTO PlayList VALUES ('2022-01-31',2,'Kanye West','Ultralight Beam','Life of Pablo','5:20',3.99)")
cur.execute("INSERT INTO PlayList VALUES ('2022-01-31',3,'Kanye West','Famous','Life of Pablo','3:16',1.99)")


# Save (commit) the changes
con.commit()

#select rows
for row in cur.execute('SELECT * FROM PlayList'):
    print(row)

con.close()

Let’s explain what is going on here:

1. We import the sqlite3 library, connect to our SQLite Database sample.db and define the cursor cur. The cursor is what we will be using to apply our SQL Commands to our SQLite Database.
2. cur.execute() method accepts our SQL Statement. In this case our SQL Statement creates the table Playlist if it doesn’t already exist. It has 7 columns.
3. The 2nd, 3rd and 4th cur.execute() statements is what we will use to INSERT rows into our database. We are inserting 3 rows into our database.
4. con.commit() method commits the changes pending in the current insert transaction. The transactions are our INSERT statements. If we don’t commit, we will lose all changes if we close the database connection. Please remember this.
5. The 5th cur.execute() is what we will use to SELECT the data stored in our table. Remember we must commit before inserted rows are actually saved to our table. A simple FOR-loop is enough to return the rows from our table.

When we execute the above code block we will get the following output:

('2022-01-31', 1, 'Kanye West', 'Fade', 'Life of Pablo', '3:13', 2.99)
('2022-01-31', 2, 'Kanye West', 'Ultralight Beam', 'Life of Pablo', '5:20', 3.99)
('2022-01-31', 3, 'Kanye West', 'Famous', 'Life of Pablo', '3:16', 1.99)

We can execute other SELECT statements with WHERE conditions that will work exactly as we expect:

#select rows
for row in cur.execute('SELECT * FROM PlayList WHERE songprice > 3'):
    print(row)

#Output
#('2022-01-31', 2, 'Kanye West', 'Ultralight Beam', 'Life of Pablo', '5:20', 3.99)
    
#select rows
for row in cur.execute("SELECT * FROM PlayList WHERE songTitle LIKE '%Fa%'"):
    print(row)

#Output
#('2022-01-31', 1, 'Kanye West', 'Fade', 'Life of Pablo', '3:13', 2.99)
#('2022-01-31', 3, 'Kanye West', 'Famous', 'Life of Pablo', '3:16', 1.99)

Thanks for reading. Find the full code HERE, go back to the first part of this tutorial HERE and HERE to find out about deleting rows in SQLite. Cheers! 👌👌👌