导入依赖包
import pymysql
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
from sqlalchemy.types import NVARCHAR, Float, Integer
连接mysql
如果是要读取,有两种方式连接mysql
方法一
def sql_connect(host="localhost",port=3306,user='root',passwd='123456',db='test',charset='utf8'):
connect = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
cur = connect.cursor()
return connect,cur
connect,cur = sql_connect(host="***",db='****')
方法二
engine = create_engine('mysql+pymysql://root:123456@ip:3306/biaoming?charset=utf8')
connect = engine.connect()
读取
如果要读取数据,两种方法生成的connect都可以用于读取
df1 = pd.read_sql("select * from **",connect)
写入
df.to_sql(name="表名",con=engine,if_exists="append",index=True,dtype={'column1':NVARCHAR(length=100),
'column2':Float(),
'column3':Integer()})
- 这里
if_exists
有三个模式:fail,若表存在,则不输出;replace:若表存在,覆盖原来表里的数据;append:若表存在,将数据写到原表的后面。默认为fail - index:是否将df的index单独写到一列中
- dtype: 指定列的输出到数据库中的数据类型。字典形式储存:{column_name: sql_dtype}。常见的数据类型有sqlalchemy.types.INTEGER(), sqlalchemy.types.NVARCHAR(),sqlalchemy.Datetime()等。
注意:如果不提供dtype,to_sql会自动根据df列的dtype选择默认的数据类型输出,比如字符型会以sqlalchemy.types.TEXT类型输出,相比NVARCHAR,TEXT类型的数据所占的空间更大,所以一般会指定输出为NVARCHAR;而如果df的列的类型为np.int64时,将会导致无法识别并转换成INTEGER型,需要事先转换成int类型(用map,apply函数可以方便的转换)。