Sling Academy
Home/Python/Python sqlite3: CRUD examples

Python sqlite3: CRUD examples

Last updated: February 06, 2024

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!

Next Article: Python sqlite3: How to remove duplicates from a table

Previous Article: Python: Converting an SQLite database to CSV and vice versa

Series: Data Persistence in Python – Tutorials & Examples

Python

You May Also Like

  • Introduction to yfinance: Fetching Historical Stock Data in Python
  • Monitoring Volatility and Daily Averages Using cryptocompare
  • Advanced DOM Interactions: XPath and CSS Selectors in Playwright (Python)
  • Automating Strategy Updates and Version Control in freqtrade
  • Setting Up a freqtrade Dashboard for Real-Time Monitoring
  • Deploying freqtrade on a Cloud Server or Docker Environment
  • Optimizing Strategy Parameters with freqtrade’s Hyperopt
  • Risk Management: Setting Stop Loss, Trailing Stops, and ROI in freqtrade
  • Integrating freqtrade with TA-Lib and pandas-ta Indicators
  • Handling Multiple Pairs and Portfolios with freqtrade
  • Using freqtrade’s Backtesting and Hyperopt Modules
  • Developing Custom Trading Strategies for freqtrade
  • Debugging Common freqtrade Errors: Exchange Connectivity and More
  • Configuring freqtrade Bot Settings and Strategy Parameters
  • Installing freqtrade for Automated Crypto Trading in Python
  • Scaling cryptofeed for High-Frequency Trading Environments
  • Building a Real-Time Market Dashboard Using cryptofeed in Python
  • Customizing cryptofeed Callbacks for Advanced Market Insights
  • Integrating cryptofeed into Automated Trading Bots