📁 last Posts
0

How to Use SQLite with Python [Beginners Tutorial]

Mostafa Amaan Mostafa Amaan

How to Use SQLite with Python [Beginners Tutorial]
SQLite is a software library that provides a self-contained, serverless, and zero-configuration relational database management system. SQLite is widely used for data storage and manipulation in various applications, such as web browsers, mobile devices, embedded systems, and scientific software. SQLite has several advantages over other database systems, such as:

  • Portability: SQLite stores the entire database as a single file, which can be easily copied, moved, or shared across different platforms and devices.
  • Reliability: SQLite ensures that the database is always in a consistent state, even in the case of power failures, crashes, or errors. SQLite also supports transactions, which allow multiple SQL commands to be executed as a single unit of work.
  • Simplicity: SQLite does not require any installation, configuration, or administration. SQLite also has a simple and intuitive SQL syntax, which makes it easy to create and manipulate data.

Python is a popular and powerful programming language that offers a rich set of features and libraries for data analysis and visualization. Python can be easily integrated with SQLite, using the `sqlite3` module, which provides an interface to interact with SQLite databases from Python scripts. By using SQLite with Python, one can perform various tasks, such as:

  • Create and manage database tables, which store data in rows and columns.
  • Write to and read from the database, using SQL commands, such as `INSERT`, `SELECT`, `UPDATE`, and `DELETE`.
  • Query and filter the data, using SQL clauses, such as `WHERE`, `ORDER BY`, `LIMIT`, and `GROUP BY`.
  • Perform complex calculations and operations on the data, using Python functions and methods, such as `sum`, `max`, `min`, `count`, `sort`, and `join`.

In this article, we will show how to use SQLite with Python for data storage and manipulation, using examples and code snippets.

How to Setup SQLite

SQLite is a software library that does not require any installation or configuration. However, to use SQLite with Python, one needs to have the following components:

  • The SQLite binary, which is a single executable file that contains the core functionality of SQLite.
  • The `sqlite3` command-line tool, which is a program that allows creating and manipulating SQLite databases from the terminal.
  • The `sqlite3` module for Python, which is a package that provides an interface to interact with SQLite databases from Python scripts.

The steps to install these components vary depending on the operating system. In this section, we will describe the steps for Windows, Linux, and Mac OS.

Windows

To install SQLite on Windows, one can download the precompiled binary and the command-line tool from the official SQLite website. The files are named `sqlite-tools-win64-*.zip` and `sqlite-dll-win32-x86-*.zip`, where `*` is the version number.

One can extract these files to any folder, such as `C:\sqlite`, and add this folder to the system `PATH` variable, so that the `sqlite3` command can be accessed from any terminal.

To install the `sqlite3` module for Python on Windows, one can use the `pip` command, which is a tool that manages Python packages.

One can open a terminal and type the following command:

Python:  
pip install pysqlite3

This will download and install the `pysqlite3` package, which is a fork of the original `sqlite3` module that supports the latest SQLite features.

Linux

To install SQLite on Linux, one can use the package manager of the Linux distribution, such as `apt` for Ubuntu or `yum` for Fedora. 

One can open a terminal and type the following command:

Python:  
sudo apt install sqlite3

or

Python:  
sudo yum install sqlite

This will download and install the SQLite binary and the command-line tool.

To install the `sqlite3` module for Python on Linux, one can use the `pip` command, which is a tool that manages Python packages.

One can open a terminal and type the following command:

Python:  

pip install pysqlite3

This will download and install the `pysqlite3` package, which is a fork of the original `sqlite3` module that supports the latest SQLite features.

Mac OS

To install SQLite on Mac OS, one can use the `brew` command, which is a package manager for Mac OS.

One can open a terminal and type the following command:

Python:  

brew install sqlite

This will download and install the SQLite binary and the command-line tool.

To install the `sqlite3` module for Python on Mac OS, one can use the `pip` command, which is a tool that manages Python packages.

One can open a terminal and type the following command:

Python:  

pip install pysqlite3

This will download and install the `pysqlite3` package, which is a fork of the original `sqlite3` module that supports the latest SQLite features.

To verify that SQLite is installed and working properly, one can use the `sqlite3` command-line tool. One can open a terminal and type the following command:

Python:  

sqlite3

This will launch the SQLite shell, which is a program that allows executing SQL commands and queries on SQLite databases. One can see the SQLite version and some helpful information by typing the following command in the SQLite shell:

Python:  

.help

