class LogDatabase: def __init__(self, **keyVals): # saving database parameters self.dbParams = keyVals # table information self.tablePrefix = 'zipnish_' self.tables = ['spans', 'annotations'] # connect to database self.db = SimpleMysql(host=keyVals['host'], db=keyVals['db'], user=keyVals['user'], passwd=keyVals['passwd'], keep_alive=keyVals['keep_alive']) self.__create_tables() if 'truncate_tables' in keyVals and keyVals['truncate_tables'] == True: self.truncateTables() def __create_tables(self): spans_table_query = "CREATE TABLE IF NOT EXISTS zipnish_spans " \ "(span_id BIGINT NOT NULL, " \ "parent_id BIGINT, " \ "trace_id BIGINT NOT NULL, " \ "span_name VARCHAR(255) NOT NULL, " \ "debug SMALLINT NOT NULL, " \ "duration BIGINT, " \ "created_ts BIGINT);" span_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_id);" trace_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(trace_id);" span_name_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_name(64));" created_ts_index = "ALTER TABLE zipnish_spans ADD INDEX(created_ts);" annotations_table_query = "CREATE TABLE IF NOT EXISTS zipnish_annotations " \ "(span_id BIGINT NOT NULL, " \ "trace_id BIGINT NOT NULL, " \ "span_name VARCHAR(255) NOT NULL, " \ "service_name VARCHAR(255) NOT NULL, " \ "value TEXT, " \ "ipv4 INT, " \ "port INT, " \ "a_timestamp BIGINT NOT NULL, " \ "duration BIGINT);" span_id_key = "ALTER TABLE zipnish_annotations ADD FOREIGN KEY(span_id) " \ "REFERENCES zipnish_spans(span_id) ON DELETE CASCADE;" trace_id_indx = "ALTER TABLE zipnish_annotations ADD INDEX(trace_id);" span_name_index = "ALTER TABLE zipnish_annotations ADD INDEX(span_name(64));" value_index = "ALTER TABLE zipnish_annotations ADD INDEX(value(64));" a_timestamp_index = "ALTER TABLE zipnish_annotations ADD INDEX(a_timestamp);" queryes = [ spans_table_query, span_id_index0, trace_id_index0, span_name_index0, created_ts_index, annotations_table_query, span_id_key, trace_id_indx, span_name_index, value_index, a_timestamp_index ] stmt = "SHOW TABLES LIKE 'zipnish_%'" cursor = self.db.query(stmt) table_count = len(cursor.fetchall()) if table_count == 0: for query in queryes: self.db.query(query) self.db.commit() def getParams(self): return self.dbParams def getDB(self): return self.conn def insert(self, tableName, rows): table = self.tablePrefix + tableName if len(rows) > 0: for row in rows: self.db.insert(table, row) self.db.commit() # truncate data in tables related to our application def truncateTables(self): print 'Truncating Tables:' if self.db is not None and self.db.is_open(): for tableName in self.tables: # table prefix + table name table = self.tablePrefix + tableName print 'truncating table -> ' + table # delete table, and commit changes to database self.db.delete(table) self.db.commit()
class LogDatabase: def __init__(self, **keyVals): # saving database parameters self.dbParams = keyVals # table information self.tablePrefix = 'zipnish_' self.tables = ['spans', 'annotations'] # connect to database self.db = SimpleMysql( host=keyVals['host'], db=keyVals['db'], user=keyVals['user'], passwd=keyVals['passwd'], keep_alive=keyVals['keep_alive'] ) self.__create_tables() if 'truncate_tables' in keyVals and keyVals['truncate_tables'] == True: self.truncateTables() def __create_tables(self): spans_table_query = "CREATE TABLE IF NOT EXISTS zipnish_spans " \ "(span_id BIGINT NOT NULL, " \ "parent_id BIGINT, " \ "trace_id BIGINT NOT NULL, " \ "span_name VARCHAR(255) NOT NULL, " \ "debug SMALLINT NOT NULL, " \ "duration BIGINT, " \ "created_ts BIGINT);" span_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_id);" trace_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(trace_id);" span_name_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_name(64));" created_ts_index = "ALTER TABLE zipnish_spans ADD INDEX(created_ts);" annotations_table_query = "CREATE TABLE IF NOT EXISTS zipnish_annotations " \ "(span_id BIGINT NOT NULL, " \ "trace_id BIGINT NOT NULL, " \ "span_name VARCHAR(255) NOT NULL, " \ "service_name VARCHAR(255) NOT NULL, " \ "value TEXT, " \ "ipv4 INT, " \ "port INT, " \ "a_timestamp BIGINT NOT NULL, " \ "duration BIGINT);" span_id_key = "ALTER TABLE zipnish_annotations ADD FOREIGN KEY(span_id) " \ "REFERENCES zipnish_spans(span_id) ON DELETE CASCADE;" trace_id_indx = "ALTER TABLE zipnish_annotations ADD INDEX(trace_id);" span_name_index = "ALTER TABLE zipnish_annotations ADD INDEX(span_name(64));" value_index = "ALTER TABLE zipnish_annotations ADD INDEX(value(64));" a_timestamp_index = "ALTER TABLE zipnish_annotations ADD INDEX(a_timestamp);" queryes = [spans_table_query, span_id_index0, trace_id_index0, span_name_index0, created_ts_index, annotations_table_query, span_id_key, trace_id_indx, span_name_index, value_index, a_timestamp_index] stmt = "SHOW TABLES LIKE 'zipnish_%'" cursor = self.db.query(stmt) table_count = len(cursor.fetchall()) if table_count == 0: for query in queryes: self.db.query(query) self.db.commit() def getParams(self): return self.dbParams def getDB(self): return self.conn def insert(self, tableName, rows): table = self.tablePrefix + tableName if len(rows) > 0: for row in rows: self.db.insert(table, row) self.db.commit() # truncate data in tables related to our application def truncateTables(self): print 'Truncating Tables:' if self.db is not None and self.db.is_open(): for tableName in self.tables: # table prefix + table name table = self.tablePrefix + tableName print 'truncating table -> ' + table # delete table, and commit changes to database self.db.delete(table) self.db.commit()
if __name__ == '__main__': db = SimpleMysql( host="127.0.0.1", db="test", user="******", port=3306, passwd="", keep_alive=True # try and reconnect timedout mysql connections? ) # db.insert("person", {'name': 'zhouyu', 'age': randint(1, 100)}) # print db.lastId() # # db.commit() db.delete("person", ("age between %s and %s", [18, 19])) print db.lastQuery() rows = db.getAll("person", ["id", "name", "age"], ("name=%s and age>%s", ["zhouyu", 10])) print rows for row in rows: print row.id, row.name, row.age # person = db.getOne("person", ['id', 'name', 'age']) person2 = db.getAll("person", ['id', 'name', 'age'], None, ["id", "asc"]) print person2 print len(person2)