python读取csv文件内容，并保存到数据库中

最新推荐文章于 2024-05-05 07:05:24 发布

风之梦丽

最新推荐文章于 2024-05-05 07:05:24 发布

阅读量2k

点赞数

分类专栏： Python 文章标签： python 数据库开发语言

本文链接：https://blog.csdn.net/fld4123/article/details/127733377

版权

Python 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

# -*- coding: utf-8 -*-
#python读取csv文件内容，并保存到数据库中
import csv
import time
import pymysql
import emoji

num = 0
file_path = r'H:\20221103174026.csv'   # r对路径进行转义，windows需要
##errors='ignore' 出现错误时忽略
with open(file_path, 'r' , errors='ignore') as csvfile:
    reader = csv.reader(_.replace('\x00', '') for _ in csvfile)
    #reader = csv.reader(csvfile)
    for row in reader:
        # the first row is table header
        num = num+1
        if num >= 18429:
            pro_name = row[1]
            google_img = emoji.demojize(row[2].replace("'", ""))
            pro_type = emoji.demojize(row[3].replace("'", ""))
            pro_list = pro_name.split('^')
            print(pro_list)
            if len(pro_list) > 1:
                pro_id = pro_list[len(pro_list)-1]
            else:
                pro_id = ''
            pro_title = emoji.demojize(pro_list[0].replace("'", ""))
            if str(pro_id) != '':
                db = pymysql.connect(host="localhost",
                                     db="test",
                                     user="root",
                                     password="root123",
                                     port=3306,
                                     charset="utf8")
                cursor = db.cursor()
                sql = "select count(1) as count from google_img where pro_id='" + pro_id + "' and pro_title = '" + pro_title + "'" + " and type = '" + pro_type + "'"
                print(sql)
                cursor.execute(sql)
                res = cursor.fetchone()
                if res[0] == 0:
                    add_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                    print(add_time)
                    sql = """insert into google_img(pro_id,pro_title,google_img,type,add_time) 
                                values ('%s','%s','%s','%s','%s')"""  % (str(pro_id), str(pro_title), str(google_img), str(pro_type), str(add_time))
                    print(sql)
                    cursor.execute(sql)
                    pl_id = db.insert_id()
                    print(str(num) + '==' + str(pro_id) + '==' + str(pro_title) + '==' + str(pro_type) + '==' + '===保存成功')
                else:
                    print(str(num) + '==' + str(pro_id) + '==' + str(pro_title) + '==' + str(pro_type) + '===已存在')
                cursor.close()
                db.commit()
                db.close()