import pymysql
from pymysql.cursors import DictCursor

db_config = {
    'host': 'home.rogersun.cn',
    'user': 'root',
    'password': 'Sxzgx1209',
    'database': 'file_md5'
}

db_connection = pymysql.Connect(**db_config)
db_cursor = db_connection.cursor(cursor=DictCursor)


def insert_data(_data):
    insert_sql = "INSERT INTO files VALUES (NULL, %s, %s, %s, %s);"
    db_cursor.execute(insert_sql, _data)
    db_connection.commit()


def get_md5_by_path(_path):
    get_md5_sql = "SELECT md5 FROM files WHERE path = %s;"
    db_cursor.execute(get_md5_sql, (_path,))
    r = db_cursor.fetchone()
    return r


def get_path_by_cate(_cate):
    get_path_sql = "SELECT SUBSTRING(path,3) as path FROM files WHERE cate = %s;"
    db_cursor.execute(get_path_sql, (_cate,))
    r = db_cursor.fetchall()
    file_list = [f['path'] for f in r]
    print('get_path_by_cate查询完成')
    return file_list


def is_exist(_md5):
    is_exist_sql = "SELECT * FROM files WHERE md5 = %s;"
    db_cursor.execute(is_exist_sql, (_md5,))
    r = db_cursor.fetchone()
    return r