将CSV导入Mysql

一、安装

菜鸟网-下载
https://www.cnblogs.com/laumians-notes/p/9069498.html

二、复习

Onedrive中的Mysql文件夹里

三、利用python连接Mysql

3.1 将csv文件导入mysql

转载： https://blog.csdn.net/shao824714565/article/details/86074257

# -*- coding:utf-8 -*-
import csv
import os
import numpy as np
import pandas as pd
import pymysql
from pymysql import connect

class CsvToMysql(object):
    def __init__(self, hostname, port, user, passwd, db):
        self.dbname = db
        self.conn = connect(host=hostname, port=port, user=user, passwd=passwd, db=db)
        self.cursor = self.conn.cursor()

    def read_csv(self,filename):
        df = pd.read_csv(filename, keep_default_na=False, encoding='utf-8')
        table_name = '`'+os.path.split(filename)[-1].split('.')[0].replace(' ', '_') + '`'
        self.csv2mysql(db_name=self.dbname,table_name=table_name, df=df )

    def make_table_sql(self,df):
        #将csv中的字段类型转换成mysql中的字段类型
        columns = df.columns.tolist()
        types = df.ftypes
        make_table = []
        make_field = []
        for item in columns:
            item1 = '`'+item.replace(' ', '_').replace(':','')+'`'
            if 'int' in types[item]:
                char = item1 + ' INT'
            elif 'float' in types[item]:
                char = item1 +' FLOAT'
            elif 'object' in types[item]:
                char = item1 +' VARCHAR(255)'
            elif 'datetime' in types[item]:
                char = item1 + ' DATETIME'
            else:
                char = item1 + ' VARCHAR(255)'
                
            #char = item1 + ' VARCHAR(255)'
            make_table.append(char)
            make_field.append(item1)

        return ','.join(make_table), ','.join(make_field)

    def csv2mysql(self,db_name,table_name,df):
        field1, field2 = self.make_table_sql(df)
        print( "create table {} (id int AUTO_INCREMENT not null primary key, {})".format(table_name,field1))             
        self.cursor.execute('drop table if exists {}'.format(table_name))
        self.cursor.execute("create table {} (id int AUTO_INCREMENT not null primary key,{})".format(table_name, field1))
        values = df.values.tolist()
        s = ','.join(['%s' for _ in range(len(df.columns))])
        try:
            print( len(values[0]),len(s.split(',')))
            print('insert into {}({}) values ({})'.format(table_name, field2, s), values[0])
            self.cursor.executemany('insert into {}({}) values ({})'.format(table_name, field2, s), values)
        except Exception as e:
            print(e.message)
        finally:
            self.conn.commit()

# if __name__ == "__main__":
#     hostname = 'localhost'
#     port = 3306
#     user = 'root'
#     passwd = 'cxxxxxxxxx'
#     db = 'xxxxxxxxxxxxxxxx'
#     M = CsvToMysql(hostname=hostname, port=port, user=user, passwd=passwd, db=db)
#     #csv文件目录
#     dir = 'C:\dataxxxxxxx'
#     file_list = os.listdir(dir)
#     for i in range(len(file_list)):
#         file_path = os.path.join(dir, file_list[i])
#         if os.path.isfile(file_path):
#             M.read_csv(file_path)                

上面是适用于导入n个csv文件
如果只要导入一个csv文件

#实例化
c2m = CsvToMysql(hostname='localhost',port=3306,user='root',passwd='cxxxx',db='test_database')
#调用类中的read_csv()方法
c2m.read_csv(r'C:\Users\81284\OneDrive\Mysql\Mysql-python\falldeteciton.csv')