1. About SQLite Databases

SQLite is a library that implements a lightweight and embedded relational database management system. It allows storing data in a relational database using the SQL language. SQLite is often used in mobile applications and embedded applications due to its small size and low memory consumption.

SQLite uses a single file to store all the database data. This means there's no need for a separate database server to manage the database, making it easy to use in resource-constrained environments.

SQLite supports the following data types: NULL, INTEGER, REAL, TEXT, BLOB. It also supports referential integrity constraints such as foreign keys and UNIQUE constraints. It also provides support for transactions, views, triggers, and prepared statements.

2. Advantages and Disadvantages of SQLite

2.1 Advantages of SQLite

Here are some advantages of using SQLite:

1. Small Size: SQLite is a lightweight library that requires no additional installation or configuration. It's easy to integrate into mobile or embedded applications due to its small size.
2. Ease of Use: SQLite uses standard SQL language, making it easy for experienced SQL developers to learn.
3. Platform Support: SQLite is available for many operating systems and development platforms, including Windows, MacOS, iOS, Android, and Linux.
4. Low Memory Consumption: SQLite uses very little memory, making it ideal for embedded applications or mobile apps.
5. Single File: SQLite stores all data in a single file, making it easy to backup, sync, and migrate data.
6. Security: SQLite supports encryption mechanisms to protect sensitive data.

2.2 Disadvantages of SQLite

Here are some potential drawbacks of using SQLite:

1. Performance Limitations: SQLite may not be suitable for applications requiring high performance in terms of queries per second or database size.
2. Scalability Limitations: SQLite is primarily designed for single-scale applications; it may not be suitable for distributed applications or those requiring simultaneous access from multiple users or processes.
3. No Support for Users and Privileges: SQLite does not support user and privilege management.
4. No Replication Support: SQLite lacks data replication support, making it difficult to set up backup and data recovery.
5. No Support for Geospatial Queries: SQLite does not support geospatial queries.

3. SQLite Usage Areas

SQLite is used in a wide variety of applications and projects because it is easy to use, reliable and lightweight. It is particularly useful for applications that require local data storage and offline use.
Basically, SQLite is used by a large number of entities, including:

1. Mobile App Developers: SQLite is often used to store local data on mobile devices because of its lightweight nature and easy integration into applications.
2. App Publishers: Many app publishers use SQLite to store their application data because of its user-friendly interface, reliability.
3. Embedded App Developers: Embedded systems such as GPS receivers, surveillance cameras, smart thermostats, and IoT devices often utilize SQLite for data storage due to its small size and ease of use.
4. Game Developers: SQLite is used to store game data such as player scores, game settings, etc.
5. Software Developers: Many software applications use SQLite to store configuration data, registration information, debugging information, and performance data.
6. Web Developers: SQLite is used to store website data such as user login information, order information, product information, etc.

4. Creating Databases with Python & SQLite

4.1 Creating a SQLite Database with Python

It's possible to create and manage SQLite databases directly by using the Python module sqlite3. This module provides a Python interface for working with SQLite, allowing you to create and manipulate databases, tables, entries, and queries. The sqlite3 module requires no installation as it comes with the standard Python version. To create a SQLite database, you need to:

1. Import the sqlite3 module: using the command import sqlite3
2. Create a connection to the database: using the connect("database_name") method (the database will be created automatically).

import sqlite3

# Connecting to the database
conn = sqlite3.connect('mydatabase.db')

# Closing the connection
conn.close()

Remark

In this case, a SQLite database called mabase.db is created automatically.

4.2 Deleting a SQLite Database

To delete a SQLite database, it's straightforward:

 

1. Import the os module
2. Use the os.remove() method

 

import os

# Database to be deleted
database = 'mabase.db'

# Delete the database
os.remove(database)

5. Creation and Management of SQLite Tables

5.1 Creating a SQLite Table

To create a SQLite table with Python, follow these steps:

1. Import the sqlite3 module: using the command import sqlite3
2. Create a connection to the database: using the connect("database_name") method (if the database does not exist, it will be created automatically).
3. Create a cursor object: using the cursor() method.
4. Create an SQL query: (e.g., creating a table, inserting data...)
5. Execute the query: using the execute() method of the cursor.
6. Validate the query execution: using the commit() method of the connection object.
7. Close the connection: using the close() method.

