In Python, save a Pandas DataFrame to Sqlite using Dataset module and retrieve into Dataframe utilizing only Dataset function all().

dataframe_dataset_dataframe_roundtrip.py

import dataset
import pandas as pd

# create dataframe
df = pd.DataFrame()
names = ['Bob', 'Jane', 'Alice', 'Ricky']
ages = [31, 30, 31, 30]
df['names'] = names
df['ages'] = ages

print(df)

# create a dict oriented as records from dataframe
user = df.to_dict(orient='records')

# using dataset module instantiate database
db = dataset.connect('sqlite:///mydatabase.db')

# create a reference to a table
table = db['user']

# insert the complete dict into database
table.insert_many(user)

# use Dataset .all() to retrieve all table's rows
from_sql = table.all()  # ordered dictionary

# iterate ordered dict into a list
data = []
for row in from_sql:
    data.append(row)

# create dataframe from list and ordereddict keys
df_new = pd.DataFrame(data, columns=from_sql.keys)

# this does not drop the id column??
df_new.drop(columns=['id'])

print(df_new)


'''
   names  ages
0    Bob    31
1   Jane    30
2  Alice    31
3  Ricky    30

      id  names  ages
0      1    Bob    31
1      2   Jane    30
2      3  Alice    31
3      4  Ricky    30

'''