Thanks guys! it helps a lot. The "count" + "group" commands work fine only without the * though.
If you don't mind, I'd have one further question. I read the query results named "output" with Python. Why is it a list of tuples?? It's not very handy... >>print output [(12.2817, 12.2817), (0, 0), (8.52, 8.52)] It seems to be a list of tuples as far as I know Python. I would like to either convert "output" in a simple 1D array (=list in Python I guess): [12.2817, 12.2817, 0, 0, 8.52, 8.52] or a 2x3 matrix: 12.2817 12.2817 0 0 8.52 8.52 to be read via "output[i][j]" The flatten command does not do the job for the 1st option, and I have no idea for the second one... :) Could you please give me a hint? Some thing fast would be great as real data are much bigger (here is just a simple example). Thank you again. John Gillespie-2 wrote: > > You need something like : > > query = "select Item1,Item2,Item3, count(*) from %s where Item55 in (%s) > *group > by Item1,Item2,Item3*" > > JG > > On 12 May 2012 10:24, philherna <[email protected]> wrote: > >> >> Hi, >> >> >> I am using sqlite commands in my python script to output data from a >> sqlite >> database. My problem is however a Sqlite coding one. >> >> I can open, and select simple elements from the database easily but I >> have >> trouble for one specific issue. >> For example, to select the content of the rows Item1 , Item2 and Item3 >> for >> which Item55 is equal to 888, I type: >> >> query = "select Item1,Item2,Item3, count(*) from %s where Item55 in (%s)" >> % >> (Database,888) >> c.execute(query) >> results = c.fetchone() >> >> If I type in Python: >> >> print results # I get a the unique object which satisfies >> Item55=888. >> (0.2, 0.5, 0.9, 1) >> in which the 3 first elements are the values for Item1 , Item2 and Item3, >> and the last element tells me that I have done "oneobject matching the >> query", i.e. the selected columns for which Item55=888. >> However, i am not quite sure that I really well understand why I am >> returned >> this last element... >> >> And for instance, >> >> print results[1] # gives >> 0.5 # as expected >> >> Now, if I want to select to more objects contained in an array, I can do: >> Array = [888,999] >> query = "select Item1,Item2,Item3, count(*) from %s where Item55 in (%s)" >> % >> (Database,Array) >> c.execute(query) >> results = c.fetchall() >> >> The fetchall command is supposed to return all the rows, as far as I >> understand Sqlite... My problem is that i don't understand the query >> results >> in this case. Indeed, >> >> print results # gives >> (0.2, 0.5, 0.9, 2) >> in which the last element tells me (again, as far as I understand ...) >> that >> I have submitted "2 queries", i.e. selected rows for which Item55=888 and >> 999. I would have expected a result to be a matrix with 2 rows, not a 1D >> array... >> >> print results[1][0] # gives >> IndexError: list index out of range >> >> >> Does anyone has an idea of what I am doing wrong here? >> Thanks in advance ! >> -- >> View this message in context: >> http://old.nabble.com/don%27t-understand-what-%22query%22-returns...-tp33796190p33796190.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/don%27t-understand-what-%22query%22-returns...-tp33796190p33863074.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
