Simple SQL-statements into Python classes

[Gerhard Häring]

Thomas Weholt <2002 at weholt.org> wrote:
> Has anybody made an attempt at converting simple SQL-statements ( mainly
> plain selects ) into Python objects? I'm thinking of trying to make a parser
> that takes the most basic version sql-statements (select, insert, update,
> delete) and convert them into a python class with methods like load, insert,
> save and delete.
> 
> If anybody has anything similar or have opinions on this matter, please let
> me know.

There are tons of object-relational mappers for Python. In one of my unit test
modules, however, I do something very simple, which might perhaps give some
inspiriation:

    [...]

    # This isn't that nice, you can ignore it; the createXXX() return the
    # primary id of the created record:

    def createProject(self, number=""):
        cursor = self.con.cursor()
        cursor.execute("""
            INSERT INTO RELPROJECT
                (PROJ_PROJECT_NAME, PROJ_PROJECT_NO)
            VALUES (:p1, :p2)
            """, ("TEST" + number, "TEST" + number))
        cursor.execute("SELECT PROJ_SEQ.CURRVAL FROM DUAL")
        return cursor.fetchone()[0]

    [...]

    # *Generic* UPDATE statement wrapper (contains some junk, too - this is
    # only for the test cases, not safe SQL):

    def _changeTable(self, tablename, primkey, id, **kwargs):
        cursor = self.con.cursor()

        colnames = kwargs.keys()
        values = []
        for val in kwargs.values():
            if type(val) is DateTimeType:
                val = date2str(val)
            elif type(val) is type(""):
                val = "'%s'" % val
            else:
                val = str(val)
            values.append(val)

        cursor.execute("UPDATE " + tablename + " SET " + \
            ", ".join(["%s=%s" % (k,v) for k, v in zip(colnames, values)]) + \
            "WHERE %s = %i" % (primkey, id))

    # But here, I'm able to create wrappers for the UPDATE statements with
    # *very little* code :-)

    def changeOpLoc(self, id, **kwargs):
        self._changeTable("CTLG_OP_LOC_TYPE", "COLT_ID", id, **kwargs)

    def changeLocation(self, id, **kwargs):
        self._changeTable("CTLG_LOCATION_TYPE", "CLOC_ID", id, **kwargs)

    [...]


    # Here's how to use the update statement wrappers:

    oploc = self.createOpLoc(self.getTestLocation())

    self.changeOpLoc(colt_id, COLT_OP_STARTED_MON='Y',
                              COLT_OP_STARTED_TUE='Y',
                              COLT_OP_STARTED_WED='Y',
                              COLT_OP_STARTED_THU='Y',
                              COLT_OP_STARTED_FRI='Y',
                              COLT_OP_STARTED_SAT='N',
                              COLT_OP_STARTED_SUN='N',
                              COLT_WD_START='Y',
                              COLT_WD_END='Y',
                              COLT_S2T=0,
                              COLT_STRESS_UNIT='h',
                              COLT_DUR_TPD=0,
                              COLT_T0=24*3 + 2)

Just food for thought. I didn't say it's particularly good food ;-)
-- 
Gerhard Häring
OPUS GmbH München
Tel.: +49 89 - 889 49 7 - 32
http://www.opus-gmbh.net/