app/DB/db.py

   1 import sys
   2 sys.path.append('..')
   3 sys.path.append('../..')
   4 from models import User, Portfolio, Album, Photo, AccessRight
   5 from enum import IntEnum, unique
   6 import inspect
   7 from baseorm import Observer
   8
   9 @unique
  10 class DBDriver(IntEnum):
  11     PG = 1
  12     MySQL = 2
  13
  14 class DB(Observer):
  15     __filename__ = None
  16
  17     def __init__(self, password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None):
  18         self.password = password
  19         self.user = user
  20         self.database = database
  21         self.driver = driver
  22         self.host = host
  23         self.port = port
  24         self.__connect()
  25         if driver == DBDriver.PG:
  26             self.__filename__ = 'db_postgres.sql'
  27         elif driver == DBDriver.MySQL:
  28             self.__filename__ = 'db_mysql.sql'
  29
  30     def __connect(self):
  31         if self.driver is DBDriver.PG:
  32             from postgres import Postgres
  33             try:
  34                 self.db = Postgres(self.database, self.user, self.password, self.host, self.port)
  35             except Exception as e:
  36                 raise Exception(e)
  37         elif self.driver is DBDriver.MySQL:
  38             from mysqld import Mysql
  39             try:
  40                 self.db = Mysql(self.database, self.user, self.password, self.host, self.port)
  41             except Exception as e:
  42                 raise Exception(e)
  43         else:
  44             raise Exception("%s: Unknown DB driver" % self.driver)
  45
  46     def update(self, subject):
  47         print("update: {0}".format(subject))
  48         self.store(subject)
  49
  50     def initDb(self):
  51         file = None
  52         try:
  53             file = open(self.__filename__, "r")
  54             sql = file.read()
  55             file.close()
  56             self.db.ddl(sql)
  57         except:
  58             raise
  59
  60     def store(self, object):
  61         if inspect.isclass(object):
  62             raise Exception("{0}: Class not instance".format(object))
  63
  64         if hasattr(object, '__tablename__'):
  65             table = object.__tablename__
  66         else:
  67             table = object.__class__.__name__
  68
  69         print("Table<{0}>".format(table))
  70         v = [i for i in dir(object) if isinstance(getattr(type(object), i, None), property)]
  71         for p in v:
  72             print("{0}: {1}".format(p, getattr(object, p)))
  73
  74         action = None
  75         column = []
  76         values = []
  77         sql = None
  78         id = None
  79         for p in v:
  80             value = getattr(object, p)
  81             if p == 'id':
  82                 id = value
  83                 if value < 0:
  84                     # insert
  85                     action = 'insert'
  86                 else:
  87                     # update
  88                     action = 'update'
  89             else:
  90                 if hasattr(value, 'id'):
  91                     values.append(value.id)
  92                 else:
  93                     values.append(value)
  94                 column.append(p)
  95         print(action)
  96         if action == 'insert':
  97             if table.lower() == 'user' and self.driver is DBDriver.PG:
  98                 sql = 'insert into "' + table + '" ('
  99             else:
 100                 sql = 'insert into ' + table + ' ('
 101             for i in range(0, len(column)):
 102                 field = column[i]
 103                 if field.lower() == 'user' and self.driver is DBDriver.PG:
 104                     field = '"{0}"'.format(field)
 105                 if i == 0:
 106                     sql += field
 107                 else:
 108                     sql += ', ' + field
 109             sql += ') values ('
 110             for i in range(0, len(values)):
 111                 if i == 0:
 112                     sql += '%s'
 113                 else:
 114                     sql += ', %s'
 115             sql += ') '
 116             object.id = self.db.insert(sql, values)
 117         else:
 118             if table.lower() == 'user' and self.driver is DBDriver.PG:
 119                 sql = 'update "' + table + '" set '
 120             else:
 121                 sql = 'update ' + table + ' set '
 122             for i in range(0, len(column)):
 123                 if i == 0:
 124                     sql += '{0} = %s'.format(column[i])
 125                 else:
 126                     sql += ', {0} = %s'.format(column[i])
 127             sql += ' where id = {0}'.format(id)
 128             self.db.update(sql, values)
 129
 130         print(sql)
 131         print(values)
 132
 133     def __repr__(self):
 134         default = '<%s.%s object at %s>' % (self.__class__.__module__, self.__class__.__name__, hex(id(self)))
 135         return "%s <Driver: %s, User: %s, database: %s>" % (default, self.driver.name,  self.user, self.database)
 136
 137     def __del__(self):
 138         #print("%s: deleted" % self.__class__.__name__)
 139         try:
 140             del self.db
 141         except AttributeError:
 142             pass
 143
 144 class Test(object):
 145     def __init__(self, x):
 146         self.x = x
 147
 148     @property
 149     def x(self):
 150         return self.__x
 151
 152     @x.setter
 153     def x(self, x):
 154         if x < 0:
 155             self.__x = 0
 156         elif x > 1000:
 157             self.__x = 1000
 158         else:
 159             self.__x = x
 160
 161 db = DB('test', DBDriver.MySQL)
 162 #db = DB('test')
 163 db.initDb()
 164
 165 user = User('test', 'test@test.dk', 'test', 'test')
 166 user.addObserver(db)
 167 print(user)
 168 db.store(user)
 169 portfolio = Portfolio('test', user)
 170 print(portfolio)
 171 album = Album('test', portfolio)
 172 print(album)
 173 accessright = AccessRight(user)
 174 print(accessright)
 175 photo = Photo('test', album)
 176 print(photo)
 177 db.store(portfolio)
 178 user.name = 'MIR'
 179 del db