BUG: io.sql.write_frame(if_exists='replace') not working as expected (with fix) #2971
Description
If if_exists='replace' and the table already exists, then the following error occurs:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 202, in write_frame
cur.execute(create)
sqlite3.OperationalError: no such table: aggregatedData
This is the code I ran with no issue:
psql.write_frame(outDf, 'aggregatedData', conn)
This is the code I ran to get the above error:
psql.write_frame(outDf, 'aggregatedData', conn, if_exists='replace')
If the same code is re-run after the error it will work as expected because the table is not there anymore. Running the code a third time will cause the error; the error happens every other run.
Here's the fix I implemented in pandas.io.sql.py (~line 191):
# create or drop-recreate if necessary
create = None
if exists and if_exists == 'replace':
create = "DROP TABLE %s" % name
cur = con.cursor()
cur.execute(create)
cur.close()
create = get_schema(frame, name, flavor)
Basically, I just dropped the existing table and set it to be recreated later in the code.