Motivation

Found myself trying to compare the differences between two SQLite databases but since I was too lazy to figure out how to use SQLite Studio in order to do that (not sure if that is even possible by the way), I decided to extract all the rows from both databases using Python and then use Bash to find their differences.

The solution

  • Usage of the sqlite3 module to connect to the SQLite database, run a select SQL query to get all the rows, and then fetch all of them.
  • Usage of the argparse module to enable the user to specify the database, table and output file from the command line.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import sqlite3
import argparse

# set up argparse arguments
# need arguments for:
# 1. path to database
# 2. path to output file
# 3. table from which rows will be selected
parser = argparse.ArgumentParser(description="To read all the rows of a SQLite table into a specified output file")
parser.add_argument('-d', '--database', type=str, metavar='', help='Path to SQLite table', required=True)
parser.add_argument('-o', '--output', type=str, metavar='', help='Path to output file', required=True)
parser.add_argument('-t', '--table', type=str, metavar='', help='Table from which rows will be selected', required=True)

# grabs all the args
args = parser.parse_args()

# "w" option creates the file if doesn't exist
# will overwrite file if already exists
# meaning of "w" from official doc: open for writing, truncating the file first
output_file = open(args.output, "w")

def readSqliteTable():
	try:
		conn = sqlite3.connect(args.database)
		c = conn.cursor()
		print("Connected to SQLite table")

		select_query = f"""SELECT * FROM {args.table}"""
		c.execute(select_query)
		records = c.fetchall()
		print(f"Total rows in '{args.table}':", len(records))
		print(f"""Now printing each row from the '{args.table}' in '{args.database}'...""")
		for i in records:
			print(i, file = output_file)

		# closing cursor is not necessary from reading (maybe look into this again)

	except sqlite3.Error as message:
		print("Failed to read data from SQLite database,", message)
	finally:
		if (conn):
			conn.close()
			print("SQLite connection is closed")


if __name__ == '__main__':
	readSqliteTable()

References

Python Select from SQLite Table