从 Pandas 数据框生成 SQL 语句

Question

我正在将来自各种来源（csv、xls、json 等）的数据加载到 Pandas 数据帧中，我想生成语句来创建并用这些数据填充 SQL 数据库。有谁知道这样做的方法？

我知道 pandas 有一个 to_sql 函数，但它只适用于数据库连接，它不能生成字符串。

例子

我想要的是采用这样的数据框：

 import pandas as pd
import numpy as np

dates = pd.date_range('20130101',periods=6)
df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))

还有一个会生成这个的函数（这个例子是 PostgreSQL，但任何一个都可以）：

 CREATE TABLE data
(
  index timestamp with time zone,
  "A" double precision,
  "B" double precision,
  "C" double precision,
  "D" double precision
)

原文由 Jorick Spitzen 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

如果你只想要’CREATE TABLE’ sql 代码（而不是插入数据），你可以使用 pandas.io.sql 模块的 get_schema 函数：

 In [10]: print pd.io.sql.get_schema(df.reset_index(), 'data')
CREATE TABLE "data" (
  "index" TIMESTAMP,
  "A" REAL,
  "B" REAL,
  "C" REAL,
  "D" REAL
)

一些注意事项：

• 我不得不使用 reset_index 因为它不包含索引
• 如果您提供某种数据库风格的 sqlalchemy 引擎，则结果将调整为该风格（例如数据类型名称）。

原文由 joris 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

如果您只是想根据 pandas.DataFrame 生成带有插入的字符串 - 我建议使用 @rup 建议的批量 sql 插入语法。

这是我为此目的编写的 函数 示例：

 import pandas as pd
import re

def df_to_sql_bulk_insert(df: pd.DataFrame, table: str, **kwargs) -> str:
    """Converts DataFrame to bulk INSERT sql query
    >>> data = [(1, "_suffixnan", 1), (2, "Noneprefix", 0), (3, "fooNULLbar", 1, 2.34)]
    >>> df = pd.DataFrame(data, columns=["id", "name", "is_deleted", "balance"])
    >>> df
       id        name  is_deleted  balance
    0   1  _suffixnan           1      NaN
    1   2  Noneprefix           0      NaN
    2   3  fooNULLbar           1     2.34
    >>> query = df_to_sql_bulk_insert(df, "users", status="APPROVED", address=None)
    >>> print(query)
    INSERT INTO users (id, name, is_deleted, balance, status, address)
    VALUES (1, '_suffixnan', 1, NULL, 'APPROVED', NULL),
           (2, 'Noneprefix', 0, NULL, 'APPROVED', NULL),
           (3, 'fooNULLbar', 1, 2.34, 'APPROVED', NULL);
    """
    df = df.copy().assign(**kwargs)
    columns = ", ".join(df.columns)
    tuples = map(str, df.itertuples(index=False, name=None))
    values = re.sub(r"(?<=\W)(nan|None)(?=\W)", "NULL", (",\n" + " " * 7).join(tuples))
    return f"INSERT INTO {table} ({columns})\nVALUES {values};"

By the way, it nan / None entries to NULL and it’s possible to pass constant column=value pairs as keyword arguments (see status="APPROVED" 和 address=None 文档字符串示例中的参数）。

通常，它的工作速度更快，因为任何数据库都为单个插入执行大量工作：检查约束、构建索引、刷新、写入日志等。数据库在执行多合一操作时可以优化这种复杂的操作，而不是一一调用引擎。

原文由 absoup 发布，翻译遵循 CC BY-SA 4.0 许可协议