使用 Pandas 在 MySQL 中创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34192473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a temporary table in MySQL using Pandas
提问by Monica Heddneck
Pandas has a great feature, where you can write your dataframe to a table in SQL.
Pandas 有一个很棒的功能，您可以在其中将数据框写入 SQL 表中。
df.to_sql(con=cnx, name='some_table_name', if_exists='replace', flavor='mysql', index=False)
df.to_sql(con=cnx, name='some_table_name', if_exists='replace', flavor='mysql', index=False)
Is there a way to make a temporary table this way?
有没有办法以这种方式制作临时表？
There is nothing in the documentation as far as I can tell.
据我所知，文档中没有任何内容。
回答by alecxe
The DataFrame.to_sql()uses the built into pandas pandas.io.sqlpackage, which itself relies on the SQLAlchemy as a database abstraction layer. In order to create a "temporary" table in SQLAlchemy ORM, you need to supply a prefix:
在DataFrame.to_sql()使用内置到大Pandaspandas.io.sql包，其本身依赖于SQLAlchemy的作为数据库抽象层。为了在 SQLAlchemy ORM 中创建“临时”表，您需要提供一个前缀：
t = Table(
't', metadata,
Column('id', Integer, primary_key=True),
# ...
prefixes=['TEMPORARY'],
)
From what I see, pandas.io.sqldoes not allow you to specify the prefixesor easily change the way tables are created.
据我所知，pandas.io.sql不允许您指定prefixes或轻松更改创建表的方式。
One way to approach this problem would be to create the temporary table beforehandand use to_sql()with if_exists="append"(all using the same database connection).
解决此问题的一种方法是预先创建临时表并使用to_sql()with if_exists="append"（都使用相同的数据库连接）。
Here is also what I've tried to do: override the pandas.io.sql.SQLTable's _create_table_setup()method and pass the prefixesto the Tableconstructor. For some reason, the table was still created non-temporary. Not sure if it would help, but here is the code I was using: gist. This is kind of hacky, but I hope it would at least serve as an example code to get you started on this approach.
这也是我尝试做的：覆盖pandas.io.sql.SQLTable的_create_table_setup()方法并将 传递prefixes给Table构造函数。出于某种原因，该表仍然是非临时创建的。不确定它是否会有所帮助，但这是我使用的代码：gist。这有点 hacky，但我希望它至少可以作为示例代码让您开始使用这种方法。
回答by Eric Ed Lohmar
This may be a bit hacky and it doesn't technically create a temporary table, it just acts like one, but you could create use the @contextmanagerdecorator from contextlibto create the table upon opening the context and drop it upon close. Could look something like:
这可能有点 hacky，它在技术上并没有创建临时表，它只是像一个临时表一样，但是您可以在打开上下文时使用@contextmanager装饰器contextlib创建表并在关闭时将其删除。可能看起来像：
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"
I tested it using Teradata and it works fine. I don't have a MySQL laying around that I can test it out on, but as long as DROPstatements work in MySQL, it should work as intended.
我使用 Teradata 对其进行了测试，并且运行良好。我没有可以测试的 MySQL，但只要DROP语句在 MySQL 中工作，它就应该按预期工作。
