DZone Snippets: sqlobject 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

ORM for pys60

korakot (Korakot Chaovavanich) — Fri, 10 Mar 2006 01:30:46 GMT

I have asked for this a few months.
Here's my own first version for an ORM.
It's adapted from SQLAlchemy and SQLObject.
(and whatever library I have searched)

You need to create a database manually first.
My previous snippets (shell, SQL) may help.



from __future__ import generators

import e32db, re

db = e32db.Dbms()

dbv = e32db.Db_view()

db.open(u'C:\\test.db')



# Some helping classes (need more in next version)

class String:

    pass



class Integer:

    pass



class Float:

    pass



class column:

    def __init__(self, coltype):

        self.coltype = coltype

Here's the mother of all your classes that map to database tables.



class Mapper(object):

    def __init__(self, id=None, **kw):

        if id is None:

            self.id = self._insert(**kw)

        else:

            self.id = id

    def _insert(self, **kw):

        names = ','.join(kw.keys())

        values = ','.join([self.quote(k,v) for k,v in kw.items()])

        tablename = self.__class__.__name__

        q = u"INSERT INTO %s(%s) VALUES (%s)" % (tablename, names, values)

        db.execute(q)

        # get last insert ID

        dbv.prepare(db, u'SELECT id FROM '+tablename+' ORDER BY id DESC')

        dbv.first_line()

        dbv.get_line()

        return dbv.col(1)

    def __getattr__(self, name):

        if name in self.mapping.__dict__:

            q = 'SELECT '+name+' FROM '+self.__class__.__name__

            q += ' WHERE id='+str(self.id)

            dbv.prepare(db, unicode(q))

            dbv.first_line()

            dbv.get_line()

            return dbv.col(1)

        else:

            return self.__dict__[name]

    def __repr__(self):

        return '<%s id=%d>' % (self.__class__.__name__, self.id)

    def quote(self, name, value):

        if self.mapping.__dict__[name].coltype == String:

            return "'%s'" % value.replace("'", "''")  # encode single quote

        else:

            return str(value)

    def __setattr__(self, name, value):

        if name in self.mapping.__dict__:

            q = 'UPDATE '+self.__class__.__name__+' SET '+name+'='

            q += self.quote(name, value) + " WHERE id=" + str(self.id)

            db.execute(unicode(q))

        else:

            self.__dict__[name] = value

    def set(self, **kw):

        q = "UPDATE "+self.__class__.__name__+" SET "

        for k, v in kw.items():

            q += k+'='+self.quote(k,v)+','

        q = q[:-1]+" WHERE id=%s" % self.id

        db.execute(unicode(q))

    def delete(self):

        q = 'DELETE FROM '+self.__class__.__name__+" WHERE id=" + str(self.id)

        db.execute(unicode(q))

        self.id = None

    def dict(self):

        names = [k for k in self.mapping.__dict__.keys() if not k.startswith('__')]

        q = 'SELECT '+','.join(names)+' FROM '+self.__class__.__name__

        q += ' WHERE id=' + str(self.id)

        dbv.prepare(db, unicode(q))

        dbv.first_line()

        dbv.get_line()

        dct = {'id': self.id}

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

            dct[names[i]] = dbv.col(i+1)

        return dct

    def select(cls, where=None, orderby=None):

        q = 'SELECT id FROM '+cls.__name__

        if where:

            q += ' WHERE '+where

        if orderby:

            q += ' ORDER BY '+orderby

        dbv = e32db.Db_view()  # need its own cursor

        dbv.prepare(db, unicode(q))

        dbv.first_line()

        for i in range(dbv.count_line()):

            dbv.get_line()

            yield cls(dbv.col(1))

            dbv.next_line()

    select = classmethod(select)

Here's how you create your class.



class Person(Mapper):

    class mapping:

        # doesn't need id = column(Integer)

        name = column(String)

        age = column(Integer)

Create an RSS feed from an SQL query

tsch () — Sun, 13 Nov 2005 02:51:47 GMT



#!/usr/bin/env python

# -*- encoding: latin1 -*-



import datetime,PyRSS2Gen,sqlobject

from sqlobject.postgres import builder



con = builder()(user = 'user', passwd = '', host = 'localhost', db='name')



# set db encoding (maybe optional)

con.queryOne("SET client_encoding TO 'latin1'; SELECT 1;")



items = []

for res in con.queryAll("""SELECT title,url,datum,description FROM table ORDER BY datum DESC LIMIT 30"""):

    items.append(

        PyRSS2Gen.RSSItem(

        title = res[0], link = res[1],

        description = """%s
on %s
%s"""%(res[0],res[2],res[]3),

        guid = PyRSS2Gen.Guid(res[1]), pubDate = res[2]))



    # generate rss feed

PyRSS2Gen.RSS2(

    title         = "sql2rss feed",

    link          = "http://localhost/die URL",

    description   = "The latest sql2rss news",

    lastBuildDate = datetime.datetime.now(),

    items         = items).write_xml(open("sql2rss.xml", "w"))

Declarative metaclass

korakot (Korakot Chaovavanich) — Sat, 01 Oct 2005 19:23:44 GMT

Metaclass is like a black magic in python.
I wouldn't learn it if not necessary to read someone's code.
SqlObject uses it. So, if I want to port it to pys60 Dbms,
I need to learn metaclass.

Fortunately, this one is not too much to understand.



class DeclarativeMeta(type):

    def __new__(meta, class_name, bases, new_attrs):

        cls = type.__new__(meta, class_name, bases, new_attrs)

        if new_attrs.has_key('__classinit__'):

            cls.__classinit__ = staticmethod(cls.__classinit__.im_func)

        cls.__classinit__(cls, new_attrs)

        return cls

When you use it you do this



class YourClass(object):

    __metaclass__ = DeclarativeMeta

    # ... and other class variables

    def __classinit__(cls, new_attrs):

        # do whatever you want with the new class

        # ...

    def __init__(self):

        # do whatever you want with the new instance

You use this when you want a subclass to have some magic
done to it when it is first defined. What you say in
__classinit__ will get done to the new subclass, eg.
create more methods, properties, etc.