One can exit the SQLite shell by typing the following command:

Python:  
.exit

To verify that the `sqlite3` module for Python is installed and working properly, one can use the Python interactive shell. One can open a terminal and type the following command:

Python:  
python

This will launch the Python interactive shell, which is a program that allows executing Python commands and expressions. One can import the `sqlite3` module and see its version by typing the following commands in the Python shell:

Python:  
import sqlite3
sqlite3.version

One can exit the Python shell by typing the following command:

Python:  
exit()

How to Create a Database Table

A database table is a collection of data organized in rows and columns. Each row represents a record or an entity, and each column represents an attribute or a property of the entity. For example, a table called `students` can store the information of students, such as their id, name, age, and grade.

To create a database table in SQLite, one can use the `CREATE TABLE` statement, which specifies the name, columns, and data types of the table.

The syntax of the `CREATE TABLE` statement is as follows:

Python:  

CREATE TABLE table_name ( column_1 data_type_1, column_2 data_type_2, ... column_n data_type_n );

The `table_name` is the name of the table to be created, and the `column_1` and `data_type_1` are the names and data types of the columns, respectively. SQLite supports various data types, such as `INTEGER`, `TEXT`, `REAL`, `BLOB`, and `NULL`.

For example, to create a table called `students` with columns `id`, `name`, `age`, and `grade`, one can use the following statement:

Python:  

CREATE TABLE students ( id INTEGER, name TEXT, age INTEGER, grade REAL );

This will create a table called `students` with four columns: `id` of type `INTEGER`, `name` of type `TEXT`, `age` of type `INTEGER`, and `grade` of type `REAL`.

To view the details of a table, such as the names, data types, and constraints of the columns, one can use the `PRAGMA table_info` command, which takes the name of the table as an argument. The syntax of the `PRAGMA table_info` command is as follows:

Python:  
PRAGMA table_info(table_name);

The `table_name` is the name of the table to be inspected. The `PRAGMA table_info` command returns a result set with six columns: `cid`, `name`, `type`, `notnull`, `dflt_value`, and `pk`.

The `cid` is the column id, the `name` is the column name, the `type` is the data type, the `notnull` is a flag indicating whether the column has a `NOT NULL` constraint, the `dflt_value` is the default value of the column, and the `pk` is a flag indicating whether the column is part of the primary key.

For example, to view the details of the `students` table, one can use the following command:

Python:  

PRAGMA table_info(students);

This will return a result set with the following rows:

cidnametypenotnulldflt_valuepk
0idINTEGER0NULL0
1nameTEXT0NULL0
2ageINTEGER0NULL0
3gradeREAL0NULL0

This shows that the `students` table has four columns: `id`, `name`, `age`, and `grade`, with their respective data types and constraints. None of the columns have a `NOT NULL` or a primary key constraint, and none of them have a default value.

How to Write to the Database

To write data to a SQLite database from a Python script, one needs to establish a database connection, which is a link between the Python script and the SQLite database file.

A database connection allows creating and executing SQL commands on the database, as well as committing and rolling back transactions.

To create a database connection in Python, one can use the `sqlite3.connect` function, which takes the name of the database file as an argument. If the file does not exist, it will be created automatically. The syntax of the `sqlite3.connect` function is as follows:

Python:  

conn = sqlite3.connect(database_file)

The `conn` is a variable that holds the database connection object, and the `database_file` is the name of the database file, such as `students.db`.

To execute SQL commands on the database, one needs to create a cursor, which is an object that allows executing SQL commands and fetching data from the database. A cursor can be created from a database connection using the `cursor` method. The syntax of the `cursor` method is as follows:

Python:  

cur = conn.cursor()

The `cur` is a variable that holds the cursor object, and the `conn` is the database connection object.

A transaction is a group of SQL commands that are executed as a single unit of work. A transaction ensures that the database is in a consistent state, even if some of the commands fail or are interrupted. A transaction can be committed or rolled back, which means saving or discarding the changes made by the commands.

To execute SQL commands using a cursor, one can use the `execute` method, which takes a SQL statement as an argument. The syntax of the `execute` method is as follows:

Python:  

cur.execute(sql_statement)

The `cur` is the cursor object, and the `sql_statement` is the SQL statement to be executed, such as `INSERT`, `UPDATE`, or `DELETE`.

To commit a transaction using a database connection, one can use the `commit` method, which saves the changes made by the SQL commands to the database file. The syntax of the `commit` method is as follows:

