SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - we can freely copy a database between 32-bit and 64-bit systems. These features make SQLite a popular choice as an Application File Format. It is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.
The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.
To use the module, need to create a Connection object that represents the database.
import os
import sqlite3
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
print('Database exists...')
else:
print('Database doesnot exists, creats a new database.')
conn.close()
We will create a schema called person, which will have id, firstname and description column.
Column Type
id INTEGER
firstname text
lastname text
age INTEGERquery = '''create table person (
id INTEGER primary key,
firstname text,
lastname text,
age INTEGER
)'''
Cursor Objects
class sqlite3.Cursor
A Cursor instance has the following attributes and methods.
execute(sql[, parameters])
Executes an SQL statement. Values may be bound to the statement using placeholders.
execute() will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript() if you want to execute multiple SQL statements with one call.
executemany(sql, seq_of_parameters)
Executes a parameterized SQL command against all parameter sequences or mappings found in the sequence seq_of_parameters. The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute(query)
conn.commit()
print("Person table created successfully...")
else:
print("Database doesnot exists, creats a new database.")
conn.close()
We will insert multiple records in person table. For that we will use executemany() function.
person_list = [
(1, "Martha", "Lucy", 45),
(2, "John", "Bronze", 70),
(3, "Noah", "Charlotte", 20),
(4, "Henry", "Sophia", 55),
(5, "John", "Sophia", 45)
]
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.executemany("insert into person values (?, ?, ?, ?)", person_list)
conn.commit()
print("Record inserted successfully...")
else:
print("Database doesnot exists, creats a new database.")
conn.close()
To retrieve data after executing a SELECT statement, either treat the cursor as an iterator, call the cursor’s fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from person")
records = cur.fetchall()
print("Fetching all records from person table")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from person order by firstname")
records = cur.fetchall()
print("Fetching all records from person table and order by firstname")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from person")
record = cur.fetchone()
print("Fetching only one record from person table")
print("\n", record)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from person where firstname=:firstname order by age", {"firstname": "John"})
records = cur.fetchall()
print("Fetching all records from person table, whose firstname is John and order by age")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
A question mark (?) denotes a positional argument, we have to pass to execute() function as a member of a tuple.
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from person where firstname = ? order by age", ("John",))
records = cur.fetchall()
print("Fetching all records from person table, whose firstname is John and order by age")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from person where firstname like '%j%'")
records = cur.fetchall()
print("Fetching all records from person table, whose firstname starts with J")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("update person set firstname = ?, lastname = ?, age = ? where id = ?", ('Maria', 'Lucy', 55, 1,))
conn.commit()
cur.execute("select * from person")
records = cur.fetchall()
print("Fetching all records from person table after update")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
We can see first record updated successfully.
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("delete from person where id = ?", (5,))
conn.commit()
cur.execute("select * from person")
records = cur.fetchall()
print("Fetching all records from person table after delete one record.")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()
executescript(sql_script)
This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. This method disregards isolation_level; any transaction control must be added to sql_script.
sql_script can be an instance of str.my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.executescript("""
create table book(
title,
author,
published
);
insert into book(title, author, published)
values (
'Dirk Gently''s Holistic Detective Agency',
'Douglas Adams',
1987
);
insert into book(title, author, published)
values (
'Joe Biden: American Dreamer',
'Evan Osnos',
2021
);
insert into book(title, author, published)
values (
'Artificial Intelligence and the Future of Power: 5 Battlegrounds',
'Rajiv Malhotra',
2021
);
""")
else:
print("Database doesnot exists, creats a new database.")
conn.close()
my_db = 'input/test.db'
db_exists = os.path.exists(my_db)
conn = sqlite3.connect(my_db)
if db_exists:
cur = conn.cursor()
cur.execute("select * from book")
records = cur.fetchall()
print("Fetching all records from book table")
print("\n", records)
else:
print("Database doesnot exists, creats a new database.")
conn.close()