sqlite3

SQLite is a C library that provides a lightweight disk-based database that doesn't require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It's also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

If you want to know more about SQLite: https://www.sqlite.org/index.html

This section assumes you have a fundamental understanding of database concepts, including cursors and transactions.

Create Connection

import sqlite3

conn = sqlite3.connect("sqlite_test.db")

Call sqlite3.connect() to create a connection to the database sqlite_test.db in the current working directory, implicitly creating one if it does not exist.

conn = sqlite3.connect(":memory:")

You can also create a database in memory.

In order to execute SQL statements and fetch results from SQL queries, you need to use a database cursor. Call conn.cursor() to create the Curosr:

cur = conn.cursor()

Create Table

Now that you've got a database connection and a cursor, you can create a database table album with columns for title, release year, and singer. For simplicity, you can just use column names in the table declartion. SQLite has a flexing typing feature that makes specifying the data types optional.

cur.execute("CREATE TABLE album(title, year, singer)")

You can verify that new table has been created by querying the sqlite_master table built-in to SQLite, which should now contain an entry for the album table definition.

result = cur.execute("SELECT name FROM sqlite_master")
result.fetchone() # ('album',)

If you are checking of an in-memory table, then query sqlite_temp_master instead of sqlite_master.

query = "SELECT name FROM sqlite_temp_master WHERE " \
        "type='table' AND name='album'"
result = cur.execute(query)

Insert Data

Now, add some data by executing an INSERT statement with SQL literals.

cur.execute("""INSERT INTO album VALUES
    ('Music', 2000, 'Madonna'),
    ('Justified', 2002, 'Justin Timberlake')
""")

The INSERT statement implicitly opens a transaction, which needs to be committed before changes are saved in the database. Call conn.commit() on the connection object to commit the transaction:

conn.commit()

You may also use cursor.executemany(...) to insert more rows.

data = [
    ("21", 2011, "Adele"),
    ("Man of the Woods", 2018, "Justin Timberlake"),
    ("folklore", 2020, "Taylor Swift")
]
cur.executemany("INSERT INTO album VALUES(?, ?, ?)", data)
conn.commit() # Remember to commit the transaction

Notice that ? placeholders are used to bind data to the query. Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks.

Select Data

You can verify the data was inserted correctly by executing SELECT query.

res = cur.execute("SELECT year FROM album")
res.fetchall() # [(2000,), (2002,), (2011,), (2018,), (2020,)]

Delete Data

Now you may delete some data by executing an DELETE statement.

cur.execute("DELETE FROM album WHERE year > 2012")
print("Deleted", cur.rowcount, "records.") # Deleted 2 records.
conn.commit()

Close Connection

Finally, just close the connection after everything is done.

conn.close()

Code Challenge

Try to modify the code in the editor to play with sqlite and output [('21', 2011, 'Adele'), ('reputation', 2017, 'Taylor Swift')].