Python and SQLite: A Comprehensive Guide

SQLite is a software library that provides a relational database management system. The lite in SQLite means light weight in terms of setup, database administration, and required resource. SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite is open source and is a great choice for local/client storage in application software such as web browsers.

SQLite Basics

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger system such as PostgreSQL or Oracle.

SQLite Data Types

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

SQLite Commands: SELECT, INSERT, UPDATE, DELETE

SQLite commands are similar to SQL commands. The SELECT command is used to select data from a database. The data returned is stored in a result table, called the result-set. The INSERT command is used to insert new data into a database. The UPDATE command is used to modify the existing records in a table. The DELETE command is used to delete existing records in a table.

Python SQLite3 Module

Python’s sqlite3 module provides an API for SQLite database. It comes with Python distribution.

Connecting to SQLite Database

To use SQLite3 in Python, first of all, you will have to import the sqlite3 module and then create a connection object which will connect us to the database and will let us execute the SQL statements. A connection object is created using the connect() function:

import sqlite3
conn = sqlite3.connect('example.db')

Creating Tables

Once you have a connection, you can create a cursor object and call its execute() method to perform SQL commands:

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

Inserting, Updating, and Deleting Records

Inserting, updating, and deleting records is done using SQL commands:

c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
conn.commit()

Querying Data

To retrieve data after executing a SELECT statement, you can either treat the cursor as an iterator, call the cursor’s fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.

for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)

Error Handling

SQLite Exceptions

If an error occurs, the sqlite3 module raises an exception. All exceptions raised by sqlite3 are subclasses of a base class named Error.

Handling SQLite Exceptions in Python

You can handle SQLite exceptions in Python using try...except blocks. If an error occurs inside the try block, Python will raise an exception and execution will jump immediately to the except block.

try:
    c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
except sqlite3.IntegrityError:
    print("Record already exists.")

In conclusion, SQLite is a powerful library that offers full, relational database functionality in a no-server, zero-configuration package. Python's sqlite3 module makes it easy to use this database for a wide range of applications.