17行代码实现将excel、csv数据批量导入到mysql数据库

save&code

已于 2022-03-08 21:11:08 修改

阅读量1k

点赞数 1

文章标签：数据库 python

于 2022-03-08 21:07:22 首次发布

本文链接：https://blog.csdn.net/qq_34510446/article/details/123363261

版权

import pymysql
import pandas as pd

# 连接mysql数据库
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='flask', charset='utf8')
# 建立游标
cur = conn.cursor()

data = pd.read_csv("shanghai.csv")
# print(len(data))

# 插入数据语句
query = """INSERT INTO hireinfo1 (title, region, price, square, floor) VALUES (%s, %s, %s, %s, %s)"""

# 迭代读取每行数据
for r in range(0, len(data)):
    title = data.loc[r].values[0]
    region = data.loc[r].values[1]
    price = data.loc[r].values[2]
    square = data.loc[r].values[3]
    floor = data.loc[r].values[4]
    values = (str(title), str(region), str(price), str(square), str(floor))
    cur.execute(query, values)
    print("插入成功")


cur.close()
conn.commit()
conn.close()