Interactive SQL Shell

• Tweet
• 

        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|

Tags:
• Database
• interactive
• pys60
• Python
• series60
• shell
• SQL
• Symbian