[转] Python操作数据库将文件夹中所有csv文件导入MySQL，操作成功但数据库没有数据

任务：将文件夹中所有csv文件导入到MySQL数据库
参考：https://blog.csdn.net/u011410413/article/details/82913645

遇到的问题：显示运行成功但是数据库表格中内容为空
解决方案：转自：https://blog.csdn.net/GodLordGee/article/details/81533104
解决问题的关键是：执行完excute语句后，一定一定要记得写commit语句，不然的话，数据库插入操作是不会成功的！！！

最终：

# -*- coding: utf-8 -*-
# author：hyr time:2019/12/3
import pandas as pd
import csv
import numpy as np
import MySQLdb
import sys
import os


def csv_to_mysql(load_sql,db="pubchem"):
    try:
        cursor.execute(load_sql)
        # print("Successfully loaded the table from csv.")
    except Exception as e:
        print("Error:{}".format(str(e)))
        sys.exit(1)


if __name__ == "__main__":
    host = "localhost"
    user = "root"
    password = ""
    db = "pubchem"
    # 打开数据库连接
    db = MySQLdb.connect(host, user, password, db, charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    print("Connected to DB:{}".format(host))

    # loop files
    rootdir = 'H:/hyr/'
    listing = os.listdir(rootdir)
    for file_csv in listing:
        if file_csv.endswith('.csv'):
            file_name = rootdir + file_csv
            load_sql = """LOAD DATA INFILE '{}' INTO TABLE all_data FIELDS TERMINATED by ',' 
            optionally  enclosed by '"'  escaped by '"' lines terminated by  "\r\n";;""".format(file_name)
            csv_to_mysql(load_sql)
            print(str(file_csv)+'成功！')
    cursor.connection.commit()  # 执行commit操作，插入语句才能生效
    db.close()