Inserting and Retrieving Data

In Module 4, we will focus on inserting data into SQLite tables and retrieving data using SQL queries.

By Kevin McAleer,    3 Minutes

In Module 4, we will focus on inserting data into SQLite tables and retrieving data using SQL queries. You will learn how to insert records into tables and retrieve specific data using various filtering and sorting techniques.

Inserting Data

To add data to a SQLite table, we use the SQL INSERT INTO statement. This statement allows us to specify the table name and provide the values for the columns.

# Insert a record into the table
connection.execute("INSERT INTO books (title, author, year) VALUES (?, ?, ?)", ("The Great Gatsby", "F. Scott Fitzgerald", 1925))

In this example, we insert a record into the “books” table with the title, author, and year values provided.

Querying Data

Retrieving data from SQLite tables involves using the SQL SELECT statement. This statement allows us to specify which columns we want to retrieve and any conditions or filters for the query.

# Retrieve all records from the table
result = connection.execute("SELECT * FROM books")
data = result.fetchall()

# Process the retrieved data
for row in data:
    print(row)

In this code snippet, we retrieve all records from the “books” table and print each row of data.

Filtering Data with WHERE Clauses

To retrieve specific data that meets certain conditions, we can use the WHERE clause in SQL queries. The WHERE clause allows us to specify one or more conditions that records must satisfy.

# Retrieve records with a specific condition
result = connection.execute("SELECT * FROM books WHERE year > ?", (2000,))
data = result.fetchall()

In this example, we retrieve records from the “books” table where the year is greater than 2000.

Sorting Data with ORDER BY

To order the retrieved data based on specific columns, we can use the ORDER BY clause. This clause allows us to sort the result set in ascending or descending order.

# Retrieve records and sort them
result = connection.execute("SELECT * FROM books ORDER BY year DESC")
data = result.fetchall()

In this example, we retrieve records from the “books” table and sort them in descending order based on the “year” column.

Limiting and Offsetting Data with LIMIT and OFFSET

To retrieve a specific number of records or skip a certain number of records, we can use the LIMIT and OFFSET clauses.

# Retrieve a limited number of records
result = connection.execute("SELECT * FROM books LIMIT 10")
data = result.fetchall()

# Skip a certain number of records
result = connection.execute("SELECT * FROM books LIMIT 10 OFFSET 5")
data = result.fetchall()

In the first example, we retrieve the first 10 records from the “books” table. In the second example, we skip the first 5 records and retrieve the next 10 records.

By mastering these techniques, you will be able to insert data into SQLite tables and retrieve specific data using powerful querying capabilities.