Pandas to_sql doesn't insert any data in my table

Question

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:

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?

Dent

Answer 1

Try to specify a schema name:

result.to_sql('ds_attribution_probabilities', con=engine, 
              schema='online', index=False, if_exists='append')

Boulevardier

got me stuck as well. Thanks! – Kirkham

A clear bug, as for me. Not only it makes no sense, but it fails silently: 3 years passed, and it's still unsolved lurking there, waiting for the next prey... – Rodriques

I believe the table is created on the postgres public schema when the schema parameter is not passed, hence it is not 'silently failing' rather creating the table in a place where you don't expect. – Impearl

1+ or if you've already included the schema in the DB connection, don't put it in the table name. – Cambyses

If it is not bug, it is poor design – Grizzly

no my to_sql still inserts nothing into my mariadb even i have included schema, and index parameter into my syntax. – Circumscissile

@OlegO Another 4 years have passed, still fails silently. – Bibliogony

thank you for this! - I created the table in a new schema (not public). – Logsdon

Answer 2

Hopefully this helps someone else. to_sql will 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:

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.

If you have a session with SQLAlchemy try passing con=session.get_bind(),

Botulin

OMG, that was my problem! I cannot believe that is actually the current behavior o_O – Metope

Whats worse is that if its failing silently due to an unspecified explicit schema passed as an argument, it creates the table in your publiv schema! Thats where all your disappearing data is being appended to! – Parthenogenesis

beware the "silent fail" when using engine.connect() for the to_sql con param! So much time wasted debugging, would like for this to be addressed – Dedication

Answer 3

I had a similar issue caused by the fact that I was passing sqlalchemy connection object instead of engine object to the con parameter. In my case tables were created but left empty.

Comitia

Worked for me self.engine = create_engine(f'mysql+pymysql://{self.db_user}:{self.db_password}@{self.db_host}:{self.db_port}/{self.db_name}') with self.engine.connect() as conn: result = df.to_sql('table-name', self.engine, if_exists='append', index=False) earlier I was passing conn instead of engine obj – Chemical

I don't understand, all the other answer doesn't work, including this https://mcmap.net/q/399533/-pandas-to_sql-doesn-39-t-insert-any-data-in-my-table, but this specific one right above my comment ( by Ravi Bhanushali Feb 21 at 14:57 ) works perfectly. If yours is not in a Class function just feel free to remove the "self.". – Circumscissile

Answer 4

In my case, writing data to the database was hampered by the fast option.

Why is this not fast loading interfering, I have not yet figured out.

This code doesn't work:

engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect={}".format(db_params), fast_executemany=True)
df.to_sql('tablename', engine, index=False, schema = 'dbo', if_exists='replace' )

Without fast_executemany=True the code works well.

Llama

Answer 5

Check the autocommit setting: https://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit

engine.execute(text("SELECT my_mutating_procedure()").execution_options(autocommit=True))

Staciastacie

Answer 6

I faced the same problem when I used .connect() and .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.

Tiddlywinks

Isn't the whole point of a context manager is you don't have to call close() or begin()? – Corruption

Answer 7

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:

When you create the engine, specify the schemaname like this:

engine = create_engine(*<connection_string>*,
    connect_args={'options': '-csearch_path={}'.format(*<dbschema_name>*)})

Link: https://mcmap.net/q/188052/-sqlalchemy-support-of-postgres-schemas

Schutt

Answer 8

use method=None

None : Uses standard SQL INSERT clause (one per row).

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html

Mine worked like this:

df.to_sql(name=table_name, method=None, schema=schema, index=False, if_exists='append', chunksize=50, con=conn.get_bind())

*table_name without prepending the schema name

Highfalutin

Answer 9

Try adding commit after you code, like this:

result.to_sql('ds_attribution_probabilities', con=engine, 
          schema='online', index=False, if_exists='append')
engine.commit()

Works for me.

Ardelia

Answer needs supporting information Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Bashful

I don't think you can engine.commit(), it would be connection.commit(). – Souther

Answer 10

5 Years later faced the same issue with PostgreSQL.

I solved it by passing the actual connection object instead of the engine itself (which I did previously).

engine = create_engine('postgresql://username:password@localhost:5432/database_name')
with engine.connect() as connection:
     dataset.to_sql(name='table_name', con=connection, schema='schema_name', if_exists='append', chunksize=1000, index=False)

Previously I was doing

engine = create_engine('postgresql://username:password@localhost:5432/database_name')
dataset.to_sql(name='table_name', con=engine, schema='schema_name', if_exists='append', chunksize=1000, index=False)

As you may have noticed above that I have passed the schema_name too. I did that after I saw the responses above. But that did not solve the issue.

While the table was created in the DB, no records were written into it. I found it pretty strange due to this last bit. I could have fully understood if the table itself wasn't getting created and I would have concluded that Python is failing to establish a database connection altogether.

Intravasation

Answer 11

Check that you are passing in the database to create_engine

for postgres, it will be something like this

engine = create_engine(f'postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}')

Volkman

Answer 12

This is what worked for me,

engine = create_engine('postgresql://username:password@localhost:5432/database_name')
with engine.begin() as connection:
     dataset.to_sql(name='table_name', con=connection, schema='schema_name', if_exists='append', index=False)

Use engine.begin()

Rigidify