Here's an example code to create a database "ma_base.db" and a table "students" with fields: "id", "name", "email", and "section"

import sqlite3

# Connect to the database
conn = sqlite3.connect('my_database.db')

# Create a cursor
cursor = conn.cursor()

# SQL query to create a table students
query = """CREATE TABLE IF NOT EXISTS students(
id INTEGER PRIMARY KEY AUTOINCREMENT, 
name TEXT NOT NULL, 
email TEXT NOT NULL,
section TEXT NOT NULL)"""

# Execute the query
cursor.execute(query)

# Validate query execution
conn.commit()

# Close the connection
conn.close()

This code creates a database called "my_database" with a table called "students" containing attributes:

1. id: an identifier used as a primary key that auto-increments.
2. name: to store the student's name.
3. email: to store the student's email address.
4. section: to store the student's section, such as math, physics, computer science...

Once the database and tables are created, you can use SQL commands to insert, update, and select data in the tables.

Note

The sqlite3 module is not secure against SQL injections, so it's important to ensure that the data used in queries is properly sanitized and escaped to avoid security risks.

5.2 Listing Tables in a SQLite Database

To list tables in a SQLite database from Python, you can use the built-in sqlite3 library.

Example

import sqlite3

# Open connection to the database
conn = sqlite3.connect('database_name.db')

# Get a cursor to execute SQL queries
cur = conn.cursor()

# Execute a query to get the list of tables
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")

# Retrieve the query results
tables = cur.fetchall()

# Display table names
for table in tables:
    print(table[0])

# Close cursor and connection
cur.close()
conn.close()

Note

Replace 'database_name.db' with the name of your own SQLite database.

5.3 Deleting a SQLite Table

To delete a SQLite table with Python, you can use the execute() method from the SQLite3 module to execute a SQL DROP TABLE statement.

Example

import sqlite3

# Connect to the database
conn = sqlite3.connect('my_database.db')

# Create a cursor object
cur = conn.cursor()

# Delete the table
cur.execute('''DROP TABLE my_table''')

# Commit the transaction
conn.commit()

# Close the connection
conn.close()

6. SQL Commands (SELECT, INSERT, UPDATE, DELETE)

6.1 The INSERT Command

Inserting data in SQLite3 environment is exactly the same as in MySQL. Before proceeding, we need to have at least one table within a SQLite database! Let's create one called 'students':

import sqlite3
conn = sqlite3.connect('my_database.db')
# Create a cursor
cur = conn.cursor()
# Create the query
query = """CREATE TABLE IF NOT EXISTS students(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    section TEXT NOT NULL)"""
# Execute the query
cur.execute(query)
# Commit the transaction
conn.commit()
# Close the connection
conn.close()

Now we can start inserting data into the 'students' table.

Syntax for inserting a data row:

cursor.execute(
"""
INSERT INTO students(name, email, section)
VALUES
('Albert', 'albert@gmail.com', 'math')"""
)

Note:

Note

The 'ID' identifier (primary key) is typically omitted! If omitted, it auto-increments as it has the AUTOINCREMENT property!

Complete code:

import sqlite3
conn = sqlite3.connect('my_database.db')
cur = conn.cursor()
# Insert a data row
query = """
INSERT INTO students(name, email, section)
VALUES
('Albert', 'albert@gmail.com', 'math')
"""
cur.execute(query)
# Commit the operation
conn.commit()
# Close the connection
conn.close()

6.2 Inserting Variable Data into a SQLite Table

When we directly inserted data into the table using the INSERT query as done in the example above, no issues were encountered!

cursor.execute("INSERT INTO students(name,email) VALUES ('Albert', 'albert@gmail.com')")

Now imagine that the data we want to insert consists of values from variables retrieved from another file or from a registration form... In this case, inserting the data this way would be completely incorrect!

name = "Najat"
email = "najat@gmail.com"
section = "SVT"

query = """INSERT INTO students
(name, email, section)
VALUES
(name, email, section)"""
cur.execute(query)
# TOTALLY WRONG!

WARNING! TOTALLY WRONG! Because the variables name and email will not be interpreted!

