Learn how to use the SQLite database with Python in this tutorial. You will get to know how to setup a database, insert and request data with Python.
In a database there are the standard CRUD operations: cread, read, update and delete. You’ll learn all of them.
To start this tutorial you need to have some Python coding experience. You’ll do well even as a beginner.
Before starting, install the prequisities first.
You may like:
Learning SQL: Master SQL Fundamentals
You need these:
- Python 3 installed.
- SQLite database (sqlite3)
- the Python module pysqlite.
Test if it’s installed with these commands:
The program below creates an SQLite database and table. SQLite stores an entire database in a file (products.db)
An SQL database system can have tables, each table can contain data.
The program creates two tables: fruits and drinks.
Each table has columns (id, name, price).
Insert records with the INSERT INTO query. When adding data you need to write the table to insert and its column values.
You can see the data has been inserted via the terminal:
Every record has a unique identifier (id). This increases automatically. If you look at the image you’ll see Coffee has id 1, Juice id 2 etcetera.
This is achieved wit the AUTOINCREMENT flag we added when creating the table.
You can read data from an SQLite database with the SELECT query.
The example below reads the entire table drinks from the database and converts that to a Python list.
The Python list structure can be used as a regular list.
This will output the table formatted:
Coffee costs $3.0
Juice costs $2.1
Milk costs $4.5
To update data in your SQLite database, the UPDATE QUERY is required.
Remember those unique identifiers (id) ?
You can update records in your database with the unique id.
The steps are as simple as:
- define record id and column value to update
- connect to database
- run update query
Delete from a database with the DELETE QUERY.
Use the WHERE clause to set the record id to delete.
The example below deletes one record from the database table drinks (pId=3).