Pandas to_sql creating table but not inserting data

Question

I am using Oracle, and I am seeing the table created in the schema (albeit all with CLOB columns for VARCHARs). But no data is being inserted? The dataframe does have data in it.

engine = create_engine('oracle+cx_oracle://user:password@host:1521/?service_name=myservice', max_identifier_length=128)
df = get_registrations()
df.to_sql(name='pandas_registrations', con=engine, if_exists='replace', chunksize=1000, schema='ANALYTICS', method=None)

I read a commit() is not needed here Does the Pandas DataFrame.to_sql() function require a subsequent commit()?

Well I got it working without con.commit(). I had to replace clobs and set autocommit on the connection. None of which was described in any examples. I am not sure which one solved the problem to be honest. — smackenzie, Dec 04 '21 at 12:18
Apparently commit is not needed https://stackoverflow.com/questions/47741775/does-the-pandas-dataframe-to-sql-function-require-a-subsequent-commit — smackenzie, Dec 04 '21 at 12:21

score 0 · Answer 1 · answered Dec 03 '21 at 19:30

0

I encountered similar problem and switching to sqlalchemy instead of cx_Oracle as the engine helped in my scenario.

answered Dec 03 '21 at 19:30

Ashish Samarth

147
4

I am using sqlalchemy create_engine, just with the oracle driver? – smackenzie Dec 03 '21 at 20:30
SQLAlchemy uses cx_Oracle! – Christopher Jones Dec 03 '21 at 21:45
So what would my driver string look like? – smackenzie Dec 04 '21 at 12:17

Pandas to_sql creating table but not inserting data

1 Answers1