您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

[Python] Python讀取百萬級數據自動寫入Mysql數據庫

編輯：Python

數據練習集

user_data.csv練習數據集https://download.csdn.net/download/Hudas/85712209?spm=1001.2014.3001.5503導入數據集

import pandas as pd
# 讀取數據
data = pd.read_csv(r'C:\Users\HP\Desktop\user_data.csv')
# 1048575行記錄，6列字段
data.shape # (1048575, 6)

1.利用Pandas sqlalchemy

import pandas as pd
from sqlalchemy import create_engine
# 創建數據庫對象
engine = create_engine('mysql+pymysql://root:[email protected]:3306/test')
# 讀取數據
data = pd.read_csv(r'C:\Users\HP\Desktop\user_data.csv')
# 將數據存入到Mysql數據庫中的user01數據表中
data.to_sql('user01',engine,chunksize=100000,index=None)
print('存入成功！')

結果展示

2.利用Python pymysql

PyMySQL是在Python3.x版本中用於連接Mysql服務器的一個庫

注意:引入import pymysql之前需要進行安裝

創建數據表user02

DROP TABLE IF EXISTS `user02`;
CREATE TABLE `user02` (
`user_id` bigint(20) DEFAULT NULL,
`item_id` bigint(20) DEFAULT NULL,
`behavior_type` bigint(20) DEFAULT NULL,
`user_geohash` text CHARACTER SET utf8 COLLATE utf8_general_ci,
`item_category` bigint(20) DEFAULT NULL,
`time` text CHARACTER SET utf8 COLLATE utf8_general_ci
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

import pandas as pd
import pymysql
# 設置數據庫連接信息:ip地址、用戶名、密碼、數據庫名、端口號、字符集
conn = pymysql.connect(
host='127.0.0.1',
user='root',
passwd='123456',
db='test',
port = 3306,
charset="utf8")
# 分塊處理
# 因為數據量龐大，所以設置每次只處理100000條數據
big_size = 100000
with pd.read_csv(r'C:\Users\HP\Desktop\user_data.csv',chunksize=big_size) as reader:
for df in reader:
datas = []
print('處理:',len(df))
for i ,j in df.iterrows():
data = (j['user_id'],j['item_id'],j['behavior_type'],j['item_category'],j['time'])
datas.append(data)
_values = ",".join(['%s', ] * 5)
# sql插入語句
sql = """insert into user02(user_id,item_id,behavior_type,item_category,time) values(%s)""" % _values
# 使用cursor()方法創建一個游標對象cursor
cursor = conn.cursor()
# 執行sql語句
cursor.executemany(sql,datas)
# 提交到數據庫執行
conn.commit()
# 關閉數據庫連接
conn.close()
cursor.close()
print('存入成功！')

結果展示