Step by Step Tutorial on Programming SQLite Database Using Python for Beginners

In this Instructable, you will learn to program and store data to an SQLite 3 database using Python Programming Language. Here we plan to teach the user how to connect with a SQLite Database and read/write data into it for persistent storage.

We assume that the reader has basic understanding of the Python Programming Language and Simple SQL Database commands. I will try to explain the SQL commands as we go by the instructable.

This SQLite Tutorial is Cross-platform and is applicable

1. on a Windows 10/11 System
2. or any Linux System
3. or macOS System

Here we will learn to create a connection to sqlite Database using Python Create tables, Add data, Update rows and finally delete data from the Db.

You will need the following things for going through this tutorial.

A Python Interpreter. You can download if from cPython .SQLite 3 Database comes with Python interpreter ,So no need to install it.

You can download DB Browser for SQLite to browse the created SQLite databases.

Source Codes can be downloaded from the GitHub Repo SQLite Tutorial using Python

SQLite is a lightweight relational database management system (RDBMS) used for storing data generated by your application software for persistent stoarge. Instead of storing data into to a text /binary file ,You can store the data into a SQLite Database which you can manipulate with SQL commands to get your desired data.

Lot of mainstream application like Firefox uses SQlite Database to store internal information like Browser History.

Traditional Database Systems

If you have ever used a Database like MySQL or PostgreSQL,

You will have a Server program (process) that runs as a Windows service (On windows Machine ) or as a daemon process in the case of Linux System called mysqld for MySQL.

The user will then connect to the server process using a command line called mysql using TCP/IP Protocol by giving a specific port number.

No Server Needed

Unlike traditional client-server database engines (like MySQL or PostgreSQL), SQLite is server-less and runs in-process with the application.

It reads and writes directly to a single ordinary disk file as shown below.

SQLite is an embedded database, meaning there is no need to install or configure a separate database server. It is compiled directly into the application and runs within the same process.

Being self-contained, the entire database,including the schema, tables, indexes, and data is stored in a single, cross-platform .sqlite or .db file.

It requires zero configuration, so applications can begin using it immediately without any setup or administrative overhead. SQLite follows the relational model, using standard SQL (with minor deviations) to define and manipulate data, supporting features such as tables, indexes, joins, transactions, and constraints. It is also ACID-compliant, fully supporting Atomicity, Consistency, Isolation, and Durability through transactional behavior, typically using a journal file or write-ahead logging (WAL) mode.

Furthermore, it is cross-platform, with a database file format that is portable between 32-bit and 64-bit systems and compatible across various operating systems.

First thing to do would be to import the sqlite3 module.It’s a built-in Python module, so you don't need to install anything extra.

You can create a new database using the following line of code.

Once you run this code ,

it will create a database file called mydatabase.db on your current directory. You can give any name or any file extension for your database.

Here conn variable is called as a connection object variable. A connection object in the context of SQLite represents an open connection between your application (in our case, your Python program) and the database. It is an instance of the sqlite3.Connection class in Python when you're using the sqlite3 module.

conn variable (connection object) establishes a communication link between your Python code and the SQLite database file.

Once you're done interacting with the database, you need to close the connection to release any resources using conn.close() method.

The above code will create a Sqlite database on your Disk but it will be completely empty.as you can see in the below image.

You can either populate the database using an external Database editor like DB Browser for SQLite

or you can populate your SQLite db with tables programmatically using Python.

SQLite provides five primary data types, also known as storage classes, that define how values are stored in a database. These data types ensure that the database can handle a wide range of information efficiently.

The five basic SQLite data types are:

1. NULL –> Represents a missing or undefined value. It is used when no data is available for a particular field.
2. INTEGER – >A signed whole number. Depending on the magnitude of the numbr, it can occupy 1, 2, 4, 6, or 8 bytes of storage.
3. REAL –> A floating-point number, stored as an 8-byte IEEE 754 value. It is used for numeric values with decimal points.
4. TEXT –> A text string, stored using Unicode encodings
5. BLOB –> Stands for Binary Large Object. It is used to store binary data such as images, multimedia, or any file content in its raw form.

For most everyday applications, you'll primarily work with the following three types:

1. TEXT – Used for storing strings, names, discriptions, and long pieces of textual information. Eg: 'Alice', 'New York', 'This is a sample note.'

1. INTEGER – Ideal for storing whole numbers such as IDs, counts, or flags.Eg: 1, 25, 1003

1. REAL – Used when you need to store numeric values with decimal points, such as prices, weights, or measurements. Eg: 3.14, 99.99, 0.0001
2. 
   

SQLite STRICT mode, introduced in version 3.37.0 is a feature that allows developers to create tables with much stricter type and integrity constraints than those enforced by default in SQLite.

Normally, SQLite uses dynamic typing, also known as manifest typing, which means that any value can be stored in any column, regardless of its declared type. STRICT tables override this behavior by requiring that values inserted into columns match the declared data type exactly.

For example, a column defined as TEXT will only accept actual text values, and an INTEGER column will reject floating-point or text values.

To create a STRICT table in SQLite, you use the STRICT keyword at the end of the CREATE TABLE statement.

A typical example might be:

In this table, any attempt to insert a non-text value into the username field, or a NULL value if it's declared as NOT NULL, would result in an error. This level of enforcement helps catch type-related bugs early and ensures data consistency.

