一、准备工作
(1)安装模块:
sqlalchemy
pymysql
pandas
numpy
(2)安装mysql 8.0
二、将DataFrame导入MySQL表格
(1)使用sqlalchemy创建SQLAlchemy.engine
from sqlalchemy import create_engine
engine=create_engine("mysql+pymysql://root:我的密码@localhost:3306/test1")
(2)利用DataFrame.to_sql函数将数据导入mysql数据库
import pandas as pd
import numpy as np
np.random.seed(24)
m=np.random.randint(0,100,size=(100,5))
df=pd.DataFrame(m,columns=['departmen','date','proj_class','support_mode','content'])
#sql_db=pd.read_sql('test',con=engine)
df.to_sql(name='test_df',con=engine)
查看结果:
mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | test_df | +-----------------+ 1 row in set (0.00 sec) mysql> select * from test_df; +-----------+------+------------+--------------+---------+ | departmen | date | proj_class | support_mode | content | +-----------+------+------------+--------------+---------+ | 34 | 3 | 64 | 87 | 17 | | 17 | 1 | 79 | 4 | 99 | | 82 | 11 | 99 | 15 | 73 | | 18 | 7 | 25 | 35 | 95 | | 28 | 0 | 12 | 95 | 33 | | 1 | 60 | 55 | 70 | 97 | | 81 | 6 | 46 | 65 | 47 | | 89 | 74 | 35 | 64 | 27 | | 97 | 33 | 3 | 4 | 65 | ... | 55 | 68 | 23 | 84 | 24 | | 10 | 89 | 21 | 72 | 1 | | 38 | 78 | 55 | 76 | 22 | | 4 | 47 | 29 | 47 | 19 | | 54 | 78 | 71 | 17 | 80 | | 81 | 56 | 78 | 80 | 8 | +-----------+------+------------+--------------+---------+ 100 rows in set (0.00 sec)
成功创建新表,并写入数据。
参考文献
ModuleNotFoundError: No module named ‘MySQLdb’
Python操作MySQL数据库的两种方式pymysql和pandas