DZone Snippets: dbms code

dbms database class

nerdcoder (Dan Larsen) — Tue, 03 Jul 2007 21:46:51 GMT

I'm at my first attempt at creating something for the PyS60 and I needed a simple sql wrapper class...
I'm an experienced programmer, but has never programmed in python for s60 and it's been a while since I've used Python... So I hope this will help somebody, not annoy ;-)



# Filename: db.py save in your Python dir

import e32db



class db:

    def __init__(self, dbpath):

	self.db = e32db.Dbms()

	self.dbv = e32db.Db_view()

	self.reset_counters()

	try:

	    self.db.open(unicode(dbpath))

	except:

	    self.db.create(unicode(dbpath))

	    self.db.open(unicode(dbpath))



    def reset_counters(self):

	self.affected_rows = 0

	self.num_rows = 0

	self.__internal_counter = 0



    def query(self, sql):

	self.reset_counters()

	if sql.lower().startswith('select'):

	    self.dbv.prepare(self.db, unicode(sql))

	    self.dbv.first_line()

	    self.num_rows = self.dbv.count_line()

	else:

	    self.affected_rows = self.db.execute(unicode(sql))



    def next(self):

	row = {'id': 0}

	if self.num_rows < 1:

	    self.reset_counters()

	    raise StopIteration

	elif self.__internal_counter < self.num_rows:

	    self.dbv.get_line()

	    for i in range(self.dbv.col_count()):

		row[i] = self.dbv.col(i+1)

	    self.dbv.next_line()

	    self.__internal_counter += 1

	    return row

	else:

	    self.reset_counters()

	    raise StopIteration



    def __iter__(self):

	return self

Now to create and fill db, create a file in the same dir as the db.py with this content:



# Change __exec_path to the path of your python script dir

__exec_path = "E:\\Python\\"

dbname = "test"



import sys

sys.path.append(__exec_path)

from db import db



# This will open E:\\Python\test.db - it will be created first, if not existing

mydb = db(__exec_path+dbname+'.db')

mydb.query("create table testing (id counter, name varchar)")

mydb.query("insert into testing (name) values ('test 1')")

mydb.query("insert into testing (name) values ('test 2')")

Now go ahead and have fun:



# Again change __exec_path to the path of your python script dir

__exec_path = "E:\\Python\\"

dbname = "test"



import sys

sys.path.append(__exec_path)

from db import db



# Opens E:\\Python\test.db

mydb = db(__exec_path+dbname+'.db')

mydb.query("select * from testing")

for row in mydb:

    print "-> ",row[0]," ",row[1]," <-"

Have fun!
Dan

SQL for Symbian DBMS

korakot (Korakot Chaovavanich) — Thu, 09 Mar 2006 13:04:15 GMT

I have summarized the allowable SQL below.



# 'SELECT' is the most popular SQL

SELECT select-list FROM table-name [ WHERE search-condition ] [ ORDER BY sort-order ] 

# 3 search condition types: compare, like, null



# DML : INSERT, DELETE, UPDATE 

INSERT INTO table-name [ ( column-identifier,… ) ] VALUES ( column-value,… ) 

DELETE FROM table-name [ WHERE search-condition ] 

UPDATE table-name SET update-column,… [ WHERE search-condition ] 



# DDL : Work with the schema

CREATE TABLE table-name (column-definition,…) 

DROP TABLE table-name 

ALTER TABLE table-name { ADD add-column-set [ DROP drop-column-set ] | DROP drop-column-set } 

CREATE [ UNIQUE ] INDEX index-name ON table-name ( sort-specification,… ) 

DROP INDEX index-name FROM table-name

Plus a few column types (not all of them)



['BIT', 'INTEGER', 'COUNTER', 'BIGINT', 'FLOAT',

 'TIMESTAMP', 'VARCHAR(n)', 'LONG VARCHAR']

# COUNTER is an auto-incremented unsigned integer

Finding all symbian database files

korakot (Korakot Chaovavanich) — Sun, 12 Feb 2006 20:16:39 GMT

Hmm.. I am considering hacking the Dbms format.
I want to know what tables are there in each db file.
The first step is to list all such files.



>>> def print_db(arg, dirname, names):

...   for name in names:

...     if name.endswith('.db'):

...       print dirname + '\\' + name

...

>>> os.path.walk('C:\\', print_db, None)

C:\\SportsDiary.db

C:\\test.db

C:\system\Data\Bookmarks1.db

C:\system\Data\lcscfg.db

C:\system\Data\lcs.db

C:\system\Data\NSmlDSSettings.db

C:\system\Data\SavedDecks1.db

C:\system\Data\midp2\systemams\MIDP2SystemAMSDynamic.db

C:\system\Data\midp2\systemams\MIDP2SystemAMSStatic.db

C:\system\Data\srsplugin\SDDatabase.db

C:\system\Data\vasdb\vasdatabase.db

>>>