However, it is important to note that STRICT tables are only available in SQLite version 3.37.0 and later, and not all features of SQLite are compatible with them, such as virtual tables.

Checking the version of SQLite Database

You can check the version of the sqlite database using the below code.

sqlite3.sqlite_version will give you the version of the sqlite supported by the sqlite3 module in Python

In relational databases like SQLite, tables are the primary structures for storing data. Each table contains multiple columns (which define the data type and label for each piece of data) and multiple rows (which hold the actual data entries).

To store and organize data efficiently, we need to define and create these tables in advance.

Now that the database file exists (datalogger), we can create a table within it. This is done by executing a SQL CREATE TABLE statement using a cursor object obtained from the connection.

Here’s SQL to create a table with multiple columns which store REAL,TEXT and INTEGER datatypes

Here we are using the SQLite STRICT option to male sure that declared datatypes match column names.

At the end of the SQL statement for creating the table , the keyword STRICT is used, which activates STRICT mode for this table. This means that each column must strictly accept only values that conform to its declared data type. For example, attempting to insert a string into one of the REAL temperature columns would result in an error.

The script then connects to an SQLite database file by calling sqlite3.connect(). If the file does not exist, SQLite will create it.

A cursor object is created using connection_object.cursor(), which is used to execute SQL commands. A cursor object acts as an intermediary between your Python code and the SQLite database. It is used to execute SQL commands, retrieve query results, and manage the context of the database operation.

The execute() method of the cursor object is called with the SQL query to create the STRICT table.

After executing the command, connection_object.commit() is called to save the changes to the database,

and finally, the connection is closed using connection_object.close().

On Running this code using the Python Interpreter ,A SQLite database with STRICT table is created on your disk which you can view using DB Browser or DBeaver Program.

CRUD, which stands for Create, Read, Update, and Delete, represents the four basic operations used in applications to interact with a database. These operations are the foundation of most web, desktop, and mobile applications that manage data.

1. Create -> refers to inserting new data into the database, such as adding a new user or product.
2. Read -> allows the application to retrieve and display existing data, like showing a list of blog posts or user profiles.
3. Update -> to modify existing records, such as changing a user's email address or editing a product's price.
4. Delete -> Removes unwanted or outdated data from the database, like deleting a post or removing an inactive user.

In real-world applications, CRUD operations appear everywhere. For Example, in an e-commerce website like amazon or flipkart, when a customer signs up, that action creates a new record in the users table. Viewing a list of products involves reading records from the database. When an administrator changes a product’s price, the update operation is used. If a product is discontinued, it can be removed using the delete operation.

In thi section we will INSERT data into our sqlite database using Python ,ie the Create part of our CRUD paradigm.

So we will use the Standard SQL to INSERT data into the table. Here is the Query for that.

Now you can run this query directly using cursor.execute() method and it will work.

But doing it exposes your database to SQL Injection attacks ,In which Rogue users could craft malicious SQL querys to execute inside your database causing data loss or corruption.

In our case when we are using Python to add data to the SQlite database ,we will be using some thing called parameterized queries.

Here Values are replaced with ? and the function adds the data after sanitizing it.

Now our parameterized SQL query will look like this.

Now you can use them in our code like this.

Here is the full code for it .

Here we will read a row from the SQLite database using Python language after executing a SQL query.

To read data from a sqlite table,Python's sqlite3 module provides three methods.They are

1. The fetchone() method is used to retrieve a single row from the result set of a SELECT query and returns it as a tuple. If there are no more rows to fetch, it returns None. This method is efficient for applications that work with large tables and only need one row at a time, helping to conserve memory by not loading the entire result set.
2. fetchall() retrieves all rows returned by the query and stores them in a list of tuples. While this is straightforward and convenient, it can lead to high memory usage if the result set is large, as it loads everything into memory at once
3. fetchmany(n) is used to fetch the next n rows from the result set. It returns a list of tuples, where each tuple corresponds to one row, and if fewer than n rows remain, it returns only the remaining rows. This method is particularly helpful when working with large datasets, as it enables processing data in smaller, more manageable chunks rather than retrieving everything at once.

Here we will be running the following SQL Query

Since our database is quite small, we can easily use the fetchall() method.

Here is the Code for reading a row from sqlite .

Updating a row in SQLite using Python involves modifying existing data in a specific table based on a condition using the UPDATE SQL statement.

Usually the query will look like.

So in our case it would look like

Now the partial code for updating the sqlite database using Python will look like this.

In the above code We will adding the new data based on the timestamp.

Before updating the table ,row will look like this.

Now you can see the temperature values has been updated inside the table( below)

To delete a row in SQLite using Python, you execute the DELETE FROM SQL command in combination with a WHERE clause that identifies the specific row or rows you want to remove from the table.

The DELETE FROM statement on its own will remove all rows from the specified table, so the WHERE clause is crucial when you intend to delete only certain entries rather than clearing the entire table.

The WHERE condition typically targets a unique value, such as an ID or a specific field, to ensure only the intended record is deleted. Without this condition, there is a risk of unintentionally erasing large amounts of data.

Here we will delete the row whose time stamp matches 1746599012..

Here is the partial code for deleting a row from the sqlite database using Python.