BUG: io.sql.write_frame(if_exists='replace') not working as expected (with fix)

[davidstackio]

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.

[iamlemec]

I can confirm this. The proposed fix worked well for me.

[jreback]

related to #4163.

[hayd]

http://stackoverflow.com/questions/18132006/pandas-write-frame-deletes-sqlite-table

[ghost]

We've got to get this fixed in 0.14 or sooner. You can't just drop people's tables
and not fix it for 11 months. That's crazy.

[jreback]

doesn't this #6164 close the issue?