To correct the error, we use string formatting with the '?' symbol:

name = "Najat"
email = "najat@gmail.com"
section = "SVT"

cur = conn.cursor()

query = """INSERT INTO students
(name, email, section)
VALUES
(?, ?, ?)"""
cur.execute(query, (name, email, section))

Complete code:

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

name = 'Farid'
email = 'farid@gmail.com'
section = 'Physics'

cur = conn.cursor()

query = """ INSERT INTO students (name, email, section)
VALUES (?, ?, ?)"""

cur.execute(query, (name, email, section))
conn.commit()
conn.close()

6.3 The SELECT Command & Displaying Data

Now, let's see how to select data and display it, assuming everything has been set up correctly: creating the 'students' table within the SQLite3 database, inserting data into the 'students' table...

6.3.1 Creating a Cursor to Execute a Select Query

cur = conn.cursor()
result = cur.execute("SELECT * FROM students")

6.3.2 Iterating Through the Selection Results

To display the data, we'll iterate through the cursor object using a row counter. The variable row that iterates through is a tuple object whose components are the values of the fields: id, name, email, section...

for row in result:
    print("ID : ", row[0])
    print("Name : ", row[1])
    print("Email : ", row[2])
    print("Section : ", row[3])

Complete code:

import sqlite3

conn = sqlite3.connect('my_database.db')
cur = conn.cursor()
result = cur.execute("SELECT * FROM students")

for row in result:
    print("ID : ", row[0])
    print("Name : ", row[1])
    print("Email : ", row[2])
    print("Section : ", row[3])
    print('----------------------------------------')

6.3.3 Conditional Display

Usually, we want to display a part of an SQLite table or a specific display...
Here's an example showing how to select and display records with an identifier between 1 and 3.

import sqlite3

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

cur = conn.cursor()

req = "SELECT*FROM students where (id) <= (?) and (id) >= (?)"
result = cur.execute(req , (3,1, ))

for row in result:
    print(row)

This code displays the records whose ID satisfies: 1 <= id <= 3.

6.4 Updating Data Using the UPDATE Command

Data in a SQLite table can be easily updated using the UPDATE command with the syntax:

"UPDATE table_name SET (column1, column2, ...) = (?, ?, ...) WHERE 'condition'"

Example

import sqlite3

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

cur = conn.cursor()
name = "Najwa"
email = "najwa@gmail.com"
section = "History Geo"
query = "UPDATE students SET (name, email, section) = (?, ?, ?) WHERE (id) = (?)"
result = cur.execute(query, (name, email, section, 4,))
conn.commit()

conn.close()

This code updates the record where the ID is 4.

6.5 Deleting Data Using the DELETE Command

To delete a specific record from a SQLite table, we use the DELETE command with the syntax:

"DELETE FROM table_name WHERE 'condition'"

Example

import sqlite3

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

cur = conn.cursor()

# Query to delete the record with ID = 4
query = "DELETE FROM students WHERE (id) = (?)"
result = cur.execute(query, (4,))
conn.commit()

conn.close()

This code deletes the record with ID = 4.

7. WYSIWYG Editor For SQLite3

Everything has been done in the dark! So far, you haven't seen anything yet, no tables or data... To address this issue, I inform you that there are many utilities for managing and exploring SQLite3 databases. I recommend DB Browser for SQLite, which is free and very easy to use:

1. Download DB Browser for SQLite, and install it,
2. Launch DB Browser,
3. From the File menu, click on the Open Database submenu,
4. Then select your database mabase.db,
5. Finally, click on Browse Data to see your students table with the data you just inserted.

8. Resources and References for Learning SQLite

There are many other tutorials, books, and tools available for learning and using SQLite. These references are provided for informational purposes. Here are some resources and references for learning and using SQLite:

1. Official SQLite website: https://www.sqlite.org/index.html
2. Official SQLite documentation: https://www.sqlite.org/docs.html
3. DB Browser for SQLite: a free SQLite database management tool: https://sqlitebrowser.org/
4. SQLite Studio: a SQLite database management tool: https://sqlitestudio.pl/
5. SQLite Expert: another SQLite database management tool: https://www.sqliteexpert.com/

 

Younes Derfoufi
CRMEF OUJDA