This program arose to make things easy for me in the long run, a kind of constructive laziness (as discussed here). There are times when I am writing programs that use database files, and I forget what the table names are, or what the column names and types are, and SQLite needs these to be correctly spelt (and is case sensitive). I could open up sqlite3 on the Windows command line and try to remember how to view the metadata about the database, but that can be a bit of hassle, particularly when my mind is focused on Python code.
This program looks at that metadata and presents it in a relatively easy way.
import sys
import os
import webbrowser
import sqlite3
# Collecting list of database files
dirlist = os.listdir('C:\\sqlite\\db')
dbfilelist = []
filecount = 0
for file in dirlist:
if file[-3:] == '.db':
print(filecount, file, 'found')
dbfilelist.append(file)
filecount +=1
print(filecount, 'Completely different directory')
# Selecting which db file
filechoice = input("Enter number of file to view: ")
try:
fileno = int(filechoice)
chosenfile = dbfilelist[fileno]
print(chosenfile, 'selected')
dirpath = 'C:\\sqlite\\db\\'+chosenfile
except:
try:
if int(filechoice) == filecount:
dirpath = input("Please enter new directory path including filename: ")
# Check that new dirpath is valid file
fcheck = open(dirpath, 'r')
fcheck.close()
chosenfile = dirpath
except:
print("Sorry, cannot parse that.")
sys.exit()
# Starting up SQLite and selecting tables
conn = sqlite3.connect(dirpath)
curs = conn.cursor()
curs.execute('SELECT name FROM sqlite_master WHERE type="table";')
tabdump = curs.fetchall()
tabcount = 0
print("Current tables in", chosenfile)
if len(tabdump) == 0:
print("Database seems to be empty - quitting")
sys.exit()
for table in tabdump:
print(tabcount, table[0])
tabcount += 1
tabchoice = input("Select which table to inspect or Q to quit: ").lower()
if tabchoice == 'q':
print("Quitting - bye!")
sys.exit()
else:
tabname = tabdump[int(tabchoice)][0]
print(tabname, "table info")
curs.execute("PRAGMA table_info("+tabname+");")
pragdump = curs.fetchall()
collist = []
for line in pragdump:
print(line)
collist.append(line[1])
curs.execute("SELECT * FROM "+tabname+";")
contentsdump = curs.fetchall()
rows = len(contentsdump)
print(tabname, "contains ", rows, "records/rows")
# Creating HTML table
if rows > 0 and input("View table as HTML (Y)? ").lower() == 'y':
FH = open('temp.html', 'w')
FH.write(" \n")
FH.write("<HTML> <BODY>\n")
FH.write("<TABLE Border=1> <TR>")
for col in collist:
FH.write("<TD><B>"+col+"</B></TD>")
for line in contentsdump:
FH.write("<TR> \n")
for cell in line:
FH.write("<TD>"+str(cell)+"</TD>")
FH.write("</TR> \n")
FH.write("</TABLE> \n")
FH.write("</BODY> </HTML> \n")
FH.close()
webbrowser.open('temp.html')
conn.close()
This script works well and makes relatively few assumptions. As a matter of good habit, I create and keep database files in 'C:\SQLite\db' folder in Windows, and I always give them a '.db' extension at the end of the filename. This script assumes the user wants that folder, but the user can go for a completely different directory path. This option also allows for users to try to open files without the .db extension. The os module proved useful in listing files.
The sys module was used to exit gracefully in a number of situations where the program could not progress.
The tables within each database can be found by looking at the sqlite_master table, which holds other information as well.
The SQLite PRAGMA command was useful in getting information about a table in the database, including the columns in the table.
Finally there is the option of viewing an HTML version of the table, using a similar method to that used before in my previous programs.
Typical results are shown here:
= RESTART: C:/Users/666/Dropbox/Misc Programming/Python/python3/db_viewer.py =
0 base1.db found
1 biology.db found
2 dragon.db found
3 example.db found
4 scimitar.db found
5 Completely different directory
Enter number of file to view: 0
base1.db selected
Current tables in base1.db
0 MonstrousTroops
1 loot
Select which table to inspect or Q to quit: 0
MonstrousTroops table info
(0, 'Name', 'TEXT', 0, None, 0)
(1, 'Challenge', 'REAL', 0, None, 0)
(2, 'Army', 'TEXT', 0, None, 0)
(3, 'Notes', 'TEXT', 0, None, 0)
MonstrousTroops contains 29 records/rows
View table as HTML (Y)? n
No comments:
Post a Comment