import pymysql
'''
需求：
有以下员工信息表

CREATE TABLE EmployeeInfo (
  staff_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8_bin DEFAULT NULL,
  age int(11) DEFAULT NULL,
  phone varchar(255) COLLATE utf8_bin DEFAULT NULL,
  dept varchar(255) COLLATE utf8_bin DEFAULT NULL,
  enroll_date datetime DEFAULT NULL,
  PRIMARY KEY (`staff_id`)
)

现需要对这个员工信息表，实现增删改查操作

可进行模糊查询，语法至少支持下面3种:
　　select name,age from staff_table where age > 22
　　select  * from staff_table where dept = "IT"
    select  * from staff_table where enroll_date like "2013"
查到的信息，打印后，最后面还要显示查到的条数
可创建新员工纪录，以phone做唯一键，staff_id需自增
可删除指定员工信息纪录，输入员工id，即可删除
可修改员工信息，语法如下:
　　UPDATE staff_table SET dept="Market" WHERE where dept = "IT"

'''



#连接sql
def connMySql(host,user,password,db,charset,*sqlStatement):
    # 数据库连接
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        db=db,
        charset=charset,
    )
    #使用cursor()方法获取操作游标
    cursor = conn.cursor()


    sqlStatement_len = len(sqlStatement)-1
    results = []
    result_count = 0
    i = 0
    if i<=sqlStatement_len:
        for j in sqlStatement:

            i+=1
            # 使用execute方法执行SQL语句
            a=cursor.execute(j)
            # 使用 fetchone() 方法获取一条数据
            results_line = cursor.fetchall()
            #print('line-->',results_line)
            results.append(results_line)
            #print('results-->',results)
            conn.commit()

    for i in results:
        result_count += len(i)
        #print("result_count--->",result_count)
    print("查到的总条数为：%s"%result_count)

    conn.close()

    return results




def deleteEmployeeInfo(staff_id):
    deleteEmployeeInfoSql = 'delete from EmployeeInfo where staff_id = %s' %staff_id
    print(deleteEmployeeInfoSql)
    return deleteEmployeeInfoSql
#deleteEmployeeInfo(1)


def updateEmployeeInfo(modifiedField,modifiedFieldValue,conditionField,conditionFieldValue):
    updateEmployeeInfoSql = "update EmployeeInfo set %s=%s WHERE  %s = '%s'"%(modifiedField,modifiedFieldValue,conditionField,conditionFieldValue)
    #print(updateEmployeeInfoSql)
    return updateEmployeeInfoSql
#updateEmployeeInfo('phone','15652940958','name','jock')

sql_createTable = """
CREATE TABLE EmployeeInfo (
  staff_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8_bin DEFAULT NULL,
  age int(11) DEFAULT NULL,
  phone varchar(255) COLLATE utf8_bin DEFAULT NULL,
  dept varchar(255) COLLATE utf8_bin DEFAULT NULL,
  enroll_date datetime DEFAULT NULL,
  PRIMARY KEY (`staff_id`)
)
"""
sql_queryByAge = 'select name,age from EmployeeInfo where age > 22'
sql_queryByDept='select  * from EmployeeInfo where dept = "IT"'
args = '%%'+'2018'+'%%'
sql_queryByEnrollDate = """select  * from EmployeeInfo where enroll_date like '%s' """%args
sql_addEmployeeInfo = 'insert into EmployeeInfo(name,age,phone,dept,enroll_date) VALUES ("Alex",20,"15330015692","IT","2018-10-11"),("jock",19,"153300121209","HR","2017-06-18"),("tom",28,"15330059254","Sales","2017-10-11"),("jexi",21,"15338912120","IT","2018-10-11") '


#调用函数方法
results = connMySql('192.168.0.75','writedafy','writeDafy!@#$','PT','utf8',updateEmployeeInfo('phone','15652940969','name',"jock"))
print(results)