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()
