Pandas to_sql 不会在我的表中插入任何数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48307008/
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
Pandas to_sql doesn't insert any data in my table
提问by Nasia Ntalla
I am trying to insert some data in a table I have created. I have a data frame that looks like this:
我正在尝试在我创建的表中插入一些数据。我有一个如下所示的数据框：
I created a table:
我创建了一个表：
create table online.ds_attribution_probabilities
(
attribution_type text,
channel text,
date date ,
value float
)
And I am running this python script:
我正在运行这个 python 脚本：
engine = create_engine("postgresql://@e.eu-central-1.redshift.amazonaws.com:5439/mdhclient_encoding=utf8")
connection = engine.raw_connection()
result.to_sql('online.ds_attribution_probabilities', con=engine, index = False, if_exists = 'append')
I get no error, but when I check there are no data in my table. What can be wrong? Do I have to commit or do an extra step?
我没有收到错误，但是当我检查表中没有数据时。有什么问题？我是否必须提交或执行额外的步骤？
回答by MaxU
Try to specify a schema name:
尝试指定架构名称：
result.to_sql('ds_attribution_probabilities', con=engine,
schema='online', index=False, if_exists='append')
回答by lewk
Hopefully this helps someone else. to_sqlwill fail silently in the form of what looks like a successful insert if you pass a connection object. This is definitely true for Postgres, but i assume the same for others as well, based on the method docs:
希望这对其他人有帮助。to_sql如果您传递连接对象，则会以看似成功插入的形式静默失败。这对于 Postgres 来说绝对是正确的，但我假设其他人也是如此，基于方法文档：
con : sqlalchemy.engine.Engine or sqlite3.Connection
Using SQLAlchemy makes it possible to use any DB supported by that
library. Legacy support is provided for sqlite3.Connection objects.
This got me because the typing hints stated Union[Engine, Connection], which is "technically" true.
这让我着迷Union[Engine, Connection]，因为打字提示指出，这在“技术上”是正确的。
If you have a session with SQLAlchemytry passing con=session.get_bind(),
如果你有一个SQLAlchemy尝试通过的会话con=session.get_bind(),
回答by PierPuce
I had a similar issue caused by the fact that I was passing sqlalchemy connectionobject instead of engineobject to the conparameter. In my case tables were created but left empty.
我有一个类似的问题，因为我将 sqlalchemy连接对象而不是引擎对象传递给con参数。在我的情况下，表已创建但留空。
回答by Tony
Check the autocommitsetting: https://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit
检查autocommit设置：https: //docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit
engine.execute(text("SELECT my_mutating_procedure()").execution_options(autocommit=True))
回答by Gopal Mynam
I have a similar situation. Not able to insert pandas dataframe into oracle table. I am using cx_oracle and SQLalchemy for oracle connection. There is no error. I am using following command:
我有类似的情况。无法将 Pandas 数据框插入到 oracle 表中。我使用 cx_oracle 和 SQLalchemy 进行 oracle 连接。没有错误。我正在使用以下命令：
pd_df.to_sql('abc.xyz', conn, schema = 'abc', if_exists='replace', index='false')
回答by Flavio
I faced the same problem when I used .connect()and .begin()
我在使用.connect()和使用时遇到了同样的问题.begin()
with engine.connect() as conn, conn.begin():
dataframe.to_sql(name='table_name', schema='schema',
con=conn, if_exists='append', index=False)
conn.close()
Just remove the .connect()and .begin()and it will work.
只是删除.connect()，并.begin()和它的工作。
回答by being_felicity
This could happen because it defaults to the public database, and there's probably a table with that name under the public database/schema, with your data in it.
这可能发生，因为它默认为公共数据库，并且在公共数据库/模式下可能有一个具有该名称的表，其中包含您的数据。
@MaxU's answer does help some, but not the others. For others, here is something else you can try:
@MaxU 的回答确实对某些人有帮助，但对其他人没有帮助。对于其他人，您可以尝试以下其他方法：
When you create the engine, specify the schemaname like this:
创建引擎时，请像这样指定架构名称：
engine = create_engine(*<connection_string>*,
connect_args={'options': '-csearch_path={}'.format(*<dbschema_name>*)})
