python批量操作数据库

from sshtunnel import SSHTunnelForwarder

import pymysql

import xlrd

##创建数据库连接

with SSHTunnelForwarder(

##跳板机配置

('',22),

ssh_password="XXX",

ssh_username="XXX",

##数据库配置

remote_bind_address=('',3306)) as server:

conn=(

host='127.0.0.1',

port=server.local_bind_port,

user='XXX',

password='XXX',

db='XXX',

charset='utf8')

##打开文件

try:

book = xlrd.open_workbook('E:/pythoncoding/')##文件路径

except:

print("open excel file failed!")

##获取某一工作表

try:

sheet = book.sheet_by_name("123")

except:

print("locate worksheet in excel failed!")

##创建操作的游标

cursor = ()

##获取行数

row_num =

for i in range(0,row_num):

##goods_id = (i,0).value ##取第i行第0列

##plus_self_buy = (i,1).value

##common_self_buy = (i,2).value

##plus_to_plus = (i,3).value

##plus_to_common = (i,4).value

##common_to_plus = (i,5).value

##common_to_common = (i,6).value

##history = (i,7).value

##value = (goods_id,plus_self_buy,common_self_buy,plus_to_plus,plus_to_common,common_to_plus,common_to_common,history)

row_data = sheet.row_values(i)

##第i行数据

value = (row_data[0],row_data[1],row_data[2],row_data[3],row_data[4],row_data[5],row_data[6],row_data[7])

print(value)

##sql语句

sql = 'insert into sl_goods_fee_splitting (goods_id,plus_self_buy,common_self_buy,plus_to_plus,plus_to_common,common_to_plus,common_to_common,history) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)'

print(sql)

##执行插入语句

(sql,value)

()

()

()