BUG: in io.sql.write_frame (replace) · Issue #4110 · pandas-dev/pandas (original) (raw)

From http://pandas.pydata.org/pandas-docs/dev/generated/pandas.io.sql.write_frame.html :

Parameters: (...)
if_exists: {‘fail’, ‘replace’, ‘append’}, default ‘fail’ :
fail: If table exists, do nothing. replace: If table exists, drop it, recreate it, 
and insert data. append: If table exists, insert data. Create if does not exist.

Everything works fine as long as the table does not exist.
If table already exists :

sql.write_frame(sp5002, name="sp500", con=conn, if_exists='replace')

-> "sqlite3.OperationalError: no such table: sp500"

Reason is that there is no new "create" statement after the drop statement:

create = None
if exists and if_exists == 'replace':
    create = "DROP TABLE %s" % name
elif not exists:
    create = get_schema(frame, name, flavor)

In my opinion it should be changed to something like: (?)

create = None
if exists and if_exists == 'replace':
    create = "DROP TABLE %s" % name + "\n"+get_schema(frame, name, flavor)
elif not exists:
    create = get_schema(frame, name, flavor)

If i manually work around that, everything works as expected:

exists = sql.table_exists('sp500', conn, 'sqlite')
if not exists:
    sql.write_frame(sp5002, name="sp500", con=conn, if_exists='replace')
else:
    create = "DROP TABLE sp500"
    cur = conn.cursor()
    cur.execute(create)
    cur.close()
    sql.write_frame(sp5002, name="sp500", con=conn, if_exists='replace')

#2971