python 导入excel代码_python导入excel表代码

import pymysql

import xlrd

class ReadExcel(object):

def __init__(self,ip , port, user, passwd, dataname, filepath):

self.ip = ip

self.port = port

self.user = user

self.passwd = passwd

self.dataname = dataname

self.filepath = filepath

def Read_text(self):# 读取文件并写入文件

read = xlrd.open_workbook(self.filepath)

table = read.sheet_by_index(0)

for iin range(0,table.nrows):

info = table.row_values(i)

if i ==0:

print(info)

else:

self.C_NULL(info)

print(f'[{info[0]},{info[1]},{int(info[2])},{info[3]}, {info[4]}, {int(info[5])}, {info[6]}, {info[7]}]')

cursor, db =self.Connect_MySQL()

self.Write_MySQL(cursor, db, info)

def Get_Values(self,info):

sname = info[0]

age =int(info[2])

gender = info[1]

phone =int(info[5])

education = info[3]

place = info[4]

remark = info[6]

email = info[7]

return sname, age, gender, phone, education, place, remark, email

def Connect_MySQL(self):

db = pymysql.connect(self.ip,self.user,self.passwd,self.dataname,self.port)

cursor = db.cursor()

return cursor,db

def Write_MySQL(self,cursor, db, info):# 写入mysql

sql ='INSERT INTO students(sname, age, gender, phone , education, place, remark, email) ' \

'values(%s, %s ,%s ,%s , %s, %s, %s, %s);'

sname, age, gender, phone, education, place, remark, email =self.Get_Values(info)

values =(sname, age, gender, phone, education, place, remark, email)

cursor.execute(sql,values)

db.commit()

def Create_Table(self,cursor,db):

try:

sql ="CREATE TABLE students(" \

"sid int PRIMARY KEY auto_increment," \

"sname VARCHAR(20) not null unique ," \

"age int," \

"gender char(5)," \

"phone char(11)," \

"education char(10)," \

"place VARCHAR(10)," \

"remark char(100) DEFAULT 'tese'," \

"email VARCHAR(50)" \

");"

cursor.execute(sql)

db.commit()

except Exception as e:

sql1 ='drop table students'

cursor.execute(sql1)

db.commit()

self.Create_Table(cursor, db)

def C_NULL(self, info):

if info[2] =='':

info[2] =0

if info[7] =='':

info[7] ='NULL'

if info[4] =='':

info[4] ='NULL'

if info[3] =='':

info[3] ='NULL'

if info[6] =='':

info[6] ='NULL'

if __name__ =='__main__':

e = ReadExcel('127.0.0.1',3306,'root','234567','students',r'C:\stu_excel\test_stu.xlsx')

cursor, db = e.Connect_MySQL()

e.Create_Table(cursor, db)

e.Read_text()