Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to open and convert sqlite database to pandas dataframe

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) 

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

like image 362
Eka Avatar asked Mar 16 '16 07:03

Eka


People also ask

How fetch data from sqlite3 database in Python?

SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.


2 Answers

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

Note In order to use read_sql_table(), you must have the SQLAlchemy optional dependency installed.

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) 

As stated here, but you need to know the name of the used table in advance.

like image 105
Mike Avatar answered Sep 20 '22 07:09

Mike


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 DataFrame constructor.

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.

like image 41
RaJa Avatar answered Sep 21 '22 07:09

RaJa