Python SQLite3 Programming

Introduction

In this lab, we will be working with the sqlite3 module in Python. SQLite is a popular lightweight relational database management system that is often used for embedded systems and mobile applications. It is also commonly used for small web applications or other projects that require a small database.

In this lab, we will cover the basics of SQLite and how to work with it in Python. We will start with a brief overview of SQLite and then move on to creating a database, inserting data, querying data, and finally updating and deleting data.

Skills Graph

Creating a Database

The first step in working with SQLite in Python is to create a new database. You can create a new database using the sqlite3 module as follows:

import sqlite3

## Create a new database
conn = sqlite3.connect('example.db')

The connect function creates a new database if the specified database does not exist. If the database already exists, it will connect to it. The conn variable represents a connection to the database.

Then run the command below:

python3 sqlite3_programming.py

Creating a Table

Once you have created a new database, you can create a table to store data in. Here is an example of how to create a table:

import sqlite3

## Create a new database
conn = sqlite3.connect('example.db')

## Create a new table
conn.execute('''CREATE TABLE users
             (id INTEGER PRIMARY KEY,
              name TEXT NOT NULL,
              email TEXT NOT NULL,
              age INTEGER);''')

## Commit the transaction
conn.commit()

This code creates a new table called users with four columns: id, name,email, and age. The id column is the primary key, which means that it is unique for each row and is used to identify each row in the table.

Then run the command below:

python3 sqlite3_programming.py

Inserting Data

After you have created a table, you can start inserting data into it. Here is an example of how to insert data:

import sqlite3

## Create a new database
conn = sqlite3.connect('example.db')

## Insert data into the users table
conn.execute("INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30)")
conn.execute("INSERT INTO users (name, email, age) VALUES ('Jane Doe', 'jane@example.com', 25)")

## Commit the transaction
conn.commit()

This code inserts two new rows into the users table. The INSERT INTO statement specifies the table name and the values to insert into the table.

Then run the command below:

python3 sqlite3_programming.py

Querying Data

Once you have inserted data into the table, you can query it to retrieve data. Here is an example of how to query data:

import sqlite3

## Create a new database
conn = sqlite3.connect('example.db')

## Query the users table
cursor = conn.execute("SELECT id, name, email, age from users")
for row in cursor:
    print(row)

## Commit the transaction
conn.commit()

This code queries the users table and prints out each row. The SELECT statement specifies the columns to retrieve from the table.

Then run the command below:

python3 sqlite3_programming.py

Updating Data

You can also update existing data in the table by using an UPDATE statement. Here is an example of how to update data:

import sqlite3

## Create a new database
conn = sqlite3.connect('example.db')

## Update data in the users table
conn.execute("UPDATE users SET age = 40 WHERE name = 'Jane Doe'")

## Commit the transaction
conn.commit()

This code updates the age column for the row with name equals to 'Jane Doe'. The UPDATE statement specifies the table name, the column to update, and the new value.

Then run the command below:

python3 sqlite3_programming.py

Deleting Data

You can also delete data from the table by using a DELETE statement. Here is an example of how to delete data:

import sqlite3

## Create a new database
conn = sqlite3.connect('example.db')

## Delete data from the users table
conn.execute("DELETE FROM users WHERE name = 'Jane Doe'")

## Commit the transaction
conn.commit()

This code deletes the row with name equals to 'Jane Doe' from the users table. The DELETE FROM statement specifies the table name and the condition for deleting rows.

Then run the command below:

python3 sqlite3_programming.py

Summary

In this lab, we covered the basics of working with SQLite in Python. We started with creating a database, creating a table, inserting data, querying data, and finally updating and deleting data. The code examples went from simple to more complex operations.

We hope that this lab has helped you understand how to use SQLite in your Python projects. Remember, this is just the beginning and there are many more advanced features that you can explore with SQLite. Happy coding!