#!/usr/bin/env python3
#-*- coding:utf-8 -*-
import pymysql, sqlite3
import os, time

def time_decorator(func):
  def exec_fun(*args, **kwargs):
    t = time.perf_counter()
    result = func(*args, **kwargs)
    print("function %s coast time:%f s" % (func.__name__, (time.perf_counter()-t)))
    return result
  return exec_fun

def exec_sql(conn, t_sql, **kwargs):
  result_of_sql = cur = fun = db = ''
  if kwargs:
    if kwargs['fun']:
      fun = kwargs['fun'].lower()
    if kwargs['db']:
      db = kwargs['db'].lower()
  if db == 'mysql':
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
  elif db == 'sqlite':
    cur = conn.cursor()
  else:
    print('need define db type!')
    return "fail"
  cur.execute(t_sql)
  if fun == 'select':
    result_of_sql = cur.fetchall()
  elif fun == 'exec':
    try:
      conn.commit()
    except Exception as e:
      print(e)
      conn.rollback()
      return "fail"
  cur.close()
  return result_of_sql


@time_decorator
def drop_sqlite_table(table_name):
  t_sql = ("drop table if exists %s;" % table_name)
  res = exec_sql(sqlite_conn, t_sql, fun='exec', db='sqlite')
  if res == 'fail':
    return 'fail'
  t_sql = "CREATE TABLE IF NOT EXISTS %s \
          (id INTEGER PRIMARY KEY,busniess_name STRING,\
          app_name STRING,app_cluster STRING,\
          app_ip STRING,data_src STRING,app_pgm STRING);"
  res = exec_sql(sqlite_conn, (t_sql % table_name), fun='exec', db='sqlite')
  if res == 'fail':
    return 'fail'


@time_decorator
def insert_sqlite_value(table_name, data):
  for i in data:
    t_sql = "insert into %s(%s) values(%s);"
    v1, v2 = '', ''
    for j in i :
      if not v1 and not v2:
        v1, v2 = j, '"'+i[j]+'"'
      else:
        v1, v2 = v1+','+j, v2+','+'"'+i[j]+'"'
    t_sql = (t_sql % (table_name, v1, v2))
    if exec_sql(sqlite_conn, t_sql, fun='exec', db='sqlite') == 'fail':
      return 'fail'

  t_sql = "select count(*) from app_info_temp;"
  try:
    counts = exec_sql(sqlite_conn, t_sql, fun='select', db='sqlite')
    counts = counts[0][0]
  except Exception as e:
    print(e)
    return "fail"
  print("get %s rows!" % (counts))
  return counts


@time_decorator
def data_convert(mysql_conn, sqlite_conn, save_table_name):
  t_sql = "select busniess_name, app_name, app_cluster, app_ip, data_src, \
          (select distinct(app_pgm) from syseasyopspgm as a \
          where a.busniess_name = syseasyops.busniess_name and a.app_name = syseasyops.app_name limit 1) as app_pgm\
          from syseasyops;"
  mysql_res = exec_sql(mysql_conn, t_sql, fun='select', db='mysql')
  mysql_conn.close()
  if drop_sqlite_table(save_table_name) == 'fail':
    print('drop sqlite table %s fail!' % save_table_name)
    return 'fail'

  if insert_sqlite_value(save_table_name, mysql_res) == 'fail':
    print('insert sqlite table %s fail!' % save_table_name)
    return 'fail'

if __name__ == '__main__':
  print("start_time==>%s" % (time.asctime(time.localtime(time.time()))))
  mysql_conn = pymysql.connect(host='db.test.com',\
               user='test',\
               passwd='test',\
               db='cmdb',\
               port=3306, \
               charset='utf8')
  sqlite_conn = sqlite3.connect("app-info.db")
  data_convert(mysql_conn, sqlite_conn, 'app_info')
  print("end_time==>%s" % (time.asctime(time.localtime(time.time()))))