Python 如何打开sqlite数据库并将其转换为pandas数据框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36028759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to open and convert sqlite database to pandas dataframe
提问by Eka
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.
我已经下载了一些数据作为 sqlite 数据库 (data.db)，我想在 python 中打开这个数据库，然后将其转换为 Pandas 数据帧。
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)
But its throwing this error
但它抛出这个错误
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()
How to convert sqlite database to pandas dataframe
如何将sqlite数据库转换为pandas数据框
回答by Mike
Despite sqlite being part of the Python Standard Library and is a nice and easy interface to SQLite databases, the Pandas tutorial states:
尽管 sqlite 是 Python 标准库的一部分，并且是 SQLite 数据库的一个很好且简单的界面，但 Pandas 教程指出：
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
注意为了使用 read_sql_table()，您必须安装 SQLAlchemy 可选依赖项。 http://pandas.pydata.org/pandas-docs/stable/io.html#reading-tables
But Pandas still supports sqlite3 access if you want to avoid installing SQLAlchemy:
但是如果你想避免安装 SQLAlchemy，Pandas 仍然支持 sqlite3 访问：
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.
但是你需要提前知道使用的表的名称。
Hope it helps!
希望能帮助到你！
http://pandas.pydata.org/pandas-docs/stable/io.html#sqlite-fallback
http://pandas.pydata.org/pandas-docs/stable/io.html#sqlite-fallback
回答by RaJa
The line
线
data = sqlite3.connect('data.db')
opens a connection to the database. There are no records queried up to this. So you have to execute a query afterward and provide this to the pandas DataFrameconstructor.
打开与数据库的连接。没有查询到此的记录。因此，您必须在之后执行查询并将其提供给 pandasDataFrame构造函数。
It should look similar to this
它应该与此类似
import sqlite3
import pandas as pd
dat = sqlite3.connect('data.db')
query = dat.execute("SELECT * From <TABLENAME>")
cols = [column[0] for column in query.description]
results= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)
I am not really firm with SQL commands, so you should check the correctness of the query. should be the name of the table in your database.
我对 SQL 命令不是很了解，所以你应该检查查询的正确性。应该是数据库中表的名称。
回答by user3226167
Search sqlalchemy, engineand database name in google (sqlite in this case):
搜索sqlalchemy，engine和数据库名称中谷歌（在这种情况下sqlite的）：
import pandas as pd
import sqlalchemy
db_name = "data.db"
table_name = "LITTLE_BOBBY_TABLES"
engine = sqlalchemy.create_engine("sqlite:///%s" % db_name, execution_options={"sqlite_raw_colnames": True})
df = pd.read_sql_table(table_name, engine)
回答by vignesh babu
i have stored my data in database.sqlite table name is Reviews
我已经将我的数据存储在 database.sqlite 表名是 Reviews
import sqlite3
con=sqlite3.connect("database.sqlite")
data=pd.read_sql_query("SELECT * FROM Reviews",con)
print(data)
