Python如何将Dataframe直接写入数据库

Python如何将Dataframe直接写入数据库

在使用pandas的过程中，我们有时候会需要将某个Dataframe存入数据库中，我们可以直接使用sqlalchemy 库和pandas

1. 首先导入库

import pandas as pd
from sqlalchemy import create_engine

2. 设置数据库信息

hostname = "localhost"
dbname = "mydb_name"
uname = "my_user_name"
pwd = "my_password"

3. 创建SQLAlchemy engine来连接数据库

engine = create_engine(f"mysql+pymysql://{uname}:{pwd}@{hostname}/{dbname}")

4. 把Dataframe存入数据库中
   （1） 使用dataframe.to_sql()
   参数：

    def to_sql(
        self,
        name: str,
        con,
        schema: str | None = None,
        if_exists: Literal["fail", "replace", "append"] = "fail",
        index: bool_t = True,
        index_label: IndexLabel = None,
        chunksize: int | None = None,
        dtype: DtypeArg | None = None,
        method: str | None = None,
    ) -> int

参数说明：

• name: SQL表名
• con: sqlalchemy.engine.(Engine or Connection) 或 sqlite3.Connection
  • 使用SQLAlchemy可以使用该库支持的任何DB。对sqlite3.Connection对象提供了遗留支持。用户负责引擎处置和连接关闭。
  • 如果传入的sqlalchemy.engine.Connection已经在事务中,则不会提交事务。如果传入sqlite3.Connection,则无法回滚记录插入。
• schema: str, 可选
  • 指定模式(如果数据库支持此功能)。如果为None,使用默认模式。
• if_exists: {‘fail’, ‘replace’, ‘append’}, 默认’fail’
  • 如果表已存在,如何处理。
    • fail:引发ValueError。
    • replace:在插入新值之前删除表。
    • append:向现有表插入新值。
• index: bool, 默认True
  • 将DataFrame索引写入为一列。使用index_label作为表中的列名称。
• index_label: str或sequence,默认None
  • 索引列的列标签。如果为None(默认值)且index为True,则使用索引名称。
  • 如果DataFrame使用MultiIndex,则应提供一个序列。
• chunksize: int,可选
  • 指定每次写入的行数。默认情况下,所有行将立即写入。
• dtype: dict或标量,可选
  • 为列指定数据类型。如果使用字典,键应为列名称,值为SQLAlchemy类型或sqlite3遗留模式的字符串。如果提供标量,它将应用于所有列。
• method: {None, ‘multi’, callable},可选
  • 控制使用的SQL插入子句:
  • None : 使用标准SQL INSERT 子句(每行一个)。
  • ‘multi’: 在单个INSERT子句中传递多个值。
  • 具有签名(pd_table, conn, keys, data_iter)的可调用对象。
  • 有关详细信息和一个可调用实现的示例,请参阅部分::ref:insert method <io.sql.method>。
• 返回:
  • None或int:受to_sql影响的行数。如果传入到method 的可调用对象不返回整数行数,则返回None。

users = pd.DataFrame(data=[[1, 'John', 25], [2, 'Jane', 30], [3, 'Bob', 35]], columns=['id', 'name', 'age'])

users.to_sql('users', engine, index=False)

(2)使用pd.io.sql.to_sql（）

 def to_sql(
        self,
        frame,
        name,
        if_exists: str = "fail",
        index: bool = True,
        index_label=None,
        schema=None,
        chunksize=None,
        dtype: DtypeArg | None = None,
        method=None,
        engine: str = "auto",
        **engine_kwargs,
    ) 

参数说明：

• frame:要写入的DataFrame
• name:SQL表名
• if_exists:{‘fail’, ‘replace’, ‘append’}, 默认’fail’
  • fail:如果表存在,不做任何操作
  • replace:如果表存在,删除表并重建表,然后插入数据
  • append:如果表存在,插入数据。如果表不存在,则创建表
• index : bool, 默认True
  • 将DataFrame的索引写入为一列
• index_label : string或sequence, 默认None
  • 索引列的列标签。如果为None(默认值)且index为True,则使用索引名称。
  • 如果DataFrame使用MultiIndex,则应提供一个序列
• schema : string, 默认None
  • 用于与SQLAlchemy的to_sql方法兼容的无视参数
• chunksize : int, 默认None
  • 如果不为None,则数据将分批写入,每批的大小为此值。如果为None,则一次性写入所有行。
• dtype : single type或dict, 默认None
  • 可选,指定列的数据类型。SQL类型应为字符串。如果所有列的类型相同,可以使用单个值。
• method : {None, ‘multi’, callable}, 默认None
  • 控制使用的SQL插入子句:
    • None : 使用标准SQL INSERT 子句(每行一个)。
    • ‘multi’: 在单个INSERT子句中传递多个值。
    • callable具有签名 (pd_table, conn, keys, data_iter) 的可调用对象。
    • 有关详细信息和一个可调用实现的示例,请参阅部分:ref:insert method <io.sql.method>。

例如：

pd.io.sql.to_sql(users, "users", engine , if_exists='replace')

表示将users这个dataframe写入engine指定的数据库中，表名为users，如果存在该表则删除后重新创建。