How To Use the sqlite3 Module in Python 3

cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")
connection.cursor() returns a Cursor object. Cursor objects allow us to send SQL statements to a SQLite database using cursor.execute(). The "CREATE TABLE fish ..." string is a SQL statement that creates a table named fish with the three columns described earlier: name of type TEXT, species of type TEXT, and tank_number of type INTEGER.
Now that we have created a table, we can insert rows of data into it:
cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")
We call cursor.execute() two times: once to insert a row for the shark Sammy in tank 1, and once to insert a row for the cuttlefish Jamie in tank 7. "INSERT INTO fish VALUES ..." is a SQL statement that allows us to add rows to a table.
In the next section, we will use a SQL SELECT statement to inspect the rows we just inserted into our fish table.
Step 3 — Reading Data from the SQLite Database

In Step 2, we added two rows to a SQLite table named fish. We can retrieve those rows using a SELECT SQL statement:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
If we run this code, we will see output like the following:
Output
[('Sammy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]
The cursor.execute() function runs a SELECT statement to retrieve values for the name, species, and tank_number columns in the fish table. fetchall() retrieves all the results of the SELECT statement. When we print(rows) we see a list of two tuples. Each tuple has three entries; one entry for each column we selected from the fish table. The two tuples have the data we inserted in Step 2: one tuple for Sammy the shark, and one tuple for Jamie the cuttlefish.
If we wanted to retrieve rows in the fish table that match a specific set of criteria, we can use a WHERE clause:
target_fish_name = "Jamie"
rows = cursor.execute(
    "SELECT name, species, tank_number FROM fish WHERE name = ?",
    (target_fish_name,)




    
,
).fetchall()
print(rows)
If we run this, we will see output like the following:
Output
[('Jamie', 'cuttlefish', 7)]
As with the previous example, cursor.execute(<SQL statement>).fetchall() allows us to fetch all the results of a SELECT statement. The WHERE clause in the SELECT statement filters for rows where the value of name is target_fish_name. Notice that we use ? to substitute our target_fish_name variable into the SELECT statement. We expect to only match one row, and indeed we only see the row for Jamie the cuttlefish returned.
Warning: Never use Python string operations to dynamically create a SQL statement string. Using Python string operations to assemble a SQL statement string leaves you vulnerable to SQL injection attacks. SQL injection attacks can be used to steal, alter, or otherwise modify data stored in your database. Always use the ? placeholder in your SQL statements to dynamically substitute values from your Python program. Pass a tuple of values as the second argument to Cursor.execute() to bind your values to the SQL statement. This substitution pattern is demonstrated here and in other parts of this tutorial as well.
Step 4 — Modifying Data in the SQLite Database

Rows in a SQLite database can be modified using UPDATE and DELETE SQL statements.
Let’s say, for example, that Sammy the shark was moved to tank number 2. We can change Sammy’s row in the fish table to reflect this change:
new_tank_number = 2
moved_fish_name = "Sammy"
cursor.execute(
    "UPDATE fish SET tank_number = ? WHERE name = ?",
    (new_tank_number, moved_fish_name)
We issue an UPDATE SQL statement to change the tank_number of Sammy to its new value of 2. The WHERE clause in the UPDATE statement ensures we only change the value of tank_number if a row has name = "Sammy".
If we run the following SELECT statement, we can confirm our update was made correctly:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
If we run this, we will see output like the following:
Output
[('Sammy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]
Notice that the row for Sammy now has the value of 2 for its tank_number column.
Let’s say that Sammy the shark was released into the wild and no longer held by the aquarium. Since Sammy no longer lives at the aquarium, it would make sense to remove the Sammy row from the fish table.
Issue a DELETE SQL statement to remove a row:
released_fish_name = "Sammy"
cursor.execute(
    "DELETE FROM fish WHERE name = ?",
    (released_fish_name,)
We issue a DELETE SQL statement to remove the row for Sammy the shark. The WHERE clause in the DELETE statement ensures we only delete a row if that row has name = "Sammy".
If we run the following SELECT statement, we can confirm our deletion was made correctly:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
If we run this code, we will see output like the following:
Output
[('Jamie', 'cuttlefish', 7)]
Notice that the row for Sammy the shark is now gone, and only Jamie the cuttlefish remains.
Step 5 — Using with Statements For Automatic Cleanup

In this tutorial, we’ve used two primary objects to interact with the "aquarium.db" SQLite database: a Connection object named connection, and a Cursor object named cursor.
In the same way that Python files should be closed when we are done working with them, Connection and Cursor objects should also be closed when they are no longer needed.
We can use a with statement to help us automatically close Connection and Cursor objects:
from contextlib import closing
with closing(sqlite3.connect("aquarium.db")) as connection:
    with closing(connection.cursor()) as cursor:
        rows = cursor.execute("SELECT 1").fetchall()
        print(rows)
closing is a convenience function provided by the contextlib module. When a with statement exits, closing ensures that close() is called on whatever object is passed to it. The closing function is used twice in this example. Once to ensure that the Connection object returned by sqlite3.connect() is automatically closed, and a second time to ensure that the Cursor object returned by connection.cursor() is automatically closed.
If we run this code, we will see output like the following:
Output
[(1,)]
Since "SELECT 1" is a SQL statement that always returns a single row with a single column with a value of 1, it makes sense to see a single tuple with 1 as its only value returned by our code.
Conclusion

The sqlite3 module is a powerful part of the Python standard library; it lets us work with a fully featured on-disk SQL database without installing any additional software.
In this tutorial, we learned how to use the sqlite3 module to connect to a SQLite database, add data to that database, as well as read and modify data in that database. Along the way, we also learned about the risks of SQL injection attacks and how to use contextlib.closing to automatically call close() on Python objects in with statements.
From here we can learn more about SQL databases in SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.