The documentation for the Pandas function to_sql() available for DataFrame objects (see to_sql() documentation) does not state that a commit() call on the connection is needed (or recommended) to persist the update.
Can I safely assume that DataFrame.to_sql('table_name', con) will always automatically commit the changes (like in: con.commit())?
Yes, at the end of the day it will be commited automatically.
Pandas calls SQLAlchemy method executemany (for SQL Alchemy connections):
conn.executemany(self.insert_statement(), data_list)
And according to the SQL Alchemy docs executemany issues commit at the end.
For SQLite connection commit is called explicitly:
def run_transaction(self):
cur = self.con.cursor()
try:
yield cur
self.con.commit()
except:
self.con.rollback()
raise
finally:
cur.close()
to_sql() also works when SQLAlchemy is not installed, in which case it uses sqlite (if I understand the documentation correctly). Is the commit then also guaranteed?flavor in to_sql
create_engine(isolation_level="AUTOCOMMIT") for sqlite, so I'm not sure yet what is the default commit behavior with sqlite using sqlalchemy.