def delete(self, table, condition, condition_val): query = "DELETE from " + table + " WHERE " + condition try: return self.execute(query, condition_val) except Exception, e: logger.logclient("error", "delete", "query: %s, param:%s" % (query, condition_val)) logger.logclient("error", "delete", str(e)) return
def _parse_data(self, changes): self._get_received_count() # print self._temp_count if self._temp_count > self._received_count: return self._db.begin() self._temp_count = self._received_count try: changes_list = [] tbl_name = "" auto_id = 0 is_insert = False for change in changes: # print change.to_structure() # Update if change.action == "1": if is_insert: # print "inerst 1 ------------- " self._execute_insert_statement(changes_list) is_insert = False changes_list = [] # print "update 1 ---------------" self._execute_update_statement(change) else: if is_insert is False: is_insert = True auto_id = change.tbl_auto_id tbl_name = change.tbl_name if auto_id != change.tbl_auto_id or tbl_name != change.tbl_name: # print "insert 2 ---------------" self._execute_insert_statement(changes_list) changes_list = [] auto_id = change.tbl_auto_id tbl_name = change.tbl_name changes_list.append(change) if is_insert: # print "insert 3 -------------------------" self._execute_insert_statement(changes_list, error_ok=True) changes_list = [] # print "audit_trail_id updated ", self._temp_count, self._type update_traild_id(self._db, self._temp_count, self._type) self._received_count = self._temp_count self._db.commit() # self._temp_count = 0 except Exception, e: # print(traceback.format_exc()) print e logger.logclient("error", "client replication base", e) logger.logclient("error", "client replication base", str(traceback.format_exc())) self._temp_count = self._received_count self._db.rollback()
def connect(self): assert self._connection is None try: connection = self._mysql.get_db connection.autocommit(True) self._connection = connection logger.logclient("info", "database connection initiate", "") return self._connection except Exception, e: logger.logclient("error", "database.py-connect", e)
def _execute_update_statement(self, change): auto_id = self._auto_id_columns.get(change.tbl_name) assert auto_id is not None val = change.value if val is not None: # val = "'" + change.value.replace("'", "\\'") + "'" query = "UPDATE %s SET %s = '%s' WHERE %s = %s;" % ( change.tbl_name, change.column_name, val, auto_id, change.tbl_auto_id) try: self._db.execute(query) except Exception, e: print e, logger.logclient("error", "client replication base", e)
def validate_user_rights(self, session_token, rcaller_name, is_mobile): if is_mobile is True: user_id, user_category_id = self.validate_session_token( session_token) return user_id, user_category_id caller_name = [str(x) for x in rcaller_name.split("/") if x != ""] caller_name = "/%s" % (caller_name[0]) try: user_id, user_category_id = self.validate_session_token( session_token) if user_id is not None: if user_category_id == 1: q = "select t2.form_url from tbl_form_category as t1 " + \ " inner join tbl_forms as t2 on t1.form_id = t2.form_id where " + \ " t1.user_category_id = 1 " + \ " and t2.form_url = %s " param = [caller_name] else: q = "select t3.form_url " + \ " from tbl_users as t1 " + \ " inner join tbl_user_group_forms as t2 on t1.user_group_id = t2.user_group_id " + \ " inner join tbl_forms as t3 on t2.form_id = t3.form_id " + \ " where t1.user_id = %s and t3.form_url = %s" param = [user_id, caller_name] if caller_name not in ("/welcome", "/home", "/profile", "/themes", "/reminders", "/escalations", "/message", "/notifications", "/view-profile", "/settings"): rows = self.select_one(q, param) if rows: if rows.get("form_url") == caller_name: return user_id, user_category_id else: return None, None else: return user_id, user_category_id else: return user_id, user_category_id except Exception, e: logger.logclient("error", "validate_rights", str(e)) raise fetch_error()
def append(self, table, column, value, condition, condition_val): try: rows = self.get_data(table, column, condition, condition_val) currentValue = rows[0][column] if currentValue is not None: newValue = currentValue + "," + str(value) else: newValue = str(value) columns = [column] values = [newValue] values += condition_val res = self.update(table, columns, values, condition) return res except Exception, e: logger.logclient( "error", "append", "table: %s, column:%s, value:%s" % (table, column, value)) logger.logclient("error", "append", str(e)) return False
def select_one(self, query, param=None): cursor = self.cursor() assert cursor is not None try: if param is None: cursor.execute(query) else: if type(param) is tuple: cursor.execute(query, param) elif type(param) is list: cursor.execute(query, param) else: cursor.execute(query) cursor.nextset() res = cursor.fetchone() cursor.nextset() logger.logclient("query", "select_one", "query: %s, param:%s" % (query, param)) return res except Exception, e: logger.logclient("error", "select_one", "query: %s, param:%s" % (query, param)) logger.logclient("error", "select_one", str(e)) raise fetch_error()
def bulk_update(self, table, columns, values, conditions): try: for outer_index, cond in enumerate(conditions): query = "UPDATE " + table + " set " for index, column in enumerate(columns): if values[outer_index][index] is not None: if (index < len(columns) - 1): query += column + " = '%s', " % ( values[outer_index][index]) else: query += column + " = '%s' " % ( values[outer_index][index]) query += " WHERE " + cond + "; " cursor = self.cursor() assert cursor is not None cursor.execute(query) cursor.nextset() logger.logclient("query", "bulk_update", "query: %s, param:%s" % (query, values)) return True except Exception, e: logger.logclient("error", "bulk_update", "query: %s, param:%s" % (query, values)) logger.logclient("error", "bulk_update", str(e)) return False
def call_proc_with_multiresult_set(self, procedure_name, args, expected_result_count): cursor = self.cursor() rows = [] assert cursor is not None try: if args is None: cursor.callproc(procedure_name) else: cursor.callproc(procedure_name, args) logger.logclient( "query", "call_proc_with_multiresult_set", "procedure: %s, param:%s" % (procedure_name, args)) rows = [] for c in cursor.stored_results(): cols = c.description if cols: cols = [x[0] for x in cols] else: cols = [] r = convert_to_dict(c.fetchall(), cols) rows.append(r) except Exception, e: logger.logclient( "error", "call_proc_with_multiresult_set", "procedure: %s, param:%s" % (procedure_name, args)) logger.logclient("error", "call_proc_with_multiresult_set", str(e))
def call_update_proc(self, procedure_name, args): cursor = self.cursor() assert cursor is not None try: if args is None: cursor.callproc(procedure_name) else: cursor.callproc(procedure_name, args) logger.logclient( "query", "call_update_proc", "procedure: %s, param:%s" % (procedure_name, args)) cursor.nextset() except Exception, e: logger.logclient( "error", "call_update_proc", "procedure: %s, param:%s" % (procedure_name, args)) logger.logclient("error", "call_update_proc", str(e)) raise process_procedure_error(procedure_name, args, e)
def update(self, table, columns, values, condition): query = "UPDATE " + table + " set " for index, column in enumerate(columns): if index < len(columns) - 1: query += column + " = %s, " else: query += column + " = %s " query += " WHERE " + condition try: status = self.execute(query, values) logger.logclient("query", "update", "query: %s, param:%s" % (query, values)) return status except Exception, e: logger.logclient("error", "update", "query: %s, param:%s" % (query, values)) logger.logclient("error", "update", str(e)) return False
def execute(self, query, param=None): cursor = self.cursor() assert cursor is not None try: if type(param) is tuple: cursor.execute(query, param) elif type(param) is list: cursor.execute(query, param) else: cursor.execute(query) logger.logclient("query", "execute", "query: %s, param:%s" % (query, param)) cursor.nextset() return True except Exception, e: logger.logclient("error", "execute", "query: %s, param:%s" % (query, param)) logger.logclient("error", "execute", str(e)) return False
def call_proc(self, procedure_name, args=None, columns=None): # columns no longer need here, so remove argument once removed from the reference place # args can be tuple/list e.g, (parm1, parm2)/[param1, param2] cursor = self.cursor() rows = [] assert cursor is not None try: if args is None: cursor.callproc(procedure_name) else: cursor.callproc(procedure_name, args) logger.logclient( "query", "call_proc", "procedure: %s, param:%s" % (procedure_name, args)) cols = cursor.description if cols: cols = [x[0] for x in cols] else: cols = [] for c in cursor.stored_results(): cols = c.description if cols: cols = [x[0] for x in cols] else: cols = [] r = convert_to_dict(c.fetchall(), cols) rows.append(r) # rows = list(cursor.fetchall()) # rows = convert_to_dict(cursor.fetchall(), cols) if len(rows) > 0: rows = rows[0] cursor.nextset() except Exception, e: logger.logclient( "error", "call_proc", "procedure: %s, param:%s" % (procedure_name, args)) logger.logclient("error", "call_proc", str(e)) raise process_procedure_error(procedure_name, args, e)
def bulk_insert(self, table, columns, valueList): stringValue = [] for i in range(len(columns)): stringValue.append('%s') if type(columns) is list: columns = ", ".join(columns) query = "INSERT INTO %s (%s) " % (table, columns) query += " VALUES (%s) " % (",".join(stringValue)) try: cursor = self.cursor() assert cursor is not None cursor.executemany(query, valueList) cursor.nextset() logger.logclient("query", "bulk_insert", "query: %s, param:%s" % (query, valueList)) return True except Exception, e: logger.logclient("error", "bulk_insert", "query: %s, param:%s" % (query, valueList)) logger.logclient("error", "bulk_insert", str(e)) return False
def call_insert_proc(self, procedure_name, args): cursor = self.cursor() assert cursor is not None new_id = None try: if args is None: cursor.callproc(procedure_name) else: cursor.callproc(procedure_name, args) logger.logclient( "query", "call_insert_proc", "procedure: %s, param:%s" % (procedure_name, args)) cursor.nextset() cursor.execute("SELECT LAST_INSERT_ID() as newid") r = cursor.fetchone() cursor.nextset() new_id = r["newid"] except Exception, e: logger.logclient( "error", "call_insert_proc", "procedure: %s, param:%s" % (procedure_name, args)) logger.logclient("error", "call_insert_proc", str(e)) raise process_procedure_error(procedure_name, args, e)
def execute_insert(self, query, param): cursor = self.cursor() assert cursor is not None try: if type(param) is tuple: cursor.execute(query, param) elif type(param) is list: cursor.execute(query, param) else: cursor.execute(query) cursor.nextset() no = int(cursor.lastrowid) logger.logclient("query", "execute_insert", "query: %s, param:%s" % (query, param)) if no == 0: return False else: return no except Exception, e: logger.logclient("error", "execute_insert", "query: %s, param:%s" % (query, param)) logger.logclient("error", "execute_insert", str(e)) return False
def insert(self, table, columns, values): # columns = ", ".join(columns) stringValue = [] for i in range(len(values)): stringValue.append('%s') if type(columns) is list: columns = ", ".join(columns) columns = "(%s)" % columns query = """INSERT INTO %s %s """ % (table, columns) query += " VALUES (%s) " % (",".join(stringValue)) try: n_id = int(self.execute_insert(query, values)) logger.logclient("query", "insert", "query: %s, param:%s" % (query, values)) return n_id if n_id == 0: return False except Exception, e: logger.logclient("error", "insert", "query: %s, param:%s" % (query, values)) logger.logclient("error", "insert", str(e)) return False
def _execute_insert_statement(self, changes, error_ok=False): assert (len(changes)) > 0 tbl_name = changes[0].tbl_name auto_id = self._auto_id_columns.get(tbl_name) # print tbl_name column_count = self._columns_count.get(tbl_name) column_count -= 1 # print column_count if tbl_name == "tbl_mapped_industries": pass # self._execute_insert_mapped_industry(changes) else: print error_ok assert auto_id is not None if error_ok: if column_count != len(changes): if tbl_name == "tbl_countries": for r in changes: self._execute_insert_statement([r]) return else: return else: if column_count != len(changes): return # columns = [x.column_name for x in changes] i_column = [] values = [] domain_id = None compliance_id = None r_country_id = None r_le_id = None r_client_d = None for x in changes: if x.value is None: # values.append('') pass else: i_column.append(x.column_name) values.append(str(x.value)) if tbl_name == "tbl_compliances" and x.column_name == "domain_id": domain_id = int(x.value) if tbl_name == "tbl_compliances" and x.column_name == "country_id": r_country_id = int(x.value) if tbl_name == "tbl_statutory_notifications" and x.column_name == "compliance_id": compliance_id = int(x.value) if tbl_name == "tbl_units" and x.column_name == "legal_entity_id": r_le_id = int(x.value) if tbl_name == "tbl_client_configuration" and x.column_name == "country_id": r_country_id = int(x.value) if tbl_name == "tbl_client_configuration" and x.column_name == "client_id": r_client_d = int(x.value) if tbl_name == "tbl_validity_date_settings" and x.column_name == "country_id": r_country_id = int(x.value) val = str(values)[1:-1] query = "INSERT INTO %s (%s, %s) VALUES(%s, %s)" % ( tbl_name, auto_id, ",".join(i_column), changes[0].tbl_auto_id, val) if tbl_name == "tbl_client_groups": query += " ON DUPLICATE KEY UPDATE email_id = values(email_id), total_view_licence = values(total_view_licence) ;" elif tbl_name == "tbl_legal_entities": query += " ON DUPLICATE KEY UPDATE legal_entity_name = values(legal_entity_name), " + \ " contract_from = values(contract_from), contract_to = values(contract_to), " + \ " logo = values(logo), logo_size = values(logo_size), file_space_limit = values(file_space_limit), " + \ " total_licence = values(total_licence), " + \ " is_closed = values(is_closed), closed_on = values(closed_on), " + \ " closed_by = values(closed_by), closed_remarks = values(closed_remarks)" elif tbl_name == "tbl_units": query += " ON DUPLICATE KEY UPDATE unit_name = values(unit_name), " + \ " unit_code = values(unit_code), geography_name = values(geography_name), " + \ " address = values(address), postal_code = values(postal_code) " elif tbl_name == "tbl_compliances": query += " ON DUPLICATE KEY UPDATE statutory_provision = values(statutory_provision), " + \ " compliance_task = values(compliance_task), document_name = values(document_name), " + \ " compliance_description = values(compliance_description), penal_consequences = values(penal_consequences), " + \ " reference_link = values(reference_link), frequency_id = values(frequency_id), " + \ " statutory_dates = values(statutory_dates), repeats_type_id = values(repeats_type_id), " + \ " duration_type_id = values(duration_type_id), repeats_every = values(repeats_every), " + \ " duration = values(duration), is_active = values(is_active), " + \ " format_file = values(format_file), format_file_size = values(format_file_size), " + \ " statutory_nature = values(statutory_nature), statutory_mapping = values(statutory_mapping)" elif tbl_name == "tbl_legal_entity_domains": query += "ON DUPLICATE KEY UPDATE count = values(count)" else: query += "" try: # print domain_id, self._domains if self._is_group: print "Replication for client ", self._client_id, self else: print "Replication for legal entity ", self._client_id, self # print tbl_name # print query if tbl_name == "tbl_client_groups": if self._is_group is False and self._group_id == changes[ 0].tbl_auto_id: self._db.execute(query) elif self._is_group: self._db.execute(query) elif tbl_name == "tbl_compliances": # print r_country_id, domain_id # print self._country_id, self._domains if r_country_id == self._country_id and domain_id in self._domains: self._db.execute(query) elif tbl_name == "tbl_statutory_notifications": if self.check_compliance_available_for_statutory_notification( compliance_id) is True: self._db.execute(query) elif tbl_name == "tbl_legal_entities": self._db.execute( "delete from tbl_legal_entity_domains where legal_entity_id = %s", [changes[0].tbl_auto_id]) self._db.execute("delete from tbl_client_configuration") if self._is_group is False and self._client_id == changes[ 0].tbl_auto_id: self._db.execute(query) elif self._is_group is True: self._db.execute(query) elif tbl_name == "tbl_units": self._db.execute( "delete from tbl_units_organizations where unit_id = %s", [changes[0].tbl_auto_id]) if self._is_group: self._db.execute(query) elif self._is_group is False and self._client_id == r_le_id: self._db.execute(query) elif tbl_name == "tbl_client_configuration": # print self._group_id, self._country_id # print r_client_d, r_country_id # print self._is_group if self._is_group and self._client_id == r_client_d: self._db.execute(query) elif self._is_group is False and self._group_id == r_client_d and self._country_id == r_country_id: self._db.execute(query) elif tbl_name == "tbl_countries": if self._is_group is False and self._country_id == changes[ 0].tbl_auto_id: self._db.execute(query) else: self._db.execute(query) elif tbl_name == "tbl_validity_date_settings": # print self._country_id, r_country_id if self._country_id == r_country_id: self._db.execute(query) else: self._db.execute(query) except Exception, e: pass print e logger.logclient("error", "client replication base", e)