Hello everyone ,Python Read data is automatically written MySQL database , This requirement is very common in the work , Mainly involves python Operating the database , Read / write updates, etc , The database may be mongodb、 es, Their treatment ideas are similar , You only need to change the syntax of operating the database .
This article will share how to write data to mysql, It is divided into two scenarios , Three ways . Like to remember to collect 、 Focus on 、 give the thumbs-up .
notes ： At the end of the article, a technical exchange group is provided
Use navicat The Import Wizard function of the tool . Support multiple file formats , You can automatically create a table according to the file fields , You can also insert data into an existing table , It's very fast and convenient .
Test data ：csv Format , about 1200 Line ten thousand
import pandas as pd
data = pd.read_csv('./tianchi_mobile_recommend_train_user.csv')
data.shape
Print the results
Mode one ：
python + pymysql library
install pymysql command
pip install pymysql
Code implementation ：
import pymysql
# Database connection information
conn = pymysql.connect(
host='127.0.0.1',
user='root',
passwd='wangyuqing',
db='test01',
port = 3306,
charset="utf8")
# Block processing
big_size = 100000
# Block traversal writes to mysql
with pd.read_csv('./tianchi_mobile_recommend_train_user.csv',chunksize=big_size) as reader:
for df in reader:
datas = []
print(' Handle ：',len(df))
# print(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 into users(user_id,item_id,behavior_type ,item_category,time) values(%s)""" % _values
cursor = conn.cursor()
cursor.executemany(sql,datas)
conn.commit()
# Close the service
conn.close()
cursor.close()
print(' Deposit successful ！')
Code implementation ：
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:[email protected]:3306/test01')
data = pd.read_csv('./tianchi_mobile_recommend_train_user.csv')
data.to_sql('user02',engine,chunksize=100000,index=None)
print(' Deposit successful ！')
pymysql The method takes 12 branch 47 second , It takes a long time , A lot of code , and pandas It takes only five lines of code to implement this requirement , It only took 4 About minutes .
Finally add , Method 1 needs to create a table in advance , Mode 2 does not require .
So I recommend you to use the second method , It is convenient and efficient . If you still feel that your speed is slow , Consider joining multiple processes 、 Multithreading .
The most complete three store data in MySQL Database method ：
Direct deposit , utilize navicat Import Wizard function of
Python pymysql
Pandas sqlalchemy
Li Hongyi 《 machine learning 》 Mandarin Program (2022) coming
Some people made Mr. Wu Enda's machine learning and in-depth learning into a Chinese version
Addicted to , Recently, a large visual screen has been rolled out for the company ( Source code attached )
So elegant ,4 paragraph Python Automatic data analysis artifact is really fragrant
Combing for more than half a month , Well prepared 17 A map of knowledge and thinking , Let's talk about statistics this time
Year end summary ：20 Visual large screen template , Direct application is really fragrant ( The source code is attached at the end of the article )
Welcome to reprint 、 Collection 、 Gain some praise and support ！ data 、 The code can be obtained from me
At present, a technical exchange group has been opened , Group friends have exceeded 2000 people , The best way to add notes is ： source + Interest direction , Easy to find like-minded friends
