如何打开SQLite数据库并将其转换为Pandas DataFrame [英] How to open and convert sqlite database to pandas dataframe

问题描述

我已经下载了一些数据作为sqlite数据库(data.db)，我想用python打开此数据库，然后将其转换为pandas数据框.

I have downloaded some datas as a sqlite database (data.db) and I want to open this database in python and then convert it into pandas dataframe.

到目前为止，我已经完成了

This is so far I have done

import sqlite3
import pandas    
dat = sqlite3.connect('data.db') #connected to database with out error
pandas.DataFrame.from_records(dat, index=None, exclude=None, columns=None, coerce_float=False, nrows=None)

但是它抛出了这个错误

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 980, in from_records
    coerce_float=coerce_float)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 5353, in _to_arrays
    if not len(data):
TypeError: object of type 'sqlite3.Connection' has no len()

如何将sqlite数据库转换为熊猫数据框

How to convert sqlite database to pandas dataframe

推荐答案

尽管sqlite是Python标准库的一部分，但它是SQLite数据库的简单易用的接口，Pandas教程指出:

Despite sqlite being part of the Python Standard Library and is a nice and easy interface to SQLite databases, the Pandas tutorial states:

注意:要使用read_sql_table()，必须具有SQLAlchemy 安装了可选的依赖项. http://pandas.pydata.org/pandas-docs/stable /io.html#reading-tables

Note In order to use read_sql_table(), you must have the SQLAlchemy optional dependency installed. http://pandas.pydata.org/pandas-docs/stable/io.html#reading-tables

但是，如果要避免安装SQLAlchemy，Pandas仍支持sqlite3访问:

But Pandas still supports sqlite3 access if you want to avoid installing SQLAlchemy:

import sqlite3
import pandas as pd
# Create your connection.
cnx = sqlite3.connect('file.db')

df = pd.read_sql_query("SELECT * FROM table_name", cnx)

但是您需要提前知道所用表的名称.

But you need to know the name of the used table in advance.

希望有帮助！

http://pandas.pydata.org/pandas- docs/stable/io.html#sqlite-fallback

这篇关于如何打开SQLite数据库并将其转换为Pandas DataFrame的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！