Python:  

conn.commit()

The `conn` is the database connection object.

For example, to insert a row into the `students` table, which has columns `id`, `name`, `age`, and `grade`, one can use the following code:

Python:  

# create a database connection conn = sqlite3.connect("students.db") # create a cursor cur = conn.cursor() # insert a row into the students table cur.execute("INSERT INTO students (name, age, grade) VALUES ('Alice', 18, 3.5)") # commit the transaction conn.commit() # get the id of the last inserted row last_id = cur.lastrowid # print the id print(last_id)

This code will create a database connection to the `students.db` file, create a cursor, insert a row into the `students` table with the values `Alice`, `18`, and `3.5` for the columns `name`, `age`, and `grade`, respectively, commit the transaction, and get the id of the last inserted row using the `lastrowid` attribute of the cursor.

The `lastrowid` attribute returns the id of the last row that was modified by the cursor, which can be useful for tracking the data. The code will print the id of the last inserted row, which is `1` in this case.

How to Read from the Database

A query is a SQL command that retrieves data from the database. A query can specify which columns and rows of a table to select, as well as apply filters, sorting, and aggregation functions to the data.

To execute a query in Python, we need to use the `execute` method of the cursor object, which is created from a database connection. The `execute` method takes a SQL statement as an argument and sends it to the database for execution.

The result of a query is a result set, which is a collection of rows that match the query criteria. The result set is stored in the cursor object and can be accessed by various methods.

To fetch data from the result set, we can use the `fetchone`, `fetchmany`, or `fetchall` methods of the cursor object. The `fetchone` method returns a single row as a tuple, the `fetchmany` method returns a list of rows as tuples, and the `fetchall` method returns all rows as a list of tuples.

If there are no more rows to fetch, these methods return `None`, an empty list, or an empty list, respectively.

For example, to query the `students` table and select the name and age of all students, we can use the following code:

Python:  

import sqlite3 conn = sqlite3.connect('students.db') # create a database connection cur = conn.cursor() # create a cursor object cur.execute('SELECT name, age FROM students') # execute a query data = cur.fetchall() # fetch all rows from the result set print(data) # print the data conn.close() # close the database connection

To filter the data based on certain criteria, we can use the `WHERE` clause in the query. The `WHERE` clause allows us to specify conditions for the rows to be selected, using operators such as `=`, `<`, `>`, `LIKE`, `IN`, `AND`, `OR`, and `NOT`.

For example, to query the `students` table and select the name and age of students who are older than 18 and have a name that starts with 'A', we can use the following code:

Python:  

import sqlite3 conn = sqlite3.connect('students.db') cur = conn.cursor() cur.execute("SELECT name, age FROM students WHERE age > 18 AND name LIKE 'A%'") data = cur.fetchall() print(data) conn.close()

To sort the data based on one or more columns, we can use the `ORDER BY` clause in the query. The `ORDER BY` clause allows us to specify the columns and the order (ascending or descending) for the data to be sorted. The default order is ascending, but we can use the `DESC` keyword to specify descending order.

For example, to query the `students` table and select the name and age of all students, sorted by age in descending order and then by name in ascending order, we can use the following code:

Python:  

import sqlite3 conn = sqlite3.connect('students.db') cur = conn.cursor() cur.execute('SELECT name, age FROM students ORDER BY age DESC, name ASC') data = cur.fetchall() print(data) conn.close()

To limit the number of rows returned by the query, we can use the `LIMIT` clause in the query. The `LIMIT` clause allows us to specify the maximum number of rows to be fetched from the result set.

For example, to query the `students` table and select the name and age of the first 10 students, sorted by age in descending order, we can use the following code:

Python:  

import sqlite3 conn = sqlite3.connect('students.db') cur = conn.cursor() cur.execute('SELECT name, age FROM students ORDER BY age DESC LIMIT 10') data = cur.fetchall() print(data) conn.close()

How to Update a Row in the Database

An update is a SQL command that modifies the data in the database. An update can change the values of one or more columns of a table for a specific row or a set of rows that match certain criteria.

To execute an update in Python, we need to use the `execute` method of the cursor object, which is created from a database connection. The `execute` method takes a SQL statement as an argument and sends it to the database for execution.

To commit the update in Python, we need to use the `commit` method of the database connection object, which saves the changes made by the SQL commands to the database file.

For example, to update a row in the `students` table, which has columns `id`, `name`, `age`, and `grade`, we can use the following code:

Python:  

