pymysql——excel数据导入mysql

先导入excel数据，取前100行：

import pymysql
import pandas as pd 
path = r'商业数据\股票客户流失.xlsx'  # 需要写入的文件路径,同级目录省略路径,直接写文件名
data = pd.read_excel(path)  # 使用pandas库读取数据
data=data[0:100]

data

创建数据库shuju

import pymysql
mydb = pymysql.connect(
  host="localhost",
  user="root",
  passwd="123456"
)
mycursor = mydb.cursor()
mycursor.execute("create database shuju")

 

mycursor.execute("use shuju")

 

创建数据表test

mycursor.execute("CREATE TABLE test(zijin VARCHAR(20), \
                jiaoyi VARCHAR(20) NOT NULL DEFAULT '',\
                yongjin VARCHAR(20) NOT NULL DEFAULT '',\
                leiji VARCHAR(20) NOT NULL DEFAULT '',\
                shichang VARCHAR(20) NOT NULL DEFAULT '',\
                liushi VARCHAR(20) NOT NULL DEFAULT '')\
                ")

 

填入数据，总共100行

sql= 'insert into test(zijin,jiaoyi,yongjin,leiji,shichang,liushi) values (%s,%s,%s,%s,%s,%s)'
for i in range(len(data)):
    zijin = data.iloc[i, 0]
    jiaoyi = data.iloc[i, 1]
    yongjin = data.iloc[i, 2]
    leiji = data.iloc[i, 3]
    shichang = data.iloc[i, 4]
    liushi = data.iloc[i, 5] 
    # 为保险进行一次类型转换
    values = ((zijin), str(jiaoyi), str(yongjin), str(leiji), str(shichang),str(liushi)) 
    mycursor.execute(sql, values)

mycursor.execute("select * from test")

for x in mycursor:
    print(x)