class SetData():
def __init__(self):
dbc = ini.get_items('MySQL')
self.db = SimpleMysql(host=dbc['host'],
db=dbc['db'],
user=dbc['user'],
passwd=dbc['passwd'])
def to_mysql(self, function, kwargs, time, table):
if not table: return
l, id = self.db.IsExistsinSQL(table, dict(kwargs))
if l == 0:
result = self.db.insert(table, dict(kwargs))
elif l == 1:
result = self.db.update(table, dict(kwargs), id)
self.db.commit()
logging.info('Complete sync - function: %s, table: %s, result: %s' %
(function, table, result))
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()
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_index = "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_index, 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 get_params(self):
return self.dbParams
def insert(self, table_name, rows):
table = self.tablePrefix + table_name
for row in rows:
self.db.insert(table, row)
self.db.commit()
def run_sql(query, *args):
db = SimpleMysql(**__DB_PARAMS__)
db.query(query, args)
db.commit()
db.conn.close()
#!/usr/bin/env python
# coding: utf-8
import sys
from simplemysql import SimpleMysql
if __name__ == '__main__':
reload(sys)
sys.setdefaultencoding('utf-8')
print sys.argv[1]
db = SimpleMysql(host='127.0.0.1',
user='******',
passwd='root',
db='databank')
for i in open(sys.argv[1]):
try:
t = i.strip('\r\n').split('\t')
r = {}
r['username'] = t[0]
r['password'] = t[1]
r['email'] = t[2]
db.insert('aipai', r)
except Exception, e:
continue
db.commit()
#!/usr/bin/env python
# coding: utf-8
import sys
from simplemysql import SimpleMysql
if __name__ == '__main__':
reload(sys)
sys.setdefaultencoding('utf-8')
print sys.argv[1]
db = SimpleMysql(host='127.0.0.1', user='******', passwd='root', db='databank')
for i in open(sys.argv[1]):
try:
t = i.strip('\r\n').split('\t')
r = {}
r['username'] = t[0]
r['password'] = t[1]
r['email'] = t[2]
db.insert('aipai', r)
except Exception, e:
continue
db.commit()
