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

[python] Python reads million level data and automatically writes it to MySQL database

編輯：Python

Data exercise set

user_data.csv Practice datasets https://download.csdn.net/download/Hudas/85712209?spm=1001.2014.3001.5503 Import dataset

import pandas as pd
# Reading data
data = pd.read_csv(r'C:\Users\HP\Desktop\user_data.csv')
# 1048575 rows ,6 Column fields
data.shape # (1048575, 6)

1. utilize Pandas sqlalchemy

import pandas as pd
from sqlalchemy import create_engine
# Create database objects
engine = create_engine('mysql+pymysql://root:[email protected]:3306/test')
# Reading data
data = pd.read_csv(r'C:\Users\HP\Desktop\user_data.csv')
# Store data in Mysql In the database user01 In the data table
data.to_sql('user01',engine,chunksize=100000,index=None)
print(' Deposit successful ！')

Result display

2. utilize Python pymysql

PyMySQL Is in Python3.x Version for connecting Mysql A library of servers

Be careful : introduce import pymysql Installation required before

Create data table 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
# Set database connection information :ip Address 、 user name 、 password 、 Database name 、 Port number 、 Character set
conn = pymysql.connect(
host='127.0.0.1',
user='root',
passwd='123456',
db='test',
port = 3306,
charset="utf8")
# Block processing
# Because of the huge amount of data , So it is set to process only 100000 Data
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(' Handle :',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 Insert statement
sql = """insert into user02(user_id,item_id,behavior_type,item_category,time) values(%s)""" % _values
# Use cursor() Method to create a cursor object cursor
cursor = conn.cursor()
# perform sql sentence
cursor.executemany(sql,datas)
# Commit to database execution
conn.commit()
# Close database connection
conn.close()
cursor.close()
print(' Deposit successful ！')

Result display