SHARE
TWEET

A Python Mini ORM

a guest Feb 5th, 2011 1,421 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. """
  2.    This is a very simple Object relational mapper (ORM)
  3.    Use this code for fun. It isn't tested on a lot of cases.
  4.  
  5.    Author: Juan Manuel García <jmg.utn@gmail.com>
  6. """
  7.  
  8. # -*- coding: utf-8 -*-
  9.  
  10. class DataBase(object):
  11.     """
  12.        A database agnostic from the engine that you're using.
  13.        At this time implements MySQLdb, sqlite3, psycopg2 but it's easy
  14.        to extend...
  15.    """
  16.  
  17.     def __init__(self, provider, host='', user='', passwd='', db=''):
  18.         """
  19.            provider: The module for the database connection
  20.                Ej: (MySQLdb, sqlite3, psycopg2)
  21.  
  22.            host: The Host on the database service is running
  23.  
  24.            user: user to connect to the database
  25.  
  26.            passwd: the password of the user
  27.  
  28.            db: the name of the database
  29.        """
  30.  
  31.         self.provider = provider
  32.  
  33.         self.connections = {"MySQLdb" : self.get_mysql_connection,
  34.                             "sqlite3" : self.get_sqlite_connection,
  35.                             "psycopg2" : self.get_postgre_connection,}
  36.  
  37.         self.connections[self.provider.__name__](host, user, passwd, db)
  38.         self.cursor = self.db.cursor()
  39.  
  40.         self.providers = {"MySQLdb" : self.get_mysql_columns,
  41.                           "sqlite3" : self.get_sqlite_columns,
  42.                           "psycopg2" : self.get_postgre_columns,}
  43.  
  44.     def get_mysql_connection(self, host='', user='', passwd='', db=''):
  45.         """
  46.            Get the connection for mysql databases
  47.        """
  48.         self.db = self.provider.connect(host=host, user=user, passwd=passwd, db=db)
  49.  
  50.     def get_postgre_connection(self, host='', user='', passwd='', db=''):
  51.         """
  52.            Get the connection for postgres databases
  53.        """
  54.         self.db = self.provider.connect(host=host, user=user, password=passwd, database=db)
  55.  
  56.     def get_sqlite_connection(self, host='', user='', passwd='', db=''):
  57.         """
  58.            Get the connection for sqlite databases
  59.        """
  60.         self.db = self.provider.connect(db)
  61.  
  62.     def get_mysql_columns(self, name):
  63.         """
  64.            Get the columns name information for mysql databases
  65.        """
  66.         self.sql_rows = 'Select * From %s' % name
  67.         sql_columns = "describe %s" % name
  68.         self.cursor.execute(sql_columns)
  69.         return [row[0] for row in self.cursor.fetchall()]
  70.  
  71.     def get_sqlite_columns(self, name):
  72.         """
  73.            Get the columns name information for sqlite databases
  74.        """
  75.         self.sql_rows = 'Select * From %s' % name
  76.         sql_columns = "PRAGMA table_info(%s)" % name
  77.         self.cursor.execute(sql_columns)
  78.         return [row[1] for row in self.cursor.fetchall()]
  79.  
  80.     def get_postgre_columns(self, name):
  81.         """
  82.            Get the columns name information for postgres databases
  83.        """
  84.         self.sql_rows = 'Select * From "%s"' % name
  85.         sql_columns = """select column_name
  86.                        from information_schema.columns
  87.                        where table_name = '%s';""" % name
  88.         self.cursor.execute(sql_columns)
  89.         return [row[0] for row in self.cursor.fetchall()]
  90.  
  91.     def Table(self, name):
  92.         """
  93.            A queryable table of the database
  94.  
  95.            name: the name of the table to query
  96.  
  97.            return: a Query object
  98.        """
  99.         columns = self.providers[self.provider.__name__](name)
  100.         return Query(self.cursor, self.sql_rows, columns, name)
  101.  
  102.  
  103. class Query(object):
  104.     """
  105.        A query Class wich recursive generate the query-string
  106.    """
  107.  
  108.     def __init__(self, cursor, sql_rows, columns, name):
  109.         self.cursor = cursor
  110.         self.sql_rows = sql_rows
  111.         self.columns = columns
  112.         self.name = name
  113.  
  114.     def filter(self, criteria):
  115.         """
  116.            Implement the "Where" statment of the standard sql
  117.        """
  118.         key_word = "AND" if "WHERE" in self.sql_rows else "WHERE"
  119.         sql = self.sql_rows + " %s %s" % (key_word, criteria)
  120.         return Query(self.cursor, sql, self.columns, self.name)
  121.  
  122.     def order_by(self, criteria):
  123.         """
  124.            Implement the "Order by" statment of the standard sql
  125.        """
  126.         return Query(self.cursor, self.sql_rows + " ORDER BY %s" % criteria, self.columns, self.name)
  127.  
  128.     def group_by(self, criteria):
  129.         """
  130.            Implement the "Group by" statment of the standard sql
  131.        """
  132.         return Query(self.cursor, self.sql_rows + " GROUP BY %s" % criteria, self.columns, self.name)
  133.  
  134.     def get_rows(self):
  135.         """
  136.            Execute the generated query on the database and return a list of Row Objects
  137.        """
  138.         print self.sql_rows
  139.         self.cursor.execute(self.sql_rows)
  140.         return [Row(zip(self.columns, fields), self.name) for fields in self.cursor.fetchall()]
  141.  
  142.     rows = property(get_rows)
  143.  
  144.  
  145. class Row(object):
  146.     """
  147.        A row Class dynamically implemented for each table
  148.    """
  149.  
  150.     def __init__(self, fields, table_name):
  151.         """
  152.            fields: A list of [column_name : value of column]
  153.  
  154.            table_name: the name of the table
  155.        """
  156.         #Assign the name of the current table to the class
  157.         self.__class__.__name__ = table_name + "_Row"
  158.  
  159.         for name, value in fields:
  160.             setattr(self, name, value)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top