Python Data Access Layer Generator

Posted on by • Posted in Python • Tagged , , , , , , , , , • Leave a comment

If you are bored to write the data access layer from scratch for every table in your database and you don’t want include more dependencies in your proyect (like SQLAlquemy, Elixir, Django-ORM, SQLObjects) here’s the solution!

This piece of code is a Data Layer SQL Generator for your Data Table Objects (DTOs). It provides the SQL statments to the basic operations in the database.

It’s very simple to use:

from Mapper import ObjectMapper

class Person(object):

    def __init__(self):

        self.name = 'john'
        self.age = 22

        #this properties are ignored
        self._some_information = 'Not saved in table'
        self.__other_stuff = 'Also ignored'

p = Person()
m = ObjectMapper(p)

>>> m.insert()
"INSERT INTO person(age,name) VALUES ('22','john')"
>>> print m.update(id=1)
"UPDATE person SET age = '22', name = 'john' WHERE id_person = '1'"
>>> m.delete(id=1)
"DELETE FROM person WHERE id_person = 1"
>>> m.get_all()
"SELECT age, name FROM person"
>>> m.get_by_id(id=1)
"SELECT age, name FROM person WHERE id_person = 1"

And forget about writting the repetitive SQL Layer for your tables.

Enjoy the code:

class ObjectMapper(object):

    def __init__(self, entity):
        self.entity = entity

    def _get_pairs(self):
        return [(k,str(v)) for k,v in self.entity.__dict__.iteritems() if not k.startswith("_") and not k.startswith("__")]

    def _get_names(self):
        return [pair[0] for pair in self._get_pairs()]

    def _get_values(self):
        return [pair[1] for pair in self._get_pairs()]

    def _get_names_values(self):
        return self._get_names(), self._get_values()

    def insert(self):
        names, values = self._get_names_values()
        names = "%s" % ",".join(names)
        values = "'%s'" % "','".join(values)
        return "INSERT INTO %s(%s) VALUES (%s)" % (self.get_table(), names, values)

    def update(self, id):
        pairs = self._get_pairs()
        fields = ", ".join(["%s = '%s'" % (k,v) for k,v in pairs])
        return "UPDATE %s SET %s WHERE %s = '%s'" % (self.get_table(), fields, self.get_id(), id)

    def delete(self, id):
        return "DELETE FROM %s WHERE %s = %s" % (self.get_table(), self.get_id(), id)

    def get_all(self):
        names = ", ".join(self._get_names())
        return "SELECT %s FROM %s" % (names, self.get_table())

    def get_by_id(self, id):
        names = ", ".join(self._get_names())
        return "SELECT %s FROM %s WHERE %s = %s" % (names, self.get_table(), self.get_id(), id)

    #Overridables

    #You can Extend from this class and override the following methods in order to configurate
    #the table name and the id_name

    def get_table(self):
        return self.entity.__class__.__name__.lower()

    def get_id(self):
        return "id_%s" % self.entity.__class__.__name__.lower()

You can also checkout the repo on github

Leave a comment