Interactive SQL shell

korakot (Korakot Chaovavanich) — Thu, 09 Mar 2006 14:01:20 GMT

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|

DZone Snippets: interactive code

Interactive SQL shell