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
  23                 try:
  24                         self.reconnect()
  25                 except:
  26                         logging.error("Cannot connect to database on %s", self.filename, exc_info=True)
  27
  28         def __del__(self):
  29                 self.close()
  30
  31         def close(self):
  32                 """Closes this database connection."""
  33                 if self._db is not None:
  34                         self._db.commit()
  35                         self._db.close()
  36                         self._db = None
  37
  38         def reconnect(self):
  39                 """Closes the existing database connection and re-opens it."""
  40                 self.close()
  41                 self._db = sqlite3.connect(self.filename)
  42                 self._db.isolation_level = None # Enabled autocommit mode
  43
  44         def query(self, query, *parameters):
  45                 """Returns a row list for the given query and parameters."""
  46                 cursor = self._cursor()
  47                 try:
  48                         self._execute(cursor, query, parameters)
  49                         column_names = [d[0] for d in cursor.description]
  50                         return [Row(itertools.izip(column_names, row)) for row in cursor]
  51                 finally:
  52                         #cursor.close()
  53                         pass
  54
  55         def get(self, query, *parameters):
  56                 """Returns the first row returned for the given query."""
  57                 rows = self.query(query, *parameters)
  58                 if not rows:
  59                         return None
  60                 elif len(rows) > 1:
  61                         raise Exception("Multiple rows returned for Database.get() query")
  62                 else:
  63                         return rows[0]
  64
  65         def execute(self, query, *parameters):
  66                 """Executes the given query, returning the lastrowid from the query."""
  67                 cursor = self._cursor()
  68                 try:
  69                         self._execute(cursor, query, parameters)
  70                         return cursor.lastrowid
  71                 finally:
  72                         pass
  73                         #self._db.commit()
  74                         #cursor.close()
  75
  76         def _cursor(self):
  77                 if self._db is None:
  78                         self.reconnect()
  79                 return self._db.cursor()
  80
  81         def _execute(self, cursor, query, parameters):
  82                 try:
  83                         return cursor.execute(query, parameters)
  84                 except sqlite3.OperationalError:
  85                         logging.error("Error connecting to database on %s", self.filename)
  86                         self.close()
  87                         raise