Over a million developers have joined DZone.

Interactive SQL Shell

·
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|
Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}