游标操作mysql数据库

# import pymysql
# db=pymysql.connect(
#     host='localhost',
#     user='?',
#     password='?',
#     database='mysql',
#     charset='utf8mb4',
#     cursorclass=pymysql.cursors.DictCursor#游标类型，默认为元组，现在设置为字典类型
# )
# cursor = db.cursor()#获取操作游标
# sql='select * from work'#查询
# try:
#     print(sql)
#     cursor.execute(sql)
#     res=cursor.fetchall()#获取所有记录列表
#     print(res)
# except:
#     print('fail')
# db.close()
#

import pymysql
def open():
    db=pymysql.connect(
        host='localhost',
        user='?',
        password='?',
        database='mysql',
        charset='utf8',
        # cursorclass=pymysql.cursors.DictCursor  # 游标类型，默认为元组，现在设置为字典类型
    )
    return db

def query1(sql):
    db = open()#打开数据库连接
    cursor = db.cursor()  # 使用cursor（）方法获取游标
    cursor.execute(sql)  # 执行sql查询语句
    result = cursor.fetchall()  # 记录查询结果
    cursor.close()  # 关闭游标
    db.close()  # 关闭数据库连接
    return result  # 返回查询结果

def insert(sql, values):
    db = open()
    cursor = db.cursor()
    cursor.executemany(sql, values)
    db.commit()
    cursor.close()
    db.close()
    return cursor.rowcount

def delete(sql, values):
    db = open()
    cursor = db.cursor()
    cursor.execute(sql, values)
    db.commit()
    cursor.close()
    db.close()
    return cursor.rowcount

def update(sql, values):
    db = open()
    cursor = db.cursor()
    cursor.execute(sql, values)
    db.commit()
    cursor.close()
    db.close()
    return cursor.rowcount

# if __name__ == "__main__": #4.更新数据
#     try:
#         sql1 = "update work set name = %s where rate =%s"
#         val = ('1', '6.00%')
#         print(update(sql1, val),"successfuly updata")
#     except:
#         print("fail")

if __name__ == "__main__":  #3.删除数据
    try:  #1
        sql1 = "delete from work where name = %s"
        # sql1 = "delete from work where rate = %s"
        keys = "c++"
        print(delete(sql1, keys),"successfully del")
    except:
        print("fail")
#     try:  #2
#         sql1 = "delete from work where name = %s"
#         sql2 = "select * from work"
#         tuple = query1(sql2)
#         # sql1 = "delete from work where rate = %s"
#         for i in tuple:
#             if(i[0]=='c'):
#                 print(delete(sql1, i[0]),"successfully del")
#     except:
#         print("fail")

# if __name__ == "__main__":  #2.添加数据
#     try:
#         sql1 = "insert into work (name,rate) values (%s,%s)"
#         val = [("c", "5.00%"),("c++", "6.00%")]
#         print(insert(sql1,val),"successfully insert")
#     except:
#         print("fail!")

# if __name__ == "__main__":  #1.查询数据
#     all_user_info=[]
#     try:
#         sql2 = "select * from work"
#         tuple = query1(sql2)
#         # print(tuple)
#         for index in range(len(tuple)):
#             temp_dict = dict()
#             temp_dict["userName"] = tuple[index][0]
#             temp_dict["userPwd"] = tuple[index][1]
#             all_user_info.append(temp_dict)
#         print(all_user_info)
#     except:
#         print("fail")