how to create pandas data frame from sqlite

Question

I have made the following program, I am fetching data from sqlite table and want to create a pandas data frame.

import sqlite3 as lite
import pandas as pd
con=lite.connect('/Users/mac/Desktop/Python/Baye_stat/productiondisruption/PCI_meat.sqlite')
cur=con.cursor()

cur.execute("SELECT * from InmateLostHours")

losthours = cur.fetchall()
k=len(losthours)-1
jan=[]
feb=[]
march=[]
april=[]
may=[]
june=[]
july=[]
aug=[] 
sept=[]
october=[]
nov=[]
dec=[]
for i in range(0,k):
  may.append((losthours[i][3])/(losthours[i][15]))
  june.append((losthours[i][4])/(losthours[i][15]))
  july.append((losthours[i][5])/(losthours[i][15]))
  aug.append((losthours[i][6])/(losthours[i][15]))
  sept.append((losthours[i][7])/(losthours[i][15]))
  october.append((losthours[i][8])/(losthours[i][15]))
  nov.append((losthours[i][9])/(losthours[i][15]))
  dec.append((losthours[i][10])/(losthours[i][15]))
  jan.append((losthours[i][11])/(losthours[i][15]))
  feb.append((losthours[i][12])/(losthours[i][15]))
  march.append((losthours[i][13])/(losthours[i][15]))
  april.append((losthours[i][14])/(losthours[i][15]))

institutionhours=pd.DataFrame({
            'May' :[may],
            'June':[june],
            'July':[july],
            'August':[aug],
            'September':[sept],
            'October':[october],
            'November':[nov],
            'December':[dec],
            'January':[jan],
            'Feburary':[feb],
            'March':[march],
            'April':[april]
            })

I want a clean data frame with size (16, 12), but I am getting enter image description here

Don't reinvent the wheel: pandas.pydata.org/pandas-docs/stable/generated/… — DeepSpace
– DeepSpace, Commented Dec 20, 2016 at 15:44

Demetri Pananos · Accepted Answer · 2016-12-20 15:54:07Z

15

This becomes really easy when using pandas.

import pandas as pd
import sqlite3

con = sqlite3.connect('/Users/mac/Desktop/Python/Baye_stat/productiondisruption/PCI_meat.sqlite')

df = pd.read_sql(<your query here>, con)

answered Dec 20, 2016 at 15:54

Demetri Pananos

7,54411 gold badges49 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sanjeev Over a year ago

Thanks a lot! I was really inventing the wheel again

Collectives™ on Stack Overflow

how to create pandas data frame from sqlite

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related