database.py

   1 #!/usr/bin/python
   2
   3 import itertools
   4 import logging
   5 import os
   6 import sqlite3
   7
   8 class Row(dict):
   9         """A dict that allows for object-like property access syntax."""
  10         def __getattr__(self, name):
  11                 try:
  12                         return self[name]
  13                 except KeyError:
  14                         raise AttributeError(name)
  15
  16
  17 class Database(object):
  18         def __init__(self, filename):
  19                 self.filename = filename
  20
  21                 self._db = None
  22                 self.reconnect()
  23
  24         def __del__(self):
  25                 self.close()
  26
  27         def close(self):
  28                 """Closes this database connection."""
  29                 if self._db is not None:
  30                         self._db.commit()
  31                         self._db.close()
  32                         self._db = None
  33
  34         def reconnect(self):
  35                 """Closes the existing database connection and re-opens it."""
  36                 self.close()
  37                 self._db = sqlite3.connect(self.filename)
  38                 self._db.isolation_level = None # Enabled autocommit mode
  39
  40         def query(self, query, *parameters):
  41                 """Returns a row list for the given query and parameters."""
  42                 cursor = self._cursor()
  43                 try:
  44                         self._execute(cursor, query, parameters)
  45                         column_names = [d[0] for d in cursor.description]
  46                         return [Row(itertools.izip(column_names, row)) for row in cursor]
  47                 finally:
  48                         #cursor.close()
  49                         pass
  50
  51         def get(self, query, *parameters):
  52                 """Returns the first row returned for the given query."""
  53                 rows = self.query(query, *parameters)
  54                 if not rows:
  55                         return None
  56                 elif len(rows) > 1:
  57                         raise Exception("Multiple rows returned for Database.get() query")
  58                 else:
  59                         return rows[0]
  60
  61         def execute(self, query, *parameters):
  62                 """Executes the given query, returning the lastrowid from the query."""
  63                 cursor = self._cursor()
  64                 try:
  65                         self._execute(cursor, query, parameters)
  66                         return cursor.lastrowid
  67                 finally:
  68                         pass
  69                         #self._db.commit()
  70                         #cursor.close()
  71
  72         def _cursor(self):
  73                 if self._db is None:
  74                         self.reconnect()
  75                 return self._db.cursor()
  76
  77         def _execute(self, cursor, query, parameters):
  78                 try:
  79                         return cursor.execute(query, parameters)
  80                 except sqlite3.OperationalError:
  81                         logging.error("Error connecting to database on %s", self.filename)
  82                         self.close()
  83                         raise