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

本文详细介绍Pandas中to_sql方法的使用,包括参数解析、推荐设置及注意事项。该方法用于将DataFrame数据写入SQL数据库,支持多种操作如创建新表、追加或替换现有表。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

DataFrame.to_sql(selfnameconschema=Noneif_exists=’fail’index=Trueindex_label=Nonechunksize=None,dtype=Nonemethod=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值调小。

 

### pandas 中 `to_sql` 函数的用法 #### 1. 基本概念 `pandas.DataFrame.to_sql()` 是 Pandas 提供的一个方法,用于将 DataFrame 数据写入 SQL 数据库表中。此功能依赖于 SQLAlchemy 库来创建数据库引擎并管理连接。 --- #### 2. 参数详解 以下是 `to_sql` 方法的主要参数及其作用: | 参数 | 描述 | |--------------|----------------------------------------------------------------------------------------| | name | 要写入的目标表名(字符串)。 | | con | 数据库连接对象,通常由 SQLAlchemy 的 `create_engine` 创建。 | | schema | 可选,默认为 None;指定目标表所在的模式(schema),如果适用的话。 | | if_exists | 指定当目标表已存在时的行为:<br> - `'fail'`: 如果表已经存在,则抛出异常。<br> - `'replace'`: 删除旧表并重新创建新表。<br> - `'append'`: 将数据追加到现有表中。[^3] | | index | 是否将 DataFrame 的索引作为单独的一列写入数据库表中。默认值为 True。 | | index_label | 索引列在数据库中的名称。 | | chunksize | 单次写入的最大行数。如果不设置该参数,则一次性写入整个 DataFrame。 | | dtype | 字典形式,指定每列的数据类型映射关系。 | --- #### 3. 示例代码 ##### (1)基本用法 以下是一个完整的示例,展示如何通过 `to_sql` 将 DataFrame 写入 MySQL 数据库: ```python import pandas as pd from sqlalchemy import create_engine # 定义数据库连接信息 user = "your_username" password = "your_password" host = "localhost" port = 3306 database = "test_db" # 创建数据库连接引擎 conn_str = f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}' engine = create_engine(conn_str) # 构造一个简单的 DataFrame data = { "id": [1, 2, 3], "name": ["Alice", "Bob", "Charlie"], "age": [25, 30, 35] } df = pd.DataFrame(data) # 使用 to_sql 将 DataFrame 写入数据库 table_name = "example_table" df.to_sql(name=table_name, con=engine, if_exists="replace", index=False) ``` --- ##### (2)处理复杂情况 如果需要自定义各列的数据类型,可以使用 `dtype` 参数传递字典型数据类型的映射关系。例如: ```python from sqlalchemy.types import Integer, String dtypedict = { "id": Integer, "name": String(50), "age": Integer } df.to_sql( name=table_name, con=engine, if_exists="replace", index=False, dtype=dtypedict ) ``` --- ##### (3)解决常见问题 - **错误 1241**: 当 DF 数据中包含列表或其他不可序列化的数据类型时,可能会引发此类错误。解决方案是对这些字段进行强制转换为字符串类型。例如: ```python df['column_with_list'] = df['column_with_list'].apply(lambda x: ','.join(map(str, x))) ``` 或者更简单的方式: ```python df.loc[:, 'column_with_list'] = df['column_with_list'].astype(str) # 强制转成字符串类型[^1] ``` --- #### 4. 数据库连接注意事项 为了成功建立与数据库的连接,需确保以下几点: - 正确安装所需的驱动程序(如 pymysqlmysqldb)。 - 配置正确的连接 URL 格式。例如对于 MySQL 数据库,URL 形式如下: ``` mysql+pymysql://username:password@hostname:port/database_name?charset=utf8 ``` --- #### 5. 总结 `to_sql` 是一种高效的方法,能够轻松实现从 PandasSQL 数据库的数据迁移。其灵活性体现在支持多种行为选项 (`if_exists`) 和数据类型控制 (`dtype`) 上。同时,在实际应用中需要注意可能存在的数据兼容性问题,并采取适当措施加以规避。 ---
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值