SQLite DB & Python.
Below are several use cases showing you how to work with SQLite, an extremely light-weight Relational database in Python.
SQLite is widely-used, for example as supporting database for relatively small web sites, and is favorite among the developers for many reasons.
Python Knowledge Base: Make coding great again.
- Updated:
2024-07-26 by Andrey BRATUS, Senior Data Analyst.
Selecting data fron SQLite database:
Saving query results to excel file:
Saving query results to PDF file:
Inserting new rows into database table:
- Easy setup.
- Light-weighted, less than 500 KBs.
- Serverless.
- Fully transactional and concurrency-compliant.
- and many more...
Installing and setting up SQLite takes a few minutes, possible from the command line tools, but is preferred via GUI-based utilities like DB Browser for SQLite.
import sqlite3
# Establish a connection and create cursor
con = sqlite3.connect('database.db')
cur = con.cursor()
# Get all rows and all columns by order
cur.execute("SELECT * FROM 'ips' ORDER BY asn")
print(cur.fetchall())
# Get all rows and certain columns
cur.execute("SELECT address, asn FROM 'ips' ORDER BY asn")
print(cur.fetchall())
# Get all rows where asn is less than 300
cur.execute("SELECT * FROM 'ips' WHERE asn < 300")
print(cur.fetchall())
# Get all rows where asn is 144
cur.execute("SELECT * FROM 'ips' WHERE asn = 144")
print(cur.fetchall())
# Get all rows where asn is 144
cur.execute("SELECT * FROM 'ips' WHERE asn < 300 AND domain LIKE '%sa'")
results1 = cur.fetchall()
print(results1)
for row in results1:
print(row)
OUT: your select results.
import sqlite3
import pandas
# Establish a connection and create cursor
con = sqlite3.connect('mydatabase.db')
cur = con.cursor()
df = pandas.read_sql_query("SELECT * FROM 'ips' ORDER BY asn", con)
# print(df)
# df.to_csv('mydatabase.csv', index=None)
df.to_excel('mydatabase.xlsx', index=None)
print("select is saved to excel !!!")
OUT: select is saved to excel !!!
import sqlite3
from fpdf import FPDF
con = sqlite3.connect('database.db')
cur = con.cursor()
cur.execute('PRAGMA table_info(ips)')
columns = cur.fetchall()
#print(columns)
pdf = FPDF(orientation='P', unit='pt', format='A4')
pdf.add_page()
for column in columns:
pdf.set_font(family='Times', style='B', size=14)
pdf.cell(w=120, h=30, txt=column[1], border=1)
pdf.ln()
cur.execute("SELECT * FROM 'ips'")
rows = cur.fetchall()
for row in rows:
for element in row:
pdf.set_font(family='Arial', size=14)
pdf.cell(w=120, h=30, txt=str(element), border=1)
pdf.ln()
pdf.output('myoutput.pdf')
print("Your select is saved to PDF file !!!")
OUT: Your select is saved to PDF file !!!
import sqlite3
con = sqlite3.connect('database.db')
cur = con.cursor()
new_rows = [
('100.200.300.400', 'bratus.b.c', 110),
('200.200.200.200', 'andrey.d.e', 220)
]
cur.executemany("INSERT INTO 'ips' VALUES(?,?,?)", new_rows)
con.commit()
cur.execute("SELECT * FROM 'ips'")
print(cur.fetchall())
OUT: [('300.431.443.201', 'bra.an.br', 125), ('100.200.300.400', 'bratus.b.c', 110), ('200.200.200.200', 'andrey.d.e', 220)]