一、创建连接
准备工作,想要使用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()