PLC/Storage/AlchemyObject.py

   1 from datetime import datetime
   2
   3 from sqlalchemy.ext.declarative import declarative_base
   4 from sqlalchemy import Table, Column, MetaData, join, ForeignKey
   5 from sqlalchemy import Column, Integer, String, TIMESTAMP
   6 from sqlalchemy.types import Boolean
   7 from sqlalchemy.orm import relationship, backref
   8 from sqlalchemy.orm import column_property
   9 from sqlalchemy.orm import mapper, object_mapper
  10 from sqlalchemy.orm import validates
  11 from sqlalchemy import ForeignKey
  12
  13 from PLC.Storage.Alchemy import Base, engine, dbsession
  14 from PLC.Storage.Record import Record
  15 from PLC.Timestamp import Timestamp
  16 from PLC.Logger import logger
  17
  18 class AlchemyObj(Record):
  19
  20     def __init__(self, api=None, fields = {}, object=None, columns=None):
  21         Record.__init__(self, dict=fields, object=object, columns=columns)
  22         self.api=api
  23
  24     def __iter__(self):
  25         self._i = iter(object_mapper(self).columns)
  26         return self
  27
  28     def next(self):
  29         n = self._i.next().name
  30         return n, getattr(self, n)
  31
  32     def get_table(self):
  33         metadata = MetaData(engine)
  34         table = Table(self.tablename, metadata)
  35         for field in self.fields:
  36             param = self.fields[field]
  37             args = {}
  38             # skip params joined from other tables
  39             if param.joined:
  40                 continue
  41             type = String
  42             if param.type == int:
  43                 type = Integer
  44             elif param.type == datetime:
  45                 type = TIMESTAMP
  46             elif param.type == bool:
  47                 type = Boolean
  48
  49             column = Column(field, type,
  50                             nullable = param.nullok,
  51                             index = param.indexed,
  52                             primary_key=param.primary_key,
  53                             default=param.default)
  54             table.append_column(column)
  55         if not table.exists():
  56             table.create()
  57         return table
  58
  59     def create(self):
  60         self.get_table()
  61
  62     def insert(self, values):
  63         table = self.get_table()
  64         result = dbsession.execute(table.insert().values(values))
  65         dbsession.commit()
  66         return result
  67
  68     def updatedb(self, filter, values):
  69         class Cls(object): pass
  70         table = self.get_table()
  71         clsmapper = mapper(Cls, table)
  72         result = dbsession.query(clsmapper).filter_by(**filter).update(values)
  73         dbsession.commit()
  74         return result
  75
  76     def delete(self, filter={}):
  77         class Cls(object): pass
  78         table = self.get_table()
  79         clsmapper = mapper(Cls, table)
  80         result = dbsession.query(clsmapper).filter_by(**filter).delete()
  81         dbsession.commit()
  82         return result
  83
  84     def select(self, filter={}):
  85         class Cls(object): pass
  86         table = self.get_table()
  87         clsmapper = mapper(Cls, table)
  88         # the easiest thing to do is pass the filter dict to the filter_by() method
  89         # but filter_by doesn't support list values, so we will use filter() instead.
  90         constraints = []
  91         for (field, value) in filter.items():
  92             if isinstance(value, list):
  93                 #if value:
  94                 column = table.columns.get(field)
  95                 if isinstance(column, Column):
  96                     constraints.append(column.in_(value))
  97             else:
  98                 constraints.append(table.columns.get(field) == value)
  99         if not constraints:
 100             return dbsession.query(clsmapper)
 101         else:
 102             return dbsession.query(clsmapper).filter(*constraints)