import sqlite3 conn = sqlite3.connect('students.db') # create a database connection cur = conn.cursor() # create a cursor object cur.execute("UPDATE students SET grade = 4.0 WHERE id = 1") # execute an update conn.commit() # commit the update conn.close() # close the database connection

This code will create a database connection to the `students.db` file, create a cursor, update the grade of the student with id 1 to 4.0, commit the update, and close the database connection.

To specify the new values for the columns and the condition for the row to be updated, we can use the `UPDATE` statement, which has the following syntax:

Python:  

UPDATE table_name SET column_1 = value_1, column_2 = value_2, ... column_n = value_n WHERE condition;

The `table_name` is the name of the table to be updated, the `column_1` and `value_1` are the names and new values of the columns to be updated, respectively, and the `condition` is the criteria for the row to be updated.

To assign new values to one or more columns of the table, we can use the `SET` clause in the `UPDATE` statement. The `SET` clause allows us to specify the columns and their new values, separated by commas.

To identify the row to be updated based on certain criteria, we can use the `WHERE` clause in the `UPDATE` statement. The `WHERE` clause allows us to specify conditions for the row to be updated, using operators such as `=`, `<`, `>`, `LIKE`, `IN`, `AND`, `OR`, and `NOT`.

How to Delete a Row in the Database

A deletion is a SQL command that removes data from the database. A deletion can delete a specific row or a set of rows that match certain criteria from a table.

To execute a deletion in Python, we need to use the `execute` method of the cursor object, which is created from a database connection. The `execute` method takes a SQL statement as an argument and sends it to the database for execution.

To commit the deletion in Python, we need to use the `commit` method of the database connection object, which saves the changes made by the SQL commands to the database file.

For example, to delete a row from the `students` table, which has columns `id`, `name`, `age`, and `grade`, we can use the following code:

Python:  

import sqlite3 conn = sqlite3.connect('students.db') # create a database connection cur = conn.cursor() # create a cursor object cur.execute("DELETE FROM students WHERE id = 1") # execute a deletion conn.commit() # commit the deletion conn.close() # close the database connection

This code will create a database connection to the `students.db` file, create a cursor, delete the row from the `students` table with id 1, commit the deletion, and close the database connection.

To specify the condition for the row to be deleted, we can use the `DELETE` statement, which has the following syntax:

Python:  

DELETE FROM table_name WHERE condition;

The `table_name` is the name of the table to be deleted from, and the `condition` is the criteria for the row to be deleted, using operators such as `=`, `<`, `>`, `LIKE`, `IN`, `AND`, `OR`, and `NOT`.

To identify the row to be deleted based on certain criteria, we can use the `WHERE` clause in the `DELETE` statement. The `WHERE` clause allows us to specify conditions for the row to be deleted, using operators such as `=`, `<`, `>`, `LIKE`, `IN`, `AND`, `OR`, and `NOT`.

Conclusion

SQLite and Python are powerful and versatile tools for data storage and manipulation. However, to use them effectively and efficiently, we need to follow some tips and best practices, such as:

  • Use parameterized queries to avoid SQL injection attacks and improve performance. Parameterized queries allow us to pass values to the SQL statements as placeholders, which are then replaced by the actual values at execution time. This prevents malicious or erroneous input from affecting the SQL syntax and logic. To use parameterized queries in Python, we can use the `?` symbol as a placeholder and pass a tuple of values as the second argument to the `execute` method.
  • Handle errors and exceptions gracefully. Errors and exceptions are inevitable when working with databases, and we need to handle them properly to avoid data loss or corruption. To handle errors and exceptions in Python, we can use the `try-except-finally` block, which allows us to execute some code, catch and handle any errors or exceptions that occur, and perform some cleanup actions regardless of the outcome.
  • Close the database connection properly. A database connection is a limited and valuable resource that should be released when not needed. To close the database connection in Python, we can use the `close` method of the database connection object. This will also close any cursor objects that are associated with the connection. Closing the database connection will ensure that the changes are saved to the database file and prevent any memory leaks or errors.

To learn more about SQLite and Python, we can refer to some further resources or references, such as:

- The official documentation of SQLite.

- The official documentation of the `sqlite3` module for Python.

- A tutorial on how to use SQLite with Python


Mostafa Amaan
Mostafa Amaan
Technical educational content creator on my blog and YouTube channel. My goal with this content is to eradicate information technology literacy.

You may like these posts

Comments
Post a Comment

إضافة تعليق