DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Interactive SQL Shell

03.09.2006
| 7293 views |
  • submit to reddit
        To help myself learning about SQL in Symbain DBMS, I write
a small script to act as an interactive SQL shell.
First you need to connect to the database.
>>> import e32db
>>> db = e32db.Dbms()
>>> dbv = e32db.Db_view()
>>> db.open(u'C:\\test.db')  # might need db.create(...)
Here's the query simplification code.
def Q(sql):
    if sql.upper().startswith('SELECT'):
        dbv.prepare(db, unicode(sql))
        dbv.first_line()
        rows = []
        maxlen = [0] * dbv.col_count()
        for i in range(dbv.count_line()):
            dbv.get_line()
            result = []
            for i in range(dbv.col_count()):
                try:
                    val = dbv.col(i+1)
                except:    # in case coltype 16
                    val = None
                result.append(val)
                maxlen[i] = max(maxlen[i], len(str(val)))
            rows.append(result)
            dbv.next_line()
        fmt = '|'+ '|'.join(['%%%ds' % n for n in maxlen]) + '|'
        for row in rows:
            print fmt % tuple(row) 
    else:
        n = db.execute(unicode(sql))
        print '%d rows affected' % n
After that, playing with SQL is quite simple.
>>> Q("CREATE TABLE person (id COUNTER, name VARCHAR)")
0 rows affected
>>> Q("INSERT INTO person(name) VALUES ('Korakot')")
1 rows affected
>>> Q("INSERT INTO person(name) VALUES ('morning_glory')")
1 rows affected
>>> Q("SELECT * from person")
|0|      Korakot|
|1|morning_glory|