def GetDataframe(self):
uDf = psql.read_sql(\
"SELECT * FROM " + str(self.name )
, db.getDb())
# CATEGORY df
cDf = psql.read_sql("SELECT id, name FROM categories", db.getDb())
cDf.columns = ['cdf_id', 'category']
uDf = pd.merge(uDf, cDf, left_on='category_id', right_on='cdf_id', how="left")
uDf = uDf[["id", "nr", "status", "name", "first_name", "category", "club", "year", "sex", "email", "o1", "o2", "o3", "o4"]]
return uDf
def __init__(self, table):
super(DfModelTimes, self).__init__(table)
self.df = pd.DataFrame()
self.conf = ListOfDicts(CONF_TABLE_TIMES)
self.db_con = db.getDb()
self.changed_rows = pd.DataFrame()
self.mynr = 0
def __init__(self, name):
print "I: CREATE: table", name
self.name = name
self.InitCollumns()
self.db_con = db.getDb()
def GetDataframe(self):
df = psql.read_sql(\
"SELECT * FROM " + str(self.name )
, db.getDb())
return df
# -*- coding: utf-8 -*-
if __name__ == "__main__":
from ewitis.data.db import db
import pandas as pd
import pandas.io.sql as psql
from pysqlite2 import dbapi2 as sqlite
# Create your connection.
cnx = sqlite.connect("db/test_db.sqlite", 5)
df1 = psql.read_frame('select * from times', cnx)
print df1, type(df1)
#exiting connection
df2 = psql.read_frame('select * from times', db.getDb())
print df2, type(df2)
#max
print "max", df1["time"].max()
print "max", df2["time"].max()
#sorting
Sorted = df1.sort(['time'], ascending = False)
print Sorted, type(Sorted)
#print Sorted.head(3) #first X rows
Sorted = df2.sort(['time'], ascending = False)
#print Sorted, type(Sorted)
print Sorted.head(3) #first X rows
idx = df1[df1['user_id']==1]['id'].argmax()
