How to Use sqlite3 in Python: A Basic Guide with Examples

Database Python

/ by admin | Leave a comment

SQLite is an embedded database engine that allows you to create and manage databases directly from your Python code. It’s lightweight, easy to use and doesn’t require a separate server process. It was created in 2000 by D. Richard Hipp as a replacement for Berkeley DB, which was the most popular embedded database at the time. SQLite was designed to be lightweight, fast and easy to use, with a focus on providing a simple and efficient way to store and retrieve data.

In this article, we will explore how to use SQLite in Python, including creating tables, inserting data, querying data, updating data, and deleting data. At the end of the article, we will encapsulate the database operations of an entity in a class.

Table of Contents

When should sqlite be used?

SQLite can be an excellent choice for various scenarios in Python due to its lightweight nature and minimal setup requirements. For small projects that don’t require the full power of traditional database systems, SQLite is a good option because it requires no additional software to install or manage.

When prototyping ideas, SQLite can be ideal as it allows you to create tables and insert, query, update and delete data with just a few lines of code. This makes experimenting with different database designs easy and efficient, making it an excellent choice for quickly testing concepts and seeing how they work in practice.

SQLite is also often used in embedded systems where resources are limited because it doesn’t require a dedicated server process. It integrates well with web application frameworks such as Flask or Django, making it a good choice for development and prototyping before moving to more robust database systems such as PostgreSQL or MySQL in production.

When analysing data with Python, SQLite is often used as a database because it allows you to easily import data from CSV files or other sources into a relational format that can be queried using SQL. This makes it an attractive option for those working with data analysis and wanting a simple yet effective way to manage their data.

Finally, SQLite is often used in test and development environments where you need a quick and easy way to create databases for testing purposes. It’s also useful for setting up a database with sample data for demonstration or testing purposes, making it an excellent choice for developers looking to streamline their workflow.

Installation

Before you start using SQLite in Python, make sure it is installed on your machine. If not, you can install it using pip:

pip install sqlite3

Importing the Module

The first step in using SQLite in Python is to import the sqlite3 module:


import sqlite3

Connecting to a Database

To interact with a SQLite database, you need to create a connection object. If the database doesn’t exist, it will be created when you connect to it:


conn = sqlite3.connect('test_database.db')

This command creates a new database called test_database.db if it doesn’t already exist. If the database already exists, this command connects to the existing database.

Creating Tables

Once you have connected to a database, you can use SQL commands to create tables:


c = conn.cursor()
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

This command creates a new table called stocks with five columns: date, trans, symbol, qty and price. The data types are specified as text (for strings) and real (for floats).

Inserting Data

You can insert data into a table using the INSERT INTO SQL command:


c.execute("INSERT INTO stocks VALUES ('2024-03-30','BUY','RHI',20,170)")
conn.commit()

This command inserts a new row into the stocks table with the specified values. The commit() method is necessary to ensure that your changes are saved and not just stored in memory.

Querying Data

You can query data from a database using the SELECT SQL command:


c.execute('SELECT * FROM stocks')
print(c.fetchall())

This command selects all rows from the stocks table and prints them. The fetchall() method retrieves all remaining rows as a list of tuples. Each tuple corresponds to a row in the database.

Updating Data

You can update data in a table using the UPDATE SQL command:


c.execute("UPDATE stocks SET qty = 25 WHERE symbol = 'RHI'")
conn.commit()

This command updates the qty column to 25 for all rows where the symbol is ‘RHI’. The changes are saved using the commit() method.

Deleting Data

You can delete data from a table using the DELETE SQL command:


c.execute("DELETE FROM stocks WHERE symbol = 'RHI'")
conn.commit()

This command deletes all rows where the symbol is ‘RHI’. The changes are saved using the commit() method.

Closing the Connection

When you’ve finished interacting with a database, it’s a good idea to close the connection:


conn.close()

This command closes the connection to the database and frees up system resources.

Organizing Database Functions with Python Classes

n Python, classes are a great way to organise related functions into a single unit. This makes your code easier to read and maintain. In the context of SQLite databases, you can use Python classes to encapsulate all database-related and entity-related operations in one place.

This approach is particularly useful when working with complex applications that involve multiple tables or when you need to perform many different types of queries on a single table. By organizing your database functions into a class, you can easily manage and maintain the codebase as it grows over time.

Here’s an example of how you can use Python classes to organise SQLite operations for the stock table:


import sqlite3

class StockDatabase:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.c = self.conn.cursor()
        
        # Create table if it doesn't exist
        self.c.execute('''CREATE TABLE IF NOT EXISTS stocks
                           (date text, trans text, symbol text, qty real, price real)''')
    
    def insert_stock(self, date, trans, symbol, qty, price):
        self.c.execute("INSERT INTO stocks VALUES (?, ?, ?, ?, ?)", 
                        (date, trans, symbol, qty, price))
        self.conn.commit()
    
    def get_stock(self, symbol):
        self.c.execute('SELECT * FROM stocks WHERE symbol = ?', (symbol,))
        return self.c.fetchall()
        
    def update_stock(self, qty, symbol):
        self.c.execute("UPDATE stocks SET qty = ? WHERE symbol = ?", 
                        (qty, symbol))
        self.conn.commit()
    
    def delete_stock(self, symbol):
        self.c.execute("DELETE FROM stocks WHERE symbol = ?", (symbol,))
        self.conn.commit()
        
    def close_connection(self):
        self.conn.close()

In this example, the StockDatabase class encapsulates all database operations related to a stock table in SQLite. This includes creating the table if it doesn’t exist, inserting new stocks, retrieving stocks by symbol, updating the quantity of a stock, and deleting a stock from the database.

By organizing these functions into a single class, you can easily manage and maintain your codebase as it grows over time. This approach also makes your code more readable and easier to understand for other developers who might work on your project in the future.

SQLite Advantages

  1. Lightweight: SQLite databases are stored as a single file on disk, making them easy to distribute and back up. They don’t require a separate server process or configuration, reducing the complexity of your application.
  2. Easy to use: SQLite is built into Python, so you can start using it right away without having to install any additional software. The API is straightforward and well documented, making it easy for beginners to learn.
  3. No server required: Unlike other database systems such as MySQL or PostgreSQL, SQLite doesn’t require a dedicated server process. This makes it ideal for small projects or applications where you don’t need the full power of a traditional database system.
  4. Cross-platform: SQLite is available on all major operating systems (Windows, MacOS, Linux), making it easy to develop and deploy across multiple platforms.
  5. No configuration required: Unlike other databases, SQLite doesn’t require any configuration or setup. You can start using it immediately with minimal effort.
  6. ACID compliance: SQLite supports transactions and concurrent access control, ensuring data integrity even in a multi-user environment.
  7. Broad support: SQLite is supported by many programming languages including Python, Java, C++, etc., making it a versatile choice for developers working with different technologies.

SQLite Disadvantages

  1. Limited concurrency: SQLite doesn’t support high concurrent write loads. If your application requires heavy write operations or multiple users accessing the database simultaneously, you may need to consider using a more robust database system such as MySQL or PostgreSQL.
  2. No built-in security: While SQLite supports encryption for data at rest and in transit, it doesn’t have any built-in security features such as user authentication, access control, etc. You would need to implement these yourself or use additional tools/services.
  3. Limited scalability: If your application grows beyond a certain size, you may face limitations with SQLite. For example, the maximum database size is 2TB and the maximum row size is 1GB.
  4. No online backup service: Unlike other cloud-based databases such as Google Cloud SQL or Amazon RDS, SQLite doesn’t have an online backup service. You would have to manage backups manually or use third party tools.
  5. Limited query capabilities: While SQLite supports a wide range of data types and query operations, it may not support all the features available in other databases, such as full-text search, spatial data types, etc.
  6. No support for advanced features: If you need advanced features such as replication, partitioning or stored procedures, SQLite may not be the best choice. You would need to use a more robust database system that supports these features.

Conclusion

In summary, SQLite is a powerful tool for managing databases in Python. With just a few lines of code, you can create tables, insert data, query data, update data and drop data. This makes it an excellent choice for small projects or for quickly prototyping ideas.

To top