def selectIdentity(self, identityObj, limitStart=0, limitDistance=50):
"""Builds a dynamic query using the identityObject to collect the parameters"""
# check we get an instance of identityObject
if not isinstance(identityObj, IdentityObject):
raise mdb.ProgrammingError("Must pass in an identityObject")
# Check the range parameters are valid
if limitStart < 0:
raise mdb.ProgrammingError("The start point must be a positive int")
if limitDistance > 50:
raise mdb.ProgrammingError("You cannot select more than 50 rows at one time")
if limitDistance < 1:
raise mdb.ProgrammingError("You must select at least one row")
# =======================================
# build the query from the identityObject's data
query = "SELECT * FROM " + self.tableName() + " WHERE "
params = [] # create a list to store all the parameters in
# build the WHERE clause of the query
for field in identityObj.fields:
first = True
for comp in identityObj.fields[field].comps:
if not first:
query += " OR " # join clauses for the same column using OR operator
query += comp['name'] + " " + comp['operator'] + " %s" # add placeholder for the column name and value
params.append(comp['value']) # add the value to a list of params
first = False
query += " AND " # join clauses for different columns using AND operator
query = query[:-5] # remove the final AND from the query
query += " LIMIT %s, %s"
# finish preparing all the params
params.append(limitStart) # add the two limit params to the list
params.append(limitStart + limitDistance)
paramsTuple = tuple(params) # convert the list of params into a tuple for the query exectuion
#========================================
# run the query and pull the results into a collection object
cursor = self.db.getCursor() # get the database cursor
rowsAffected = cursor.execute(query, paramsTuple) # run the query storing the number of rows affected
data = cursor.fetchall() # fetch the data from the server
cursor.close() # close the cursor to the database
if rowsAffected > 0: # check there were results to be returned
return Collection(data, self) # return a collection of the objects
else:
return None # otherwise return nada
def update(self, obj):
"""Updates a given object's records in the database"""
if not isinstance(obj, DomainObject) :
raise mdb.ProgrammingError("This function expects a DomainObject object as the input parameter")
if obj.getId() is -1: # can't update an object that has not been inserted
raise mdb.ProgrammingError("You can only update objects that are in the database, please insert this object first")
print "Update " + str(type(obj)) + " object " + str(obj.getId())
return self._doUpdate(obj)
def delete(self, obj):
"""Deletes a given object from the database"""
if not isinstance(obj, DomainObject):
raise mdb.ProgrammingError("This function expects a DomainObject object as the input parameter")
if obj.getId() is -1:
raise mdb.ProgrammingError("You cannot delete an object that was never in the database. It has no id")
print "Deleting " + str(type(obj)) + " object " + str(obj.getId())
query = self._deleteStmt(obj)
params = (obj.getId(),)
return self.__executeOperation(query, params)
def getData(db_obj, table, column="*", conditions=''):
"""
从数据库中获取数据
:param db_obj: 目标数据库对象
:param table: 来源表
:param column: select的列
:param conditions: 筛选条件
:return: 返回查询的数据 (dictionary形式)
"""
try:
db = MySQLdb.connect(db_obj.host_ip,
db_obj.username,
db_obj.password,
db_obj.database,
charset='utf8')
try:
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""select %s from %s%s;""" %
(column, table, conditions))
print("""select %s from %s%s;""" % (column, table, conditions))
result = cursor.fetchall()
return result
except MySQLdb.ProgrammingError as e:
raise MySQLdb.ProgrammingError("ERROR 数据库执行语句出错:" + str(e))
#raise MySQLdb.ProgrammingError("ERROR 数据库执行语句出错:" + e)
finally:
# 一定关闭数据库连接
db.close()
except MySQLdb.OperationalError as e:
raise MySQLdb.OperationalError("ERROR 数据库连接不上:" + str(e))
def findByUser(self, user, start=0, number=50):
if start < 0:
raise mdb.ProgrammingError(
"The start point must be a positive int")
if number > 50:
raise mdb.ProgrammingError(
"You cannot select more than 50 rows at one time")
query = """SELECT g.*, gt.name as game_type_name
FROM games g
LEFT JOIN game_types gt ON g.game_type_id = gt.id
LEFT JOIN players p ON p.game_id = g.id
LEFT JOIN users u ON p.user_id = u.id
WHERE u.id = %s LIMIT %s, %s"""
params = (user.getId(), start, start + number)
return DeferredCollection(self, query, params)
def _findMany(self, query, start=0, number=50):
"""Stock stuff for finding multiple records at the same time limited by the limit factors"""
# check that the limit params are not off the wall
if start < 0:
raise mdb.ProgrammingError("The start point must be a positive int")
if number > 50:
raise mdb.ProgrammingError("You cannot select more than 50 rows at one time")
# run the qurery
params = (start, start + number)
cursor = self.db.getCursor()
rowsAffected = cursor.execute(query, params)
data = cursor.fetchall()
cursor.close()
if rowsAffected > 0: # check if there are results to be returned
return Collection(data, self) # create a collection object for the results
else:
return None
def insert(self, obj):
"""Inserts this object into the database as its records"""
if not isinstance(obj, DomainObject):
raise mdb.ProgrammingError("This function expects a DomainObject object as the input parameter")
print "Inserting new " + str(type(obj)) + " object " + str(obj.getId())
result = self._doInsert(obj) # do the insertion specifics
if result:
self.addToWatcher(obj) # add the new object to the object watcher
return result
def _check_param(self, obj, table):
model_class = None
t = table
is_dict = (type(obj) == dict)
if not is_dict:
model_class = obj.table_schema.model_class
if not table:
t = obj.table_schema.table_name
elif isinstance(table, basestring):
table_schema = DatabaseSchema.tables[table]
model_class = table_schema.model_class
else:
msg = 'table is required while obj is dict'
raise MySQLdb.ProgrammingError(msg)
return model_class, t
def execute(self, *args, **kwargs):
try:
result = self._cursor.execute(*args, **kwargs)
except MySQLdb.ProgrammingError as e:
raise MySQLdb.ProgrammingError(
e.args[0], e.args[1] +
'.\nSTATEMENT: {}'.format(self._cursor._last_executed))
except MySQLdb.OperationalError as e:
# Sometimes a MySQL session times out. In this case, we wish to reconnect, and reissue the query.
if e[0] == 2006:
self.connect()
result = self._cursor.execute(*args, **kwargs)
else:
raise MySQLdb.OperationalError(
e.args[0], e.args[1] +
'.\nSTATEMENT: {}'.format(self._cursor._last_executed))
return result
