使用Pandas在MySQL中创建临时表

Question

Pandas有一个很棒的功能，可以将数据框写入SQL表格中。

df.to_sql(con=cnx, name='some_table_name', if_exists='replace', flavor='mysql', index=False)

有没有一种方法可以以这种方式创建临时表？

据我所知，文档中没有提到任何内容。

- Monica Heddneck

Answer 1

DataFrame.to_sql()使用内置于pandas pandas.io.sql包中的内容，该包本身依赖于SQLAlchemy作为数据库抽象层。为了在SQLAlchemy ORM中创建一个“临时”表，您需要提供一个前缀：

t = Table(
    't', metadata,
    Column('id', Integer, primary_key=True),
    # ...
    prefixes=['TEMPORARY'],
)

我所看到的是，pandas.io.sql 不允许您指定前缀或轻松更改表的创建方式。

解决此问题的一种方法是预先创建临时表，并使用to_sql()和if_exists="append"（都使用同一个数据库连接）。

---

以下是我尝试过的方法：重写pandas.io.sql.SQLTable的_create_table_setup()方法，并将prefixes传递给Table构造函数。由于某些原因，表仍然被创建为非临时表。不确定是否有帮助，但这是我使用的代码：gist。这种方法有点hacky，但我希望它至少可以作为一个示例代码，让你开始尝试这种方法。

- alecxe

---

函数_create_table_setup仅在__init__中被调用以设置table属性。要使用pandas.io.sql.SQLTable创建表格，您需要调用create，它会调用_execute_create，并覆盖table属性。这就是为什么您的尝试没有成功的原因。相反，您需要编辑_execute_create：https://gist.github.com/alecxe/44682f79b18f0c82a99c#gistcomment-2359365 - Guilherme Beltramini

Answer 2

简单易行的解决方案，无需花哨的魔法

对我来说，这是一个快速简单的解决方案。

只需将正则表达式应用于生成的 SQL 中，添加任何你想要的语句即可。

import io
import pandas as pd

# Get the SQL that would be generated by the create table statement
create_table_sql = pd.io.sql.get_schema(df, tmp_table_name)

# Replace the `CREATE TABLE` part of the generated statement with 
# whatever you need.
create_tmp_table_sql = re.sub(
    "^(CREATE TABLE)?",
    "CREATE TEMP TABLE",
    create_table_sql
)

接下来，你可以像这样使用它：

# Write to the database in a transaction (psycopg2)
with conn.cursor() as cur:
    cur.execute(create_tmp_table_sql)
    output = io.StringIO()
    df.to_csv(output, sep="\t", header=False, index=False, na_rep="NULL")
    output.seek(0)
    cur.copy_from(output, tmp_table_name, null="NULL")

感谢Aseem提供了一种快速向Postgres写入数据的方法。

- iamyojimbo

Answer 3

这可能有点巧妙，它并不是技术上创建临时表，而是像一个临时表一样运作，但是你可以使用contextlib中的@contextmanager装饰器，在打开上下文时创建表，在关闭上下文时删除表。可能看起来像这样：

from contextlib import contextmanager

import numpy as np
import sqlalchemy as sqla
import pandas as pd


@contextmanager
def temp_table(frame, tbl, eng, *args, **kwargs):
    frame.to_sql(tbl, eng, *args, **kwargs)
    yield
    eng.execute('DROP TABLE {}'.format(tbl))

df = pd.DataFrame(np.random.randint(21, size=(10, 10)))
cnx = sqla.create_engine(conn_string)

with temp_table(df, 'some_table_name', cnx, if_exists='replace', flavor='mysql', index=False):
    # do stuff with "some_table_name"

我使用Teradata测试过，它可以正常工作。我没有MySQL可以测试它，但只要DROP语句在MySQL中可用，它应该按预期工作。

- Eric Ed Lohmar