swdz_crm/pyshell/mysql_sync_psql.py

# encoding: utf-8
import sys
import pymysql.cursors
import psycopg2
import time

reload(sys)
sys.setdefaultencoding('utf-8')


def mysql_connect(host, db, user, password):
    try:
        mysql_conn = pymysql.connect(host=host,
                                     user=user,
                                     password=password,
                                     db=db,
                                     charset='utf8mb4',
                                     cursorclass=pymysql.cursors.DictCursor)
        return mysql_conn
    except:
        print('连接mysql失败！')
        pass


def postgres_connect(host, port, db, user, password):
    try:
        postgres_conn = psycopg2.connect(database=db, user=user, password=password, host=host, port=port)
        return postgres_conn
    except:
        print('连接Postgres失败！')
        pass


if __name__ == '__main__':
    # 获取postgres连接
    postgres_conn = postgres_connect('127.0.0.1', '5432', 'nwaycc', 'postgres', 'Nway2017')

    # 获取mysql连接
    mysql_conn = mysql_connect('47.94.228.245', 'swdz_crm', 'dbuser', 'BXhKw6JRxMy9FTb2')

    ip = '172.31.20.133'
    # try:
    # 从mysql获取需要导入的电话号码
    mysql_cur = mysql_conn.cursor()
    mysql_sql_select = "SELECT phone,id from call_list WHERE sync = 0 and ip =  '" + ip + "' "
    mysql_cur.execute(mysql_sql_select)
    nums = mysql_cur.fetchall()

    postgres_cur = postgres_conn.cursor()
    group_name = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    # 生成号码组
    if nums:
        #检查号码组是否存在，不存在则新建
        psql_select_group = "Select id from ai_number_group where group_name = '" + group_name + "'"
        postgres_cur.execute(psql_select_group)
        group_id = postgres_cur.fetchone()

        if (group_id == None):
            psql_insert_group = "INSERT INTO ai_number_group(group_name, own_id) VALUES ('" + group_name + "', 1) RETURNING id"
            postgres_cur.execute(psql_insert_group)
            postgres_conn.commit()
            group_id = postgres_cur.fetchone()[0]
        else:
            group_id = group_id[0]

        # 插入号码
        for num in nums:
            # 检查号码是否已经导入
            psql_select_num = "SELECT id from ai_numbers WHERE dst_number = '" + num['phone'] + "' "
            postgres_cur.execute(psql_select_num)
            rows = postgres_cur.fetchone()
            if (rows):
                num_id = rows[0]
            else:
                # 插入号码到ai_numbers
                psql_insert_num = "INSERT INTO ai_numbers(dst_number) VALUES ('" + num['phone'] + "') RETURNING id"
                postgres_cur.execute(psql_insert_num)
                postgres_conn.commit()
                num_id = postgres_cur.fetchone()[0]

            # 将导入的号码添加到号码组
            psql_insert_group_map = "INSERT INTO ai_number_group_map(group_id, number_id) VALUES ('" + str(
                group_id) + "', '" + str(num_id) + "')"
            postgres_cur.execute(psql_insert_group_map)
            postgres_conn.commit()

            # 更新mysql中该号码状态
            mysql_sql_update = "UPDATE call_list SET sync=1 WHERE id= %s"
            mysql_cur.execute(mysql_sql_update % (num['id']))
            mysql_conn.commit()

        mysql_conn.close()
        postgres_conn.close()
# except:
#     print "Error: insert data error"
#     pass