MAC python3 excel 数据存入mysql

一、创建连接

准备工作，想要使用Python操作MySQL，首先需要安装MySQL-Python的包，在Python 3.x下，该包已经改名为MySQLClient。可以使用pip3方式安装：

sudo pip3 install MySQLClient

二、实例

import xlrd
import MySQLdb
book = xlrd.open_workbook("demo.xls")
sheet = book.sheet_by_name("sheet")
conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='MOPS', charset='utf8')
cur = conn.cursor()
cur.execute("""
 create table if not EXISTS Ipsheet
  (
   id    bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   ip    varchar(512) NOT NULL DEFAULT '' COMMENT 'ip：配置获取ip',
   port  varchar(10) NOT NULL DEFAULT '' COMMENT '端口',
   PRIMARY KEY (`id`)
)
""")
for r in range(1, sheet.nrows):
    ip =  sheet.cell(r,0).value
    port =  sheet.cell(r,1).value
    Values = (ip, 80)
    try:
       cur.execute("insert into ipsheet(ip, port) values(\'%s\',\'%d\')" % Values)
    except Exception  as e:
       print("Can't create Temp Table BadTranscripts")
conn.commit()
cur.close()
conn.close()