BUG: io.sql.write_frame(if_exists='replace') not working as expected (with fix) · Issue #2971 · pandas-dev/pandas (original) (raw)

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.