Page MenuHomec4science
Files F65219143

sqlobject.py
No OneTemporary
Actions

File Metadata

Created
Sat, Jun 1, 22:58

sqlobject.py
View Options

from __future__ import print_function
import copy
import re
import sys
import datetime
class SQLObject(object):
"""
"""
def __str__(self):
output = ""
keys = set(self.entries.keys())
keys.remove('id')
keys = list(keys)
keys = ['id'] + keys
outputs = []
for k in keys:
v = self.entries[k]
outputs += [k + ": " + str(v)]
return "\n".join(outputs)
def __getitem__(self, index) :
return self.entries[index.lower()]
def __setitem__(self, index, value) :
self.entries[index.lower()] = value
def commit(self):
self.base.connection.commit()
def setConstraint(self, constraints) :
# print (constraints)
for cons in constraints:
_regex = "\s*(\w*)\s*((?:\=~)|(?:!=)|<|>|=)\s*(.*)"
match = re.match(_regex,cons)
# print (match.groups())
if (not match or (not len(match.groups()) == 3)):
print ("malformed constraint: " + cons)
sys.exit(-1)
key = match.group(1).lower().strip()
op = match.group(2)
if (op == "=~"):
op = "~"
# print (op)
val = match.group(3)
# print (key)
# print (op)
# print (val)
if (not key in self.types):
print ("unknown key '{0}'".format(key))
print (constraints)
print ("possible keys are:")
for k in self.types.keys():
print ("\t" + k)
sys.exit(-1)
# print (val)
# print (key)
# print (self.types[key])
val = self.types[key](val)
if (not key in self.entries):
self.entries[key] = []
self.entries[key].append(val)
if (not key in self.operators):
self.operators[key] = []
self.operators[key].append(op)
def setEntries(self,params):
self.prepare()
for p, val in params.iteritems():
if (p in self.types):
self.entries[p] = val
def setFields(self, constraints) :
for cons in constraints:
_regex = "(\w*)\s*=\s*(.*)"
match = re.match(_regex,cons)
if (not match or (not len(match.groups()) == 2)):
print ("malformed assignment: " + cons)
sys.exit(-1)
key = match.group(1).lower().strip()
val = match.group(2)
if (not key in self.types):
print ("unknown key '{0}'".format(key))
print ("possible keys are:")
for k in self.types.keys():
print ("\t" + k)
sys.exit(-1)
val = self.types[key](val)
self.entries[key] = val
def __init__(self,base,my_id=0):
self._is_input = True
self.foreign_keys = {}
self.allowNull = {}
self.types = {}
self.entries = {}
self.id = my_id
self.base = base
self.operators = {}
def __copy__(self):
_cp.types = self.types.copy()
_cp.entries = self.entries.copy()
_cp.id = self.id
_cp.foreign_keys = self.foreign_keys.copy()
_cp.allowNull = self.foreign_keys.copy()
_cp.connection = self.connection.copy()
return _cp
def __deepcopy__(self, memo):
_cp = type(self)(self.base)
_cp.types = copy.deepcopy(self.types.copy(),memo)
_cp.entries = copy.deepcopy(self.entries.copy(),memo)
_cp.id = self.id
_cp.foreign_keys = copy.deepcopy(self.foreign_keys,memo)
_cp.allowNull = copy.deepcopy(self.foreign_keys,memo)
_cp.connection = self.base
return _cp
def prepare(self):
self.base.setObjectItemTypes(self)
@property
def is_input(self):
return _is_input
@is_input.setter
def is_input(self, value):
self._is_input = value
@property
def id(self):
if not hasattr(self, "_id"):
raise Exception("You either forgot to save the id at insert for "
"class '{0}' or id this object doesn't know its id "
"yet because it has never been inserted into the"
"data base".format(self.__class__.__name__))
return self._id
@id.setter
def id(self, value):
self._id = value
def __conform__(self, foo):
if foo is ISQLQuote:
return self
@classmethod
def from_select(cls, fetched_row):
raise Exception("Your forgot to implement the select method for class "
"'{0}'!".format(self.__class__.__name__))
def _sql_members(self):
raise Exception("Till, you forgot to implement the property "
"__sql_members for the class '{0}'".format(
self.__class__.__name__))
def insert(self):
params = list()
# print (self.types)
ex_msg = ""
for key,value in self.types.items():
if key == "id":
continue
if ((key not in self.entries) and (key not in self.allowNull)):
ex_msg += "key '" + key + "' must be given a value before proceeding insertion\n"
if (not ex_msg == ""):
raise Exception("\n****************\n"+ex_msg+"****************\n")
for key,value in self.entries.items():
# print (key)
# print (self.types[key])
# print (value)
params.append(self.types[key.lower()](value))
request = "INSERT INTO {0}.{1} ({2}) VALUES ({3}) RETURNING id".format(self.base.schema,self.table_name,','.join(self.entries.keys()),','.join(["%s" for item in params])), params
return request
def delete(self):
request,params = "DELETE FROM {0}.{1} WHERE id={2}".format(self.base.schema,self.table_name,self.id),[]
self.base.performRequest(request,params)
def update(self):
params = list()
keys = list()
for key,value in self.entries.items():
if (value is None):
continue
_type = self.types[key]
# print (_type)
# print (key)
# print (type(value))
if (_type == datetime.datetime):
continue
# _type = str
keys.append(key)
params.append(_type(value))
request = "UPDATE {0}.{1} SET ({2}) = ({3}) WHERE id = {4}".format(self.base.schema,self.table_name,','.join(keys),','.join(["%s" for item in params]),self.id)
self.base.performRequest(request,params)
def getquoted(self):
objs = [sql_adapt(member) for member in self._sql_members()]
for obj in objs:
if hasattr(obj, 'prepare'):
obj.prepare(self._conn)
quoted_objs = [obj.getquoted() for obj in objs]
return '(' + ', '.join(quoted_objs) + ')'
def createTableRequest(self):
query_string = "CREATE TABLE {0}.{1} ( id SERIAL PRIMARY KEY,".format(self.base.schema,self.table_name)
for key,value in self.types.items():
if (value == float):
type_string = "DOUBLE PRECISION"
elif (value == int):
type_string = "INTEGER"
elif (value == str):
type_string = "TEXT"
elif (value == bool):
type_string = "BOOLEAN"
elif (value == datetime.datetime):
type_string = "TIMESTAMP"
else:
print (value)
raise Exception("type '{0}' not handled".format(value))
query_string += "{0} {1} ".format(key, type_string)
if (key not in self.allowNull):
query_string += " NOT NULL"
query_string += ","
for key, value in self.foreign_keys.items():
query_string += "FOREIGN KEY ({0}) REFERENCES {1}.{2},".format(key,self.base.schema,value)
return query_string[:-1] +");"
def makeMatchingCondition(self, binary_operator = "and"):
self.prepare()
condition = ""
params = []
binary_operator = " " + binary_operator + " "
for key,values in self.entries.items():
if (values is None):
continue
if (not type(values) == list):
values = [values]
for i in range(0,len(values)):
try:
if (not isinstance(self.types[key],type)):
raise Exception("Key " + key + " needs to be given a type and not " + self.types[key])
except KeyError as err:
raise KeyError("{0}, keys are {1}".format(err, self.types.keys()))
# print (str(key) + "type:" + str(self.types[key]) + " " + str(values[i]))
params.append(self.types[key](values[i]))
op = "="
if (key in self.operators):
op = self.operators[key][i]
if (condition):
condition += binary_operator
if isinstance(values[i], float) and op == "=" and values[i] != 0:
condition += "@({0}.{1} / %s -1) < 1e-10".format(
self.table_name, key)
else:
condition += self.table_name + "." + key
condition += " " + op + " "
condition += " %s "
return condition,params
def makeSelectRequest(self):
request = "SELECT * FROM {0}.{1} ".format(self.base.schema,self.table_name)
return request
def getObjectList(self, order="id"):
self.prepare()
request = "SELECT * FROM {0}.{1} ORDER BY ".format(self.base.schema,self.table_name) + order
curs = self.base.performRequest(request)
return self.buildList(curs)
return self.buildList(sqlobject,curs)
def getMatchedObjectList(self, order_condition = ""):
condition, params = self.makeMatchingCondition()
request = self.makeSelectRequest()
# print (condition)
# print (order_condition)
if (condition):
request += "WHERE " + condition
if (order_condition):
request += order_condition
#print (request)
#print (params)
curs = self.base.performRequest(request, params)
return self.buildList(curs)
def buildList(self, curs):
col_info = self.base.getColumnProperties(self)
list_objects = []
for entries in curs:
# print (col_info)
obj = copy.deepcopy(self)
for i in range(0,len(col_info)):
col_name = col_info[i][0]
obj[col_name] = entries[i]
obj.id = obj["id"]
# print (col_name + " " + str(entries[i]))
list_objects.append(obj)
# print (list_objects[0].entries)
return list_objects

Event Timeline

c4science · Help