Why I'm not recovering in python variables the same counts as SQL from table -

Why I'm not recovering in python variables the same counts as SQL from table -

this first post in stack overflow , i'm trying concise can. have experience in sql i'm starting code python. have weird result getting sql info in python variables , seems i'm doing wrong can't find.

my sqlite table got 26244 row sql query shows:

table = 'datoslaboratorio' sqlquery = "select count(*) %s" % table rows = cursor.execute(sqlquery).fetchone()[0] print(rows) 26244

however when seek summarize table, python not recovering same figures:

sqlquery = "select familia, count(*) num %s grouping familia order familia" % table rows = cursor.execute(sqlquery).fetchall() conn.commit() # sum totals grouped in field 1 (num) count=0 row in rows: count=count+row[1] print(count) 8862

i have verified direct sql query against sqlite gives right figures:

select sum(num) total (select familia, count (*) num datoslaboratorio grouping familia) total 26244

worse, when seek info in dataframe using pandas, don't same counts, seems pandas reads in 33 valid rows, have values in 26244 records:

sqlquery = "select * %s" % table df = pd.read_sql (sqlquery,conn) conn.commit() df.count() id 33 seccion 0 fecha 33 familia 33 codigo 33 extractoseco 33 materiagrasa 33 sal 33 ph 33 observaciones 33 phsalmuera 0 temperaturasalmuera 4 densidadsalmuera 4

what missing? give thanks in advance help!

@hrabal: adding output

this sql output of query on sqlite:

select familia, count (*) num datoslaboratorio grouping familia recno familia num 1 cabra barra tierno 297 2 cabra madurado 3 kg 29 3 cabra madurado mini 44 4 cabra tierno 3 kgs 140 5 cabra tierno barra 4,2 50 6 cabra tierno mini 258 7 gran capitan 3 kgs 2 8 madurado 3 kg sl 2588 9 madurado 3 kgs iqm 315 10 madurado 3 kgs s/lis 308 11 madurado 3kg cl 1229 12 madurado barra 1585 13 madurado barra 4,2 523 14 madurado barra iqm 60 15 madurado barra iqm 4,2 41 16 madurado mini 1393 ... 50 tierno mini iqm 142 51 tierno mini lite 572 52 tierno pÑo 323 53 tierno pÑo iqm 2124 54 tierno soja 3 kgs 3 55 tierno soja barra 14 56 tierno soja mini 4

so result 56 rows info grouped "familia", , sum("num") = 26244

when print python, doesn't seems read data:

sqlquery = "select familia, count(*) num %s grouping familia order familia" % table rows = cursor.execute(sqlquery).fetchall() conn.commit() columns = [column[0] column in cursor.description] print(columns) row in rows: print (row[0],row[1]) ['familia', 'num'] cabra barra tierno 297 cabra madurado 3 kg 29 cabra madurado mini 44 cabra tierno 3 kgs 140 cabra tierno barra 4,2 50 cabra tierno mini 258 gran capitan 3 kgs 2 madurado 3 kg sl 2588 madurado 3 kgs iqm 315 madurado 3 kgs s/lis 308 madurado 3kg cl 1229 madurado barra 1585 madurado barra 4,2 523 madurado barra iqm 60 madurado barra iqm 4,2 41 madurado mini 1393

that's info python reading in, apparently: 16 first lines, or @ to the lowest degree not able rest of info in. should reading 56 rows. , pandas doesn't read info neither.

all can think problem in .fetchall().. since python giving first 16 rows, fetchall() not working seek using .fetchone() (if have little dataset) or generator .fetchmany():

def resultgenerator(cursor, arraysize=8): while true: results = cursor.fetchmany(arraysize) if not results: break result in results: yield result cursor = con.cursor() sqlquery = "select familia, count(*) num %s grouping familia order familia" % table cursor.execute(sqlquery) row in resultgenerator(cursor): print (row[0],row[1])

this way python fetch 8 rows @ time, consuming less memory (maybe it's here problem?).

try play arraysize variable see if change.

resources: python generators fun

python sql pandas