python之Pandas write_frame 删除sqlite表

当尝试用 Pandas dataframe 的内容覆盖 sqlite 表时，Pandas DROP 表，但在尝试 之前不会重新创建它插入。

这是一个最小的工作示例:

import sqlite3 as sq 
import pandas as pd 
import pandas.io.sql as pd_sql 
 
d = pd.DataFrame({'year':[2008,2008,2008,2008,2009,2009,2009,2009],  
                  'flavour':['strawberry','strawberry','banana','banana', 
                  'strawberry','strawberry','banana','banana'], 
                  'day':['sat','sun','sat','sun','sat','sun','sat','sun'], 
                  'sales':[10,12,22,23,11,13,23,24]}) 
 
# Connect to the database (create if necessary)                   
conn = sq.connect('mydb') 
 
# Create the table 'mytable' if necessary 
if not pd_sql.table_exists('mytable', conn, 'sqlite'): 
    pd_sql.write_frame(d, 'mytable', conn) 
 
# Change some data     
d['sales'][d.sales==24] = 25 
 
# Confirm the table exists 
print "Table 'mytable' exists:" 
print pd_sql.table_exists('mytable', conn, 'sqlite') 
 
# Get some data from the table 
cur = pd_sql.execute("SELECT DISTINCT flavour FROM mytable", conn) 
print "Here's the data to prove the table exists:" 
print cur.fetchall() 
 
try: 
    print "Attempting write_frame..." 
    pd_sql.write_frame(d, 'mytable', conn, if_exists='replace') 
except sq.OperationalError as e: 
    print "sq.OperationalError is: " + str(e) 
    print pd_sql.table_exists('mytable', conn, 'sqlite') 
    conn.close() 

运行此脚本会产生以下输出:

Table 'mytable' exists: 
True 
Here's the data to prove the table exists: 
[(u'banana',), (u'strawberry',)] 
Attempting write_frame... 
sq.OperationalError is: no such table: mytable 
Table 'mytable' exists after write_frame: 
False 

这看起来像是 Pandas 中的一个错误。有谁能证实这一点？

一如既往的感谢， 罗布

请您参考如下方法:

在原贴的评论中，已确认这是 Pandas 中的一个已知错误。

这个解决方法对我来说似乎没问题:

pd_sql.uquery("DELETE FROM mytable", conn) 
pd_sql.write_frame(d, 'mytable', conn, if_exists='append')