May 11, 2010

Simple Database QueryTool

This fascinating little program only needs proper exception handling to turn it into a versatile teaching tool. As it is, one SQL syntax error and you have to re-run the program. I was impressed by how easy this was to write, and it should work on any Python 3 installation.

The odd .strip() calls compensate for some glitch in certain input channels.

"Takes input from the user and runs it through a sqlite relational database."
import sqlite3
dbname = input("Database name: ").strip()
dbpath = r"V:\{0}.db".format(dbname)
conn = sqlite3.connect(dbpath)
curs = conn.cursor()

while True:
    stmt = input("DB> ").strip()
    if not stmt:
        break
    while True:
        line = input("... ").strip()
        if not line:
            break
        stmt += "\n" + line
    curs.execute(stmt)
    conn.commit()
    result = curs.fetchall()
    if result:
        print("Results:")
        for row in result:
            print(row)
conn.close()
print("Finished")

So, if you have ever wanted to dive down into SQL, Python now provides you with an easy tool. Then you just have to learn SQL. That's where the exception handling comes in ...

2 comments:

Kent Johnson said...

Why not just use the sqlite3 command-line tool available from
http://sqlite.org/download.html ?

It allows the evaluation of SQL commands, includes error handling and has many useful meta commands as well.

Steve said...

Absolutely no reason at all, but I might argue that typing the above Python program in and running it might overall be more instructive.