将Dataset对象转换为Pandas DataFrame的最简单方法是什么？

Question

Pythondataset.readthedocs.io/en/latest/quickstart.html" rel="nofollow noreferrer">Dataset module基于Sqlalchemy，它公开了一个函数，用于返回名为all（）的表中的所有记录。all（）返回iterable数据集对象。在

users = db['user'].all()

for user in db['user']:
   print(user['age'])

将Dataset对象转换为Pandas DataFrame对象的最简单方法是什么？

为了清楚起见，我对利用Dataset的功能感兴趣，因为它已经将表加载到Dataset对象中。在

Answer 1

在对Dataset module投入大量时间后，我发现all（）可以迭代到一个列表中，然后转换成pandas数据帧。有更好的方法吗？在

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()  # custom ResultIter type (iterable)

# iterate ResultIter type 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, but it should??
df_new.drop(columns=['id'])

print(df_new)

---

^{pr2}$

Answer 2

import pandas as pd
df = pd.DataFrame(data=db['user'])
df

同样

^{pr2}$

应该做同样的事情

也可以指定列或索引：

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html

Answer 3

这对我很有效：

import dataset
import pandas
db = dataset.connect('sqlite:///db.sqlite3')
data = list(db['my_table'].all())
dataframe = pandas.DataFrame(data=data)