使用Python将Excel数据批量导入到帝国cms中

本文是将采集得到的数据，通过Python将数据批量导入到数据库中，代码段如下：

import xlrd
import MySQLdb
import time
import random
import aiohttp
#sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='gb18030')

# filename=r"F:\pachong\xx.xlsx"
wb=xlrd.open_workbook(r"E:\pachong\xx.xls")
sheet_1=wb.sheet_by_index(1)
db = MySQLdb.connect(host='127.0.0.1', port=3306, user='dg', passwd='diguo75', db='dg',charset='utf8')
m=21784
classid=444

cursor = db.cursor()

sql_ext = "select classpath from phome_enewsclass where classid='" + str(classid) + "';"
cursor.execute(sql_ext)
result=cursor.fetchone()


for i in range(sheet_1.nrows):
    # print(sheet_1.row_values(i))
    print(sheet_1.cell(i, 0).value)
    # print(sheet_1.cell(i, 1).value)
    m += 1
    title=sheet_1.cell(i, 0).value
    body=sheet_1.cell(i, 1).value
    # 使用cursor()方法获取操作游标

    titleurl = 'domain' +result[0]+'/'+str(m)+'.html'
    print(titleurl)
    # cursor.execute("SELECT VERSION()")
    # 使用 fetchone() 方法获取一条数据

    # # SQL 插入语句
    try:
        info = {
                'id': m,
                'classid': classid,
                'onclick': random.randint(3000, 10000),
                'newspath': '',
                'keyboard': '',
                'userid': 1,
                'username': 'admin',
                'istop': 0,
                'truetime': time.time(),
                'ismember': 0,
                'userfen': 0,
                'isgood': 0,
                'titlefont': '',
                'titleurl': titleurl,
                'filename': m,
                'groupid': 0,
                'plnum':0,
                'firsttitle': 0,
                'isqf': 0,
                'totaldown': 0,
                'title': title.replace('\n', ''),
                'newstime': time.time(),
                'titlepic': '',
                'havehtml': 1,
                'lastdotime': time.time(),
                'ftitle': title.replace('\n', ''),
                'smalltext': '',  # MySQLdb.escape_string(des[0:200]),
                'diggtop': 0,
            }

        data={
                'id'    :m,
                'classid' :classid,
                'writer':'program',
                'befrom':'',
                'newstext':body,
                'keyid' :'',
                'dokey' :1,
                'newstempid': 0,
                'closepl': 0,
                'haveaddfen': 0,
                'infotags': ''

            }

        index={
                'id'    :m,
                'classid' :classid,
                'checked': 1,
                'newstime': time.time(),
                'truetime': time.time(),
                'lastdotime': time.time(),
                'havehtml': 1
            }

        table = 'phome_ecms_table'
        keys = ','.join(info.keys())
        values = ','.join(['%s'] * len(info))
        sql1 = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)

        tabledata = 'phome_ecms_table_data_1'
        keysdata = ','.join(data.keys())
        valuesdata = ','.join(['%s'] * len(data))
        sql2 = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=tabledata, keys=keysdata, values=valuesdata)

        indexdata = 'phome_ecms_table_index'
        indexkeysdata = ','.join(index.keys())
        indexvaluesdata = ','.join(['%s'] * len(index))
        sql3 = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=indexdata, keys=indexkeysdata, values=indexvaluesdata)

        # exit(print(tuple(data.values())))

        # cursor.execute(sql1, tuple(info.values()))
        # cursor.execute(sql2, tuple(data.values()))
        # cursor.execute(sql3, tuple(index.values()))
        print('ko')
        # 提交到数据库执行
        db.commit()
    except:
        # Rollback in case there is any error
        # print(db.DataError.args[0])
        db.rollback()
        print('no：'+title)
        # 关闭数据库连接
db.close()