pandas 当我没有表对象时，如何在 SQLAlchemy 中删除表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33229140/
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
How do I drop a table in SQLAlchemy when I don't have a table object?
提问by olaf
I want to drop a table (if it exists) before writing some data in a Pandas dataframe:
在 Pandas 数据框中写入一些数据之前，我想删除一个表（如果它存在）：
def store_sqlite(in_data, dbpath = 'my.db', table = 'mytab'):
database = sqlalchemy.create_engine('sqlite:///' + dbpath)
## DROP TABLE HERE
in_data.to_sql(name = table, con = database, if_exists = 'append')
database.close()
The SQLAlchemy documentation all points to a Table.drop()object - how would I create that object, or equivalently is there an alternative way to drop this table?
SQLAlchemy 文档都指向一个Table.drop()对象 - 我将如何创建该对象，或者等效地是否有另一种方法来删除该表？
Note: I can't just use if_exists = 'replace'as the input data is actually a dict of DataFrames which I loop over - I've suppressed that code for clarity (I hope).
注意：我不能仅仅使用，if_exists = 'replace'因为输入数据实际上是我循环的 DataFrame 的字典 - 为了清晰起见，我已经抑制了该代码（我希望）。
回答by Paul
From the panda docs;
来自Pandas文档；
"You can also run a plain query without creating a dataframe with execute(). This is useful for queries that don't return values, such as INSERT. This is functionally equivalent to calling execute on the SQLAlchemy engine or db connection object."
“您还可以在不使用 execute() 创建数据帧的情况下运行普通查询。这对于不返回值的查询非常有用，例如 INSERT。这在功能上等同于在 SQLAlchemy 引擎或数据库连接对象上调用 execute。”
http://pandas.pydata.org/pandas-docs/version/0.18.0/io.html#id3
http://pandas.pydata.org/pandas-docs/version/0.18.0/io.html#id3
So I do this;
所以我这样做；
from pandas.io import sql
sql.execute('DROP TABLE IF EXISTS %s'%table, engine)
sql.execute('VACUUM', engine)
Where "engine" is the SQLAlchemy database object (the OP's "database" above). Vacuum is optional, just reduces the size of the sqlite file (I use the table drop part infrequently in my code).
其中“引擎”是 SQLAlchemy 数据库对象（上面的 OP 的“数据库”）。Vacuum 是可选的，只是减小了 sqlite 文件的大小（我在我的代码中很少使用 table drop 部分）。
回答by John Sandall
You should be able to create a cursor from your SQLAlchemy engine
您应该能够从 SQLAlchemy 引擎创建游标
import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///' + dbpath)
connection = engine.raw_connection()
cursor = connection.cursor()
command = "DROP TABLE IF EXISTS {};".format(table)
cursor.execute(command)
connection.commit()
cursor.close()
# Now you can chunk upload your data as you wish
in_data.to_sql(name=table, con=engine, if_exists='append')
If you're loading a lot of data into your db, you may find it faster to use pandas' to_csv()and SQL's copy_fromfunction. You can also use StringIO()to hold it in memory and having to write the file.
如果您将大量数据加载到数据库中，您可能会发现使用 Pandasto_csv()和 SQL 的copy_from函数会更快。您还可以使用StringIO()将其保存在内存中而不必写入文件。
