pandas使用to_sql快速写入mysql

https://blog.youkuaiyun.com/LeiLiFengX/article/details/109922043

指定数据类型

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 指定 根据列名对应的数据类型字段即可
将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`) 上。同时,在实际应用中需要注意可能存在的数据兼容性问题,并采取适当措施加以规避。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值