python导入csv文件数据并处理数据存入数据库``’

初学python，安装python
引入pymysql:Python解决ModuleNotFoundError: No module named 'pymysql'的问题

1、用管理员身份运行CMD.exe，然后查看python的安装路径

2、切换到python路径，在Scripts目录下执行pip install pymysql命令，由于我的已经安装过，所以显示的是pymysql的version。

3、如果要删除pymysql，在Scripts目录下执行pip uninstall pymysql命令，看清楚，是在Scripts目录下。输入y，回车即可。

产品表数据处理

import csv      # csv
import codecs   # csv读取
import re       # 正则替换
import json     # json数据
import pymysql  # mysql
from pymysql.converters import escape_string    #转义方法转义后再进行保存
#pymysql.install_as_MySQLdb()    # 返回字典类型的数据 列表数据不引入
#import MySQLdb.cursors          # 返回字典类型的数据 列表数据不引入
class DoMysql:
    #初始化
    def __init__(self):
        #创建连接
        self.conn = pymysql.Connect(
            host 		= 'host',
            port 		= port,
            user 		= 'user',
            password 	= 'password',
            db 			= 'db',
            charset 	= 'utf8',
            cursorclass = pymysql.cursors.DictCursor  #以字典的形式返回数据
        )
        #获取游标
        self.cursor = self.conn.cursor()

    #返回单条数据
    def fetchOne(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    #返回多条数据
    def fetchAll(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    #插入一条数据
    def insert_one(self,sql):
        result = self.cursor.execute(sql)
        self.conn.commit()
        return result

    #插入多条数据
    def insert_many(self,sql,datas):
        result = self.cursor.executemany(sql,datas)
        self.conn.commit()
        return result

    #更新数据
    def update(self,sql):
        result = self.cursor.execute(sql)
        self.conn.commit()
        return result

    #清空数据
    def truncateTable(self,sql):
        result = self.cursor.execute(sql)
        self.conn.commit()
        return result

    #关闭连接
    def close(self):
        self.cursor.close()
        self.conn.close()
""" #参数分别为：ip，用户名，密码，库名
    db  = MySQLdb.connect(host='host',user='user',password='password',db='db',charset='utf8',cursorclass = MySQLdb.cursors.DictCursor)
    con = db.cursor()"""

def getOneRow(rowDat):
    #return rowDat['CoverPicture']
    img     = rowDat['CoverPicture'].split('?')            # 图片以?分割
    Picture = rowDat['PicturesJson'].split('Picture":"')   # 多图片以?分割
    imgs    = ''
    i       = 0
    for pic in Picture:
        pic = pic.split('?')
        # 去除存在Thumbnail字段的图片
        if re.search('.*?"Thumbnail"', str(pic[0])) == None:
            if i == 0:
                imgs = imgs+pic[0]
            else:
                imgs = imgs+','+pic[0]
            i+=1
    """dats    = {
        'serial':           dat['GoodsNo'],
        'designation':      dat['Title'],
        'abstract':         dat['ZhaiYao'],
        'aurify':           dat['GoldTypeName'],
        'min_wgt':          dat['MinGoldWeight'],
        'max_wgt':          dat['MaxGoldWeight'],
        'category':         dat['CategoryName'],
        'gram_wage':        dat['SaleFeePrice'],
        'gram_add_wage':    dat['SaleOtherFeePrice'],
        'img':              img[0],
        'imgs':             imgs,
        'supplier':         2,
        'status':           1,
        'shelf':            0,
        'sell':             1,
    }"""
    indats    = (
        rowDat['GoodsNo'],
        rowDat['Title'],
        rowDat['ZhaiYao'],
        rowDat['GoldTypeName'],
        rowDat['MinGoldWeight'],
        rowDat['MaxGoldWeight'],
        rowDat['CategoryName'],
        rowDat['SaleFeePrice'],
        rowDat['SaleOtherFeePrice'],
        img[0],
        imgs,
        2,
        1,
        0,
        1,
    )
    return indats
    """#使用execute方法执行一条sql语句
    sql = "select * from a_article_copy where supplier = 2 and serial = '"+rowDat['GoodsNo']+"' limit 1"
    #使用ferchall获取数据
    data = mysql.fetchOne(sql)
    #data = connectDb('000',)
    if data == None:
        indats    = (
            rowDat['GoodsNo'],
            rowDat['Title'],
            rowDat['ZhaiYao'],
            rowDat['GoldTypeName'],
            rowDat['MinGoldWeight'],
            rowDat['MaxGoldWeight'],
            rowDat['CategoryName'],
            rowDat['SaleFeePrice'],
            rowDat['SaleOtherFeePrice'],
            img[0],
            imgs,
            2,
            1,
            0,
            1,
        )
        return {'type':'add','data':indats}
    else:
        designation = escape_string(rowDat['Title'])
        updats    = {
            'serial':           rowDat['GoodsNo'],
            'designation':      designation,
            'abstract':         rowDat['ZhaiYao'],
            'aurify':           rowDat['GoldTypeName'],
            'min_wgt':          rowDat['MinGoldWeight'],
            'max_wgt':          rowDat['MaxGoldWeight'],
            'category':         rowDat['CategoryName'],
            'gram_wage':        rowDat['SaleFeePrice'],
            'gram_add_wage':    rowDat['SaleOtherFeePrice'],
            'img':              img[0],
            'imgs':             imgs,
        }
        return {'type':'edit','data':updats}
        designation = escape_string(rowDat['Title'])
        sql = "update a_article_copy set serial='"+rowDat['GoodsNo']+"',designation='"  +designation
        sql +="',abstract='"     +rowDat['ZhaiYao']
        sql +="',aurify='"       +rowDat['GoldTypeName']
        sql +="',min_wgt='"      +rowDat['MinGoldWeight']
        sql +="',max_wgt='"      +rowDat['MaxGoldWeight']
        sql +="',category='"     +rowDat['CategoryName']
        sql +="',gram_wage='"    +rowDat['SaleFeePrice']
        sql +="',gram_add_wage='"+rowDat['SaleOtherFeePrice']
        sql +="',img='"          +img[0]
        sql +="',imgs='"         +imgs
        sql +="' where supplier = 2 and serial = '"+ rowDat['GoodsNo'] +"'"
        mysql.update(sql)"""

dat         = []
#updat      = []
#i          = 0
mysql       = DoMysql()
"""# 更新数据
sql ="select id,serial,designation,abstract,aurify,CAST(criterion_wgt as CHAR)as criterion_wgt, CAST(min_wgt as CHAR) as min_wgt,CAST(max_wgt as CHAR) as max_wgt,category,CAST(wage as CHAR) as wage,CAST(gram_wage as CHAR) as gram_wage,CAST(add_wage as CHAR) as add_wage,CAST(gram_add_wage as CHAR) as gram_add_wage,CAST(tag as CHAR) as tag,img,imgs from a_article_copy where supplier=2"
res = mysql.fetchAll(sql)
for oneRow in res:
    sql     = "select id from a_article where supplier=2 and serial='"+oneRow['serial']+"'"
    idDat   = mysql.fetchOne(sql)
    if idDat==None:
        sql     = "insert into `a_article_copy`(`serial`,`designation`,`abstract`,`aurify`,`min_wgt`,`max_wgt`,`category`,`gram_wage`,`gram_add_wage`,`img`,`imgs`,`supplier`,`status`,`shelf`,`sell`) values("+oneRow['serial']+","+oneRow['designation']+","+oneRow['abstract']+","+oneRow['aurify']+","+oneRow['min_wgt']+","+oneRow['max_wgt']+","+oneRow['category']+","+oneRow['gram_wage']+","+oneRow['gram_add_wage']+","+oneRow['img']+","+oneRow['imgs']+"2,1,0,1)"
        result  = mysql.insert_one(sql)
    else:
        designation = escape_string(oneRow['designation'])
        sql = "update a_article_copy set designation='"+designation
        sql +="',abstract='"     +oneRow['abstract']
        sql +="',aurify='"       +oneRow['aurify']
        sql +="',min_wgt='"      +oneRow['min_wgt']
        sql +="',max_wgt='"      +oneRow['max_wgt']
        sql +="',category='"     +oneRow['category']
        sql +="',gram_wage='"    +oneRow['gram_wage']
        sql +="',gram_add_wage='"+oneRow['gram_add_wage']
        sql +="',img='"          +oneRow['img']
        sql +="',imgs='"         +oneRow['imgs']
        sql +="' where id ="+ "%d" % idDat['id']
        result  = mysql.update(sql)
exit()"""
#with open('D:\project\\/respect_baifuyun\public\Product.csv', encoding='utf-8-sig') as f:
with codecs.open('D:\project\\/respect_baifuyun\public\Product.csv', encoding='utf-8-sig') as f:
    # 获取标题 以标题为字典的下标  csv.Reader 一行一行获取 没有下标的列表
    for row in csv.DictReader(f, skipinitialspace=True):
        """if i == 0:
            dat.append(getOneRow(row))
        i +=1"""
        dat.append(getOneRow(row))
        """res = getOneRow(row)
        if res['type'] == 'add':
            dat.append(res['data'])
        if res['type'] == 'edit':
            updat.append(res['data'])
        #dat.append(getOneRow(row))"""
#print(dat)
if dat == None:
    print('没有插入数据')
else:
    sql     = 'truncate table a_article_copy'
    mysql.truncateTable(sql)
    sql     = 'insert into `a_article_copy`(`serial`,`designation`,`abstract`,`aurify`,`min_wgt`,`max_wgt`,`category`,`gram_wage`,`gram_add_wage`,`img`,`imgs`,`supplier`,`status`,`shelf`,`sell`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    result  = mysql.insert_many(sql,dat)
    print(result) #返回插入数据的条数(2)
"""
if updat == None:
    print('没有更新数据')
else:
    j=0
    for proDat in updat:
        #designation = escape_string(proDat['Title'])
        sql = "update a_article_copy set designation='"  +proDat['designation']
        sql +="',abstract='"     +proDat['abstract']
        sql +="',aurify='"       +proDat['aurify']
        sql +="',min_wgt='"      +proDat['min_wgt']
        sql +="',max_wgt='"      +proDat['max_wgt']
        sql +="',category='"     +proDat['category']
        sql +="',gram_wage='"    +proDat['gram_wage']
        sql +="',gram_add_wage='"+proDat['gram_add_wage']
        sql +="',img='"          +proDat['img']
        sql +="',imgs='"         +proDat['imgs']
        sql +="' where supplier = 2 and serial = '"+ proDat['serial'] +"'"
        result  = mysql.update(sql)
        j = result+j
    print(j) #返回插入数据的条数(2)"""
#print(dat)

产品详情表数据处理

import csv      # csv
import codecs   # csv读取
import re       # 正则替换
import json     # json数据
import pymysql  # mysql
import time     # 时间
from pymysql.converters import escape_string    #转义方法转义后再进行保存
#pymysql.install_as_MySQLdb()    # 返回字典类型的数据 列表数据不引入
#import MySQLdb.cursors          # 返回字典类型的数据 列表数据不引入
class DoMysql:
    #初始化
    def __init__(self):
        #创建连接
        self.conn = pymysql.Connect(
            host 		= 'host',
            port 		= port,
            user 		= 'user',
            password 	= 'password',
            db 			= 'db',
            charset 	= 'utf8',
            cursorclass = pymysql.cursors.DictCursor  #以字典的形式返回数据
        )
        #获取游标
        self.cursor = self.conn.cursor()

    #返回单条数据
    def fetchOne(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    #返回多条数据
    def fetchAll(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    #插入一条数据
    def insert_one(self,sql):
        result = self.cursor.execute(sql)
        self.conn.commit()
        return result

    #插入多条数据
    def insert_many(self,sql,datas):
        result = self.cursor.executemany(sql,datas)
        self.conn.commit()
        return result

    #更新数据
    def update(self,sql):
        result = self.cursor.execute(sql)
        self.conn.commit()
        return result

    #清空数据
    def truncateTable(self,sql):
        result = self.cursor.execute(sql)
        self.conn.commit()
        return result

    #关闭连接
    def close(self):
        self.cursor.close()
        self.conn.close()
""" #参数分别为：ip，用户名，密码，库名
    db  = MySQLdb.connect(host='host',user='user',password='password',db='db',charset='utf8',cursorclass = MySQLdb.cursors.DictCursor)
    con = db.cursor()"""
addTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
def getOneRow(rowDat):
    #使用execute方法执行一条sql语句
    """sql 	= "select * from a_article_dtl_copy where supplier = 2 and serial = '"+rowDat['GoodsNo']+"' and barcode='"+rowDat['Barcode']+"'"
    #使用ferchall获取数据
    data 	= mysql.fetchOne(sql)"""
    indats  = (
        rowDat['Title'],
        rowDat['GoodsNo'],
        rowDat['Barcode'],
        rowDat['WorkFee'],
        rowDat['GoodsPrice'],
        rowDat['GoldWeight'],
        rowDat['GoldTypeName'],
        rowDat['GoodsParameters'],
        2,
        addTime,
    )
    return indats
    """if data == None:
        indats    = (
            rowDat['Title'],
            rowDat['GoodsNo'],
            rowDat['Barcode'],
            rowDat['WorkFee'],
            rowDat['GoodsPrice'],
            rowDat['GoldWeight'],
            rowDat['GoldTypeName'],
            rowDat['GoodsParameters'],
            2,
            addTime,
        )
        return {'type':'add','data':indats}
    else:
        updats    = {
            'title':            rowDat['Title'],
            'serial':           rowDat['GoodsNo'],
            'barcode':          rowDat['Barcode'],
            'wage':             rowDat['WorkFee'],
            'tag':              rowDat['GoodsPrice'],
            'wgt':              rowDat['GoldWeight'],
            'aurify':           rowDat['GoldTypeName'],
            'describe':         rowDat['GoodsParameters'],
            'supplier':         2,
            'addtime':          addTime,
        }
        return {'type':'edit','data':updats}"""

dat         = []
#updat      = []
serials     = []
i           = 0
mysql       = DoMysql()
#with open('D:\project\\/respect_baifuyun\public\ProductStock1.csv', encoding='utf-8-sig') as f:
sql     = 'select distinct `serial` from `a_article` where `supplier`=2'
seadat  = mysql.fetchAll(sql)
for ss in seadat:
    serials.append(ss['serial'])

#print(serials)
with codecs.open('D:\project\\/respect_baifuyun\public\ProductStock1.csv', encoding='utf-8-sig') as f:
    # 获取标题 以标题为字典的下标  csv.Reader 一行一行获取 没有下标的列表
    for row in csv.DictReader(f, skipinitialspace=True):
        """if row['GoodsNo'] in serials:
            if i == 0:
                dat.append(getOneRow(row))
            i +=1
        print(row)"""
        dat.append(getOneRow(row))
        """res = getOneRow(row)
        if res['type'] == 'add':
            dat.append(res['data'])
        if res['type'] == 'edit':
            updat.append(res['data'])"""
        #dat.append(getOneRow(row))
if dat == None:
    print('没有插入数据')
else:
    sql     = 'truncate table a_article_dtl_copy'
    mysql.truncateTable(sql)
    sql     = 'insert into `a_article_dtl_copy`(`title`,`serial`,`barcode`,`wage`,`tag`,`wgt`,`aurify`,`describe`,`supplier`,`addtime`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    result  = mysql.insert_many(sql,dat)
    print(result) #返回插入数据的条数(2)
"""
if updat == None:
    print('没有更新数据')
else:
    j=0
    for dtlDat in updat:
        sql = "update a_article_dtl_copy set title='"+dtlDat['title']+"'"
        sql +="',wage='"        +dtlDat['wage']
        sql +="',tag='"         +dtlDat['tag']
        sql +="',wgt='"         +dtlDat['wgt']
        sql +="',aurify='"      +dtlDat['aurify']
        sql +="',describ='"     +dtlDat['describ']
        sql +="',addtime='"     +dtlDat['addtime']
        sql +="' where supplier = 2 and serial = '"+ dtlDat['serial '] +"' and barcode = '"+dtlDat['barcode']+"''"
        result  = mysql.update(sql)
        j = result+j
    print(j) #返回插入数据的条数(2)"""
#print(dat)