Iterating through the results (list of tuples) from fetchall in python/sqlite?

[scootermaster]

So, I've pretty much got this issue: http://stackoverflow.com/questions/...st-from-sqlite-in-python-not-a-list-of-tuples

i.e.

e.g cursor.fetchall() returns [(u'one',), (u'two',), (u'three',)]

from this, how do i get the simple list [u'one', u'two', u'three']?

Fetchall is returning a list of tuples, not just a list of the relevant returned column. Right now, I have some ridiculously hack-y and non-pythonic way of converting the list of tuples to just an array/list/whatever (sorry, I'm more used to C++ forgive the abuse of notation), but....

Now, these result sets are relatively tiny (anywhere from, say, 50 to at most a few hundred) so there's absolutely not real reason why I can't continue doing this, but it makes the code ugly, and at some point, I should probably learn how to do it "right".

Sooooo, if you want to iterate across the list of tuples (but obviously only accessing the first relevant element of each tuple), or more aptly, be able to send the list to other functions expecting just an array (i.e. "min" or the like, for example), what's the pythonic way of doing that? Things like min, max, etc. bark at me when they get the normal list of tuples, so how do I send them just the relevant tuple members?

Simple enough, right? Thanks!

 

[velis]

As already answered in the SO question (answer #2), use list / generator comprehension to extract only the relevant tuple members.

(x[0] for x in resultset)
[x[0] for x in resultset]  # requires more memory

 

[scootermaster]

As already answered in the SO question (answer #2), use list / generator comprehension to extract only the relevant tuple members.

(x[0] for x in resultset)
[x[0] for x in resultset]  # requires more memory

Huh. I guess I must have missed that. I'll check it out. Thanks!

But what about sending the result set to a function? I.e.

myList = fetchall(stuff)  # returns a list of tuples

myMin = min(myList)    # won't work, since myList is a list of tuples

Would something like min(myList[0]) work?

 

[velis]

No, seems you're just starting in programming, so I'll pretend this was a sensible question.

myMin = min([x[0] for x in myList])

 

[scootermaster]

No, seems you're just starting in programming, so I'll pretend this was a sensible question.

myMin = min([x[0] for x in myList])

Hah. If you knew my qualifications, you'd be...well, surprised. So, unnecessary snark aside, thank you for the input. I am new to Python and I am probably the least "pythonic" person you've ever seen.

Having said that, it's not entirely unreasonable to think that a built-in function like "min" takes, y'know, an array of values, so something like min(myArr) is not all that crazy. So obviously that list comprehension (or whatever the heck it is) obviously reduces to some sort of array/list, but it's a). conceptually the same thing, and b). obviously syntactically way more complex.

Now, it's possible you know everything there is to know about programming and computer science, but I'd probably wager that between the 20ish years of experience -- and, y'know, three degrees -- I've got doing this, I might have picked up something that you don't know. Let's hope that if you ever decide to ask about it -- and I'm not gonna hold my breath here -- I'm a little more gracious about it than you were.

 

[Ken g6]

Alright, let's settle down and drop the personal attacks.

myList appears to have elements [0][0], [1][0], etc. So that's why min didn't work.

Tomorrow I'll explain my theory of how a list comprehension is like a SQL query. If you behave.

 

[velis]

Sorry, no personal attack intended here. It just seemed impossible for someone with any programming background to not be able to use one expression as parameter in a function call. That's all I meant by "sense in the question" remark.

In addition min(myList[0]) would only return minimum of the record tuple, which only has one value anyway (as per OP example). This expression has this meaning for pretty much any language I know of. That's what led me to assume you were a beginner. Sorry about that too.

 

[Ken g6]

Alright, here's my SQL theory of list comprehensions, first posted at http://reprog.wordpress.com/2010/04...sp-part-2-is-lisp-just-too-hard/#comment-1432:

Definition: List comprehensions are functional programming tools (specifically map and grep, with lambdas) for SQL users.

You know what a SQL query looks like, right? For example:

SELECT table.element FROM table WHERE table.anotherelement == 42

As a list comprehension, that becomes:

[ row.element for row in table if row.anotherelement == 42 ]

Great! So how does this relate to functional programming? Well, both statements perform a “grep” (filter in Python), and they sorta perform a map too. Plus there are implied lambdas in both. Let me explain by breaking things up. I’ll start with grep:

Say “file” is a list of lines, and you want all lines that begin with “42”. This is the canonical problem for grep. In a Linux shell, you’d say:

grep “^42″ file

I’m not a SQL guru, but in SQL you’d say something like:

SELECT line FROM file WHERE line LIKE “42%”

In Python, you could say something like:

filter(lambda line: line.startswith(“42″), file)

(Ugly cuss, ain’t it?) Or you could say:

[ line for line in file if line.startswith(“42″) ]

I think that looks nicer, if a little redundant with “line”. But that redundancy disappears when you use list comprehensions the other way, as lambdas.

Now, suppose we want to remove “42” from the beginning of those lines. In SQL, you’d say:

SELECT MID(line,2) FROM file WHERE line LIKE “42%”

This is apparently known as a “scalar function”. Now, in Python, you could say:

map(lambda line: line[2:], filter(lambda line: line.startswith(“42″), file))

YUCK! Or you could say:

[ line[2:] for line in file if line.startswith(“42″) ]

Of course for some queries in SQL you don’t need the “WHERE” clause. And for some list comprehensions you don’t need the “if” clause. To chop the first two characters off every line as above, in SQL you’d say:

SELECT MID(line,2) FROM file

Or in Python I’d say:

[ line[2:] for line in file ]

Because I’ve had enough of lambdas, haven’t you?

 

[scootermaster]

Sorry, no personal attack intended here. It just seemed impossible for someone with any programming background to not be able to use one expression as parameter in a function call. That's all I meant by "sense in the question" remark.

In addition min(myList[0]) would only return minimum of the record tuple, which only has one value anyway (as per OP example). This expression has this meaning for pretty much any language I know of. That's what led me to assume you were a beginner. Sorry about that too.

It's fine. And myList[0] was stupid, I admit. I was just silly enough to think Python had some syntactic way of actually reducing the list of tuples [itself] in to something the built in functional could swallow. While the comprehension form obviously returns the same thing, there doesn't seem to be a way of actually representing just the first element of each tuple as a list, in line.

Which makes me wonder why people bark when you bother to do that explicitly

i.e.

x = 0
for element in myList:
       myRealList[x] = element[0] 
	x = x+1

I get all the reasons that's dumb to do in practice (it eats up processor cycles, it's ugly, it makes your dick smaller, etc.) but, like, if Python's just gonna do the same thing (in theory) behind the scenes, well, there you go. (Yes, I know the actual implementation is obviously nothing like that, but you get the idea)

Anyway, I get it now, and I obviously just haven't learned to think about this Pythonically. I'm sure once I do, it'll be the most amazing thing in the history of amazing things, and I'll wonder how I ever lived without it.

Alright, here's my SQL theory of list comprehensions, first posted at http://reprog.wordpress.com/2010/04...sp-part-2-is-lisp-just-too-hard/#comment-1432:

Thanks. I'll try and read all that a little more closely when I can.

 

[Ken g6]

Thanks. I'll try and read all that a little more closely when I can.

I should mention I'm no Python guru. Take with salt.