#coding:utf-8
import pymysql
import datetime

class face_mysql:
    def __init__(self):
        pass
    #设置数据库和密码
    def conn_mysql(self):
        # db = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='mysql', charset="utf8")
        db = pymysql.connect(host='172.20.71.35', port=3306, user='root', passwd='', db='mysql', charset="utf8")
        return db

    def insert_facejson(self,pic_name,pic_json,uid,ugroup):
    # def insert_facejson(self, pic_name, pic_json):
        db = self.conn_mysql()
        cursor = db.cursor()
        dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # sql = "insert into face_json(json,pic_name,date,state) values('%s' ,'%s','%s','%d') ;" % (
        #     pic_json, pic_name, dt, 1)
        sql = "insert into face_json(json,pic_name,date,state,uid,ugroup) values('%s' ,'%s','%s','%d','%s','%s') ;" % (
            pic_json, pic_name, dt, 1, uid, ugroup)
        # print("sql=",sql)
        try:
            #执行sql语句
            cursor.execute(sql)
            #提交给数据库执行
            lastid = int(cursor.lastrowid)
            db.commit()
        except:
            #Rollback in case there is any error
            db.rollback()
        db.close()
        return lastid

    def findall_facejson(self,ugroup):
        db = self.conn_mysql()
        cursor = db.cursor()
        sql = "select * from face_json where state=1 and ugroup=%s;"%(ugroup)
        try:
            cursor.execute(sql)
            results = cursor.fetchall()
            return results
        except:
            print("Error:unable to fetch data")
        db.close()