#-*- encoding:utf-8 -*-

'''
@Author : dingjiawen
@Date : 2023/5/17 21:45
@Usage : 
@Desc :  操作mysql的demo
'''
'''
参考:
[1] https://zhuanlan.zhihu.com/p/397765212
'''

import pymysql
import time

def test_db():
    # 打开数据库连接
    try:
        db = pymysql.connect(host='Ding202', user='root', passwd='123456', port=3306,database='rt_phm')
        print('连接成功！')
    except:
        print('something wrong!')

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT * from gas_table_process")

    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 打印结果
        print('数据查询成功！')
        print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
                (fname, lname, age, sex, income))


    # 使用 fetchone() 方法获取单条数据.
    # data = cursor.fetchone()

    # print("Database version : %s " % data)

    # 关闭数据库连接
    db.close()


# 装饰器，计算插入50000条数据需要的时间
def timer(func):
    def decor(*args):
        start_time = time.time()
        func(*args)
        end_time = time.time()
        d_time = end_time - start_time
        print("the running time is : ", d_time)

    return decor

# 批量插入
@timer
def add_test_users():
    usersvalues = []
    for num in range(1, 50000):
        usersvalues.append((num,"11",num))  # 注意要用两个括号扩起来

    conn = pymysql.connect(host='Ding202', port=3306, user='root', password='123456', database='rt_phm', charset='utf8')
    cs = conn.cursor()  # 获取光标
    # 注意这里使用的是executemany而不是execute，下边有对executemany的详细说明
    cs.executemany("insert into test(age,name,id) values(%s,%s,%s)", usersvalues)

    conn.commit()
    cs.close()
    conn.close()
    print('OK')


if __name__ == '__main__':
    add_test_users()