pandas 写入mysql数据库.to_sql方法详解

pandas 写入mysql数据库.to_sql方法详解

DataFrame.to_sql(self, name, con, schema=None, if_exists=’fail’, index=True, index_label=None, chunksize=None,dtype=None, method=None)

 

再官方文档中已经详细描述to_sql()方法使用方式

.to_sql()方法的定义：

def to_sql(self, name, con, schema=None, if_exists='fail', index=True,
           index_label=None, chunksize=None, dtype=None, method=None):
    """
    Write records stored in a DataFrame to a SQL database.

    Databases supported by SQLAlchemy [1]_ are supported. Tables can be
    newly created, appended to, or overwritten.

    Parameters
    ----------
    name : string
        Name of SQL table.
    con : sqlalchemy.engine.Engine or sqlite3.Connection
        Using SQLAlchemy makes it possible to use any DB supported by that
        library. Legacy support is provided for sqlite3.Connection objects.
....

to_sql()方法参数解析：

name:指定的是将输入接入数据库当做的哪个表

con：与数据库链接的方式，推荐使用sqlalchemy的engine类型

schema: 相应数据库的引擎，不设置则使用数据库的默认引擎，如mysql中的innodb引擎

if_exists: 当数据库中已经存在数据表时对数据表的操作，有replace替换、append追加，fail则当表存在时提示ValueError。

index：对DataFrame的index索引的处理，为True时索引也将作为数据写入数据表

index_label:当上一个参数index为True时，设置写入数据表时index的列名称

chunsize：设置整数，如20000，一次写入数据时的数据行数量，当数据量很大时，需要设置，否则会链接超时写入失败。

dtype：写入数据表时，可以设置列的名称(The keys should be the column
names and the values should be the SQLAlchemy types or strings for
the sqlite3 legacy mode),需要设置时，类型需要和sqlalchemy的类型保持一致.当不设置时，to_sql生成表时会自动兼容最大的类型。

.to_sql()参数中除 name、con必填外，可选参数index推荐使用False，同时dtype推荐不使用。

to_sql方法当数据表不存在时创建，存在时根据if_exists参数设置的replace，append，fail时分别对应替换、追加、失败处理。

数据库中对表的增删改，最好是在数据库层面处理，不应该由to_sql()方法设置，虽然这个方法本身可以在表不存在时增加表，但是不推荐。在数据库层面设计表，需要根据表的数据，不同的字段设计合理的存储类型，可以对表进行合理的设计和优化。to_sql()本身创建的表，浮点类型是double，整型bigint，字符类型默认兼容最大的text，虽然可以使用dtype参数设置类型，但我个人不推荐使用。还是建议在数据库中先创建合理的目标表，在根据to_sql()方法，将数据写入目标表中。

在Python3中，需要安装sqlalchemy 和 mysql-connector、pymsyql库

在python3中，to_sql()的con对象，是 sqlalchemy 的 engine 引擎，通过sqlalchemy的create_engine创建：有两种方式，基本格式一致，区别只是在于使用mysqldb,还是使用mysqlconnector，推荐使用mysqlconnector。

mysqldb是python2的mysql连接库，在python3时，已经废除mysqldb，改为pymysql。在sqlachemy必须使用mysqldb驱动时，需要先导入pymysql，然后pymysql.install_as_MySQLdb()才能使用。

sqlalchemy.create_engine()的两种方式：

engine = create_engine('mysql+mysqldb://user:passwd@127.0.0.1/database?charset=utf8') 
engine = create_engine('mysql+mysqlconnector:// user:passwd@127.0.0.1/database?charset=utf8') 
user:passwd@127.0.0.1/database  --> 格式为 用户名:密码@服务器地址/数据库名

mysqlconnector的使用方式

生成engine对象时，推荐使用mysqlconnector作为驱动，需要使用到sqlalchemy 和 mysql-connector 两个库，使用pip安装

pip install sqlalchemy

pin isntall mysql-connector

固定语法：

from sqlalchemy import create_engine
DB_STRING = 'mysql+mysqlconnector://user:passwd@127.0.0.1/database?charset=utf8'
engine = create_engine(DB_STRING)
.....
data.to_sql('表明',con = engine,..)

这种使用方式在mysql5.7版本以前，是没有问题，但是在mysql8版本以后，mysql8更改了密码加密方式，在使用此种方式时会提示错误。

在用to_sql写入mysql8以上版本时，需要使用mysqldb作为驱动

pymysq的使用方式

pin install pymysql

在导入pymysq使用时需要pymysql.install_as_MySQLdb()才能使用

固定语法：

import pymysql
pymysql.install_as_MySQLdb()
DB_STRING = 'mysql+mysqldb://user:passwd@127.0.0.1/db_name?charset=utf8'
engine = create_engine(DB_STRING) 

生成引擎之后，可以使用DataFrame.to_sql()方法，将DataFrame数据写入数据库。这种方式本身没有问题，但是在写入数据库时会提示预警信息，不影响正常写入。

to_sql()时对应的字段类型设置参数dtype使用方法：

DATE,CHAR,VARCHAR… 可以去 sqlalchemy 的官方文档查看所有的sql数据类型： [‘TypeEngine’, ‘TypeDecorator’, ‘UserDefinedType’, ‘INT’, ‘CHAR’, ‘VARCHAR’, ‘NCHAR’, ‘NVARCHAR’, ‘TEXT’, ‘Text’, ‘FLOAT’, ‘NUMERIC’, ‘REAL’, ‘DECIMAL’, ‘TIMESTAMP’, ‘DATETIME’, ‘CLOB’, ‘BLOB’, ‘BINARY’, ‘VARBINARY’, ‘BOOLEAN’, ‘BIGINT’, ‘SMALLINT’, ‘INTEGER’, ‘DATE’, ‘TIME’, ‘String’, ‘Integer’, ‘SmallInteger’, ‘BigInteger’, ‘Numeric’, ‘Float’, ‘DateTime’, ‘Date’, ‘Time’, ‘LargeBinary’, ‘Binary’, ‘Boolean’, ‘Unicode’, ‘Concatenable’, ‘UnicodeText’, ‘PickleType’, ‘Interval’, ‘Enum’, ‘Indexable’, ‘ARRAY’, ‘JSON’] 可以选择合适的类型与数据库对应

示例：
from sqlalchemy.types import DATE,CHAR,VARCHAR 
DTYPES = {'col_1字段名称' : DATE, 'col_2':CHAR(4),'col_3':VARCHAR(10)}
df.to_sql(....,dtype = DTYPES)
将写入数据表的df中，dtype 指定 根据列名对应的数据类型字段即可

如果使用.to_sql（）需要指定dtype类型时，如果数据库中不存在目标表，则相应创建；如果数据库中已经存在目标表，则设置append追加模式写入数据库时，可能会引起字段类型冲突。

.to_sql()方法参数演示

data = pd.DataFrame(np.random.rand(4,4),index=list('abcd'),columns=['col_1','col_2','col_3','col_4'])
print(data)
  col_1     col_2     col_3     col_4

 a  0.526716  0.082858  0.458375  0.640027
 b  0.316326  0.122944  0.469743  0.119170
 c  0.911248  0.920943  0.120026  0.165420
 d  0.919385  0.669661  0.083722  0.227291

将data写入数据库，如果表存在就替换，将data的index也写入数据表，写入字段名称为id_name
data.to_sql('table_name',con='engine',chunksize=10000,if_exists='replace',index=True,index_label='id_name')
将data写入数据库，如果表存在就追加
data.to_sql('table_name',con='engine',chunksize=10000,if_exists='append')
将data写入数据库，如果表存在就替换，指定col_1的字段类型为char(4)
data.to_sql('table_name',con='engine',chunksize=10000,if_exists='replace,dtype={'col_1':CHAR(4)})

如果data数据量大，需要设置合理的chunksize值，这和数据库缓存大小有关，
可以设置在50000-10000，如果提示数据库连接超时错误，就将size值调小。