replace sqlite3 database table with pandas dataframe-CodePudding

My sqlite3 database look like the following:

id     name
1      apple
2      orange
3      pear
4      bananna

Then I query each element from the database into a pandas dataframe.

After that I merge the dataframe with a list.

After that I sort the database element by the list.

conn = sqlite3.connect('db.sqlite3', isolation_level=None, detect_types=sqlite3.PARSE_COLNAMES)
df = pd.read_sql_query('select * from name', conn)

#I am getting the list .....

df["color"] = color #color is a list
df.sort_values(by=['color'], ascending=True, inplace=True)

How could I replace the original sqlite3 table with the sorted dataframe? I do not want to add the color column to the sqlite3 table.

CodePudding user response：

The correct answer:

db.sort_values(by=['color'], ascending=True).drop('color',axis=1).to_sql("name",conn,if_exists='replace',index=False, index_label="id")