This Python SQLite tutorial aims to demonstrate how to develop Python database applications with the SQLite database. You will learn how to perform SQLite database operations from Python.
As you all know, SQLite is a C-language library that implements a SQL database engine that is relatively quick, serverless, and self-contained, high-reliable. SQLite is the most commonly used database engine in the test environment (Refer to SQLite Home page).
SQLite comes built-in with most computers and mobile devices, and browsers. Python’s official sqlite3 module helps us to work with the SQLite database.
Python sqlite3 module adheres to Python Database API Specification v2.0 (PEP 249). PEP 249 provides a SQL interface designed to encourage and maintain the similarity between the Python modules used to access databases.
Let see each section now.
Table of contents
- Python SQLite Database Connection
- Create SQLite table from Python
- SQLite Datatypes and it’s corresponding Python types
- Perform SQLite CRUD Operations from Python
- Execute SQL File (scripts) from Python using cursor’s executescript
- Insert/Retrieve digital data in SQLite using Python
- Create Or Redefine SQLite Functions using Python
- Working with SQLite date and timestamp types in Python and vice-versa
- SQLite Database Exceptions
- Change SQLite connection timeout when connecting from Python
- Identify total changes since the SQLite database connection is open
- Take a backup of SQLite database from Python
- Python SQLite Exercise Project
Python SQLite Database Connection
This section lets you know how to connect to SQLite database in Python using the sqlite3 module.
Use the following steps to connect to SQLite
How to Connect to SQLite Database in Python
- Import sqlite3 module
import sqlite3statement imports the sqlite3 module in the program. Using the classes and methods defined in the sqlite3 module we can communicate with the SQLite database. - Use the connect() method
Use the
connect()method of theconnectorclass with the database name. To establish a connection to SQLite, you need to pass the database name you want to connect. If you specify the database file name that already presents on the disk, it will connect to it. But if your specified SQLite database file doesn’t exist, SQLite creates a new database for you.
This method returns the SQLite Connection Object if the connection is successful. - Use the cursor() method
Use the
cursor()method of a connection class to create a cursor object to execute SQLite command/queries from Python. - Use the execute() method
The execute() methods run the SQL query and return the result.
- Extract result using fetchall()
Use
cursor.fetchall()orfetchone()orfetchmany()to read query result. - Close cursor and connection objects
use
cursor.clsoe()andconnection.clsoe()method to close the cursor and SQLite connections after your work completes - Catch database exception if any that may occur during this connection process.