python远程连接mysql以及pandas.DataFrame.to_sql写入数据库

​

1. 安装mysqlconnector

pip install mysql-connector-2.1.6.tar.gz

2. 安装sqlalchemy

pip install sqlalchemy

3. mysql的连接与操作

import mysql.connector
cnt = mysql.connector.connect(user='username', password='yourpassword', host='yourip', port=3306, database='dbname')
cursor_1 = cnt.cursor()
cursor_1.execute("selcect * from")
data = cursor_1.fetchall() #获取结果

4. dataframe直接写入mysql

from sqlalchemy import create_engine
conn = create_engine('mysql+mysqlconnector://用户名:密码@ip:端口/数据库?charset=utf8')
yourdf.to_sql(tablename, conn, schema='数据库名', if_exists='append', index=False, chunksize=10000) # 如果表已经存在 df的列名应该与表里的列名一样

​