Python sqlite3: CRUD examples

Introduction to SQLite

SQLite is a C library that provides a lightweight disk-based database. It doesn’t require a separate server process, making it ideal for embedded applications, development servers, or local data storage.

Python introduces several enhancements and optimizations, making it more appealing for developers to implement database operations efficiently. This tutorial covers the CRUD (Create, Read, Update, Delete) operations in Python 3 using the built-in sqlite3 module. By the end of this tutorial, you’ll have a solid foundation for managing SQLite databases in Python.

Ensure you have Python 3 installed. Verify this by running python --version in your terminal. If you don’t have it, download it from the official Python website.

Creating a Database and Table

To perform any CRUD operation, first, we need to create a database and table. Here’s how:

import sqlite3

def create_connection(db_file):
    """ create a database connection to a SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(f'Successful connection to {db_file}')
    except sqlite3.Error as e:
        print(e)
    finally:
        if conn:
            conn.close()
            
if __name__ == '__main__':
    create_connection(r'test.db')

This function creates a connection to an SQLite database or creates the database if it doesn’t exist. Notice the use of r before the database name, indicating a raw string to handle any escape characters.

Creating a Table

Once the database is up, let’s create a table named users.

def create_table(db_file):
    create_users_table_sql = '''
    CREATE TABLE IF NOT EXISTS users (
        id integer PRIMARY KEY,
        name text NOT NULL,
        email text NOT NULL UNIQUE
    );
    '''
    conn = sqlite3.connect(db_file)
    try:
        c = conn.cursor()
        c.execute(create_users_table_sql)
        conn.commit()
        print("Table created successfully.")
    except sqlite3.Error as e:
        print(e)
    finally:
        conn.close()

Now, let’s add some data to our table.

CREATE: Adding Data

To insert data:

def add_user(db_file, user):
    sql = ''' INSERT INTO users(name, email)
              VALUES(?,?) '''
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute(sql, user)
    conn.commit()
    return cur.lastrowid

Continuing, we need to read data.

READ: Fetching Data

For reading data:

def select_all_users(db_file):
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute("SELECT * FROM users")
    rows = cur.fetchall()
    for row in rows:
        print(row)

Up until now, we have implemented CREATE and READ. Next, let’s tackle UPDATE.

UPDATE: Modifying Data

To update a user’s email:

def update_user(db_file, user):
    sql = ''' UPDATE users
              SET email = ?
              WHERE name = ?'''
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute(sql, user)
    conn.commit()

Lastly, we DEAL with the deletion of data.

DELETE: Removing Data

To delete a user:

def delete_user(db_file, name):
    sql = 'DELETE FROM users WHERE name = ?'
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute(sql, (name,))
    conn.commit()

Bringing It All Together

By now, you should be comfortable with the basic CRUD operations using the sqlite3 module in Python 3.11. As a best practice, remember to handle exceptions and close your database connections properly. Implement proper error handling and data validation in your real-world applications.

Conclusion

In this tutorial, we’ve explored how to perform CRUD operations using the sqlite3 module in Python 3. These basic operations form the backbone of database management and serve as a solid foundation for building more complex data-driven applications. Happy coding!