pymysql insert example

gistfile1.txt

def insert_mysql(list_items):
    if len(list_items) == 0:
        print('input data illegal')
        return
    config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '********',
        'db': 'lagouInfo',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor,
    }

    # Connect to the database
    db = pymysql.connect(**config)

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 插入语句
    # data = {
    #     'title': '1',
    #     'address': '2',
    #     'salary': '3',
    #     'workYear': '4',
    #     'company': '5',
    #     'financeStage': '0'
    # }
    table = 'info'
    try:
        for item in list_items:
            data = item.__dict__
            keys = ', '.join(data.keys())
            values = ', '.join(['%s'] * len(data))
            sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
            # 执行sql语句
            cursor.execute(sql, tuple(data.values()))
            # 提交到数据库执行
        db.commit()

    except Exception as e:
        print('发生错误' + str(e))
        # 如果发生错误则回滚
        db.rollback()
    # 关闭数据库连接
    db.close()