How to open and convert sqlite database to pandas dataframe

Question

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

I still did it

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

Answer 1

Although sqlite is part of the Python standard library and provides a convenient and convenient interface for SQLite databases, the Pandas manual says:

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

But Pandas still supports access to sqlite3 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 must know the name of the table used in advance.

Hope this helps!

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

Answer 2

Line

 data = sqlite3.connect('data.db') 

opens a connection to the database. No records requested before. Therefore, you need to fulfill the request and provide it to the pandas DataFrame .

It should look something like 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 very good at SQL commands, so you should check if the query is correct. There must be a table name in your database.

Answer 3

Search sqlalchemy , engine and database name in google (in this case 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) 

Answer 4

I saved my data in the database.sqlite table is Reviews file

 import sqlite3 con=sqlite3.connect("database.sqlite") data=pd.read_sql_query("SELECT * FROM Reviews",con) print(data)