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值调小。

在数据分析时，我们有中间结果，或者最终的结果，需要保存到 数据库 中；或者我们有一个中间的结果，如果放到 数据库 中通过 sql 操作会更加的直观，处理后再将结果读取到DataFrame中。这两个场景，就需要用到DataFrame的to_ sql 操作。 具体的操作 连接 数据库 代码 import pandas as pd from sql alchemy import create_engine # default engine = create_engine(' mysql +py mysql ://ledao:ledao123@localhost/ pandas _learn') original_data name: 数据库 中的表名 con: 与read_ sql 中相同， 数据库 连接的驱动 if_exits： 当 数据库 中的这个表存在的时候，采取的措施是什么，包括三个值，默认为fail fail，若表存在，则不进行数据表 写入 的相关操作 replace：若表存在，将 数据库 表中的数据覆盖； append：若表存在，将数据写到原表的后面。 index：是否将df 3.if_exists=‘append’ 默认是覆盖，如果已经存在则报错 4.如果 pandas 中的列不全，则在 写入 sql 时，没有指明的列会设为null，或默认值，或自动增加 5.类型对应关系 1）如果类型不一样会不会报错？比如str 写入 int 2）具体对应关系如何 H2是一个用Java开发的嵌入式 数据库 ，它本身只是一个类库，可以直接嵌入到应用项目中。 常用的开源 数据库 有：H2，Derby，H SQL DB， MySQL ，Postgre SQL 。其中H2和H SQL DB类似，十分适合作为嵌入式 数据库 使用，而其它的 数据库 大部分都需要安装独立的客户端和服务器端。 　　H2的优势： 　　　　1、h2采用纯Java编写，因此不受平台的限制。 　　　　2、h2只有一个jar文件，十分适合作为嵌入式 数据库 试用。 　　　　3、h2提供了一个十分方便的web控制台用于操作和管