Easy way to interact with SQLite DB with Python.

- 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.

Selecting data fron SQLite database:

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.

Saving query results to excel file:

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 !!!

Saving query results to PDF file:

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 !!!

Inserting new rows into database table:

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)]