SQLite in Python Tutorial

Introduction

SQLite is a self-contained, serverless, and zero-configuration SQL database engine that is ideal for small to medium-sized applications.

Installing the sqlite3 Module

To use SQLite in Python, you will first need to install the sqlite3 module. You can do this using the pip package manager by running the following command:

pip install sqlite3

Connecting to a Database

Once the sqlite3 module is installed, you can use it to connect to an existing SQLite database or create a new one. To create a new database, you can use the sqlite3.connect() function, which takes the name of the database file as an argument. If the database file does not exist, it will be created for you.

import sqlite3

# Connect to a new database or create one if it doesn't exist
conn = sqlite3.connect('mydatabase.db')

Creating a Table

To create a new table in the database, you can use the CREATE TABLE SQL command. For example, to create a table called employees with columns for the employee’s ID, name, and salary, you could use the following code:

cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT,
        salary REAL
    )
''')

Inserting Data

To insert data into the table, you can use the INSERT INTO SQL command. For example, to insert a new employee into the employees table, you could use the following code:

cursor = conn.cursor()

cursor.execute('''
    INSERT INTO employees (id, name, salary)
    VALUES (?, ?, ?)
''', (1, 'John Smith', 75000))

Retrieving Data

Retrieving All Records

To retrieve data from the table, you can use the SELECT SQL command. For example, to retrieve all employees from the employees table, you could use the following code:

cursor = conn.cursor()

cursor.execute('''
    SELECT * FROM employees
''')

employees = cursor.fetchall()

for employee in employees:
    print(employee)

Filtering Records

You can also use the WHERE clause to filter the results of a SELECT query. For example, to retrieve only employees with a salary greater than $50,000, you could use the following code:

cursor = conn.cursor()

cursor.execute('''
    SELECT * FROM employees
    WHERE salary > 50000
''')

employees = cursor.fetchall()

for employee in employees:
    print(employee)

Updating Data

To update data in the table, you can use the UPDATE SQL command. For example, to give all employees a 10% raise, you could use the following code:

cursor = conn.cursor()

cursor.execute('''
    UPDATE employees
    SET salary = salary * 1.1
''')

Closing the Connection

When you’re finished with the connection you can close it like so:

conn.close()