[Chicago] List comprehension question

Fri May 27 15:55:23 EDT 2016

In my experience ORMs are good for the sort of simple filter and projection type of queries we have here.  And for those queries they can really help productivity as Carl points out.  As soon as you have the possibility of the SQL optimizer having multiple paths I have found the ORM just gets in the way of writing a good query.  Then the O in ORM stands for Obfuscation and you spend as much time figuring out how to present the query to the ORM as it takes to write the code in SQL.  And later maintainers can understand the SQL much more readily than the manipulations squirrelled away in the connector class can be understood.

On the other hand I have worked with people with years of experience in Django’s ORM or Psycopg and they could produce and maintain the ORM code quite efficiently.  The rest of the team,  with less experience, just had to take their word for it that this was the way to do it and not break anything.

Phil Robare

From: Chicago [mailto:chicago-bounces+proba=allstate.com at python.org] On Behalf Of Adam Forsyth
Sent: Friday, May 27, 2016 11:51 AM
To: The Chicago Python Users Group <chicago at python.org>
Subject: Re: [Chicago] List comprehension question

Sometimes an ORM is useful, but there are lots of cases where it can't do what you want, or encourages tight coupling of your logic and data model -- which is bad when your data model has to change.
See https://github.com/kennethreitz/records<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_kennethreitz_records&d=CwMFaQ&c=gtIjdLs6LnStUpy9cTOW9w&r=VXIryE9UwJGlNMLzgMzDT4_t2NMrZf6alSphHwSEwC0&m=kYQYlbXSxLYEpib9zWoZvJh7DrH53-TXKcBdkW7hVpg&s=2YmQA-ZG8h7pr49Ly8IHuKg7RE5MB_7UPygh2i1xi4Y&e=> for a nice wrapper around raw SQL.

On Fri, May 27, 2016 at 11:32 AM, Carl Karsten <carl at personnelware.com<mailto:carl at personnelware.com>> wrote:
Now that we have answered your question....
Stop doing all this and use an ORM.

manual way:
sql = "SELECT tail FROM aircraft_state WHERE airline='WN'"
c.execute(sql)
comm = [x[0] for x in c.fetchall()]
for c in comm:
    print( c )

ORMy way:

class Aircraft_state(models.Model):
    airline = models.CharField(max_length=2)
    tail  = models.CharField(max_length=20)
states = Aircraft_state.objects.filter( airline='WN'" )
for state in states:
    print( state.tail )

All of the SQL and stuff has been done in the models.Model class. This includes generating and executing the CREATE TABLE commands to build a new db, parametrizing the parameters so no SQL injection exploits or mistakes, lazy reads so it ie less likely to read a bunch of data that never gets used, etc.
And you write less code.  less code is easier to read and debug.

On Fri, May 27, 2016 at 10:46 AM, Dan Mahoney <catdude at gmail.com<mailto:catdude at gmail.com>> wrote:
Very cool, thanks to both of you. That makes sense - I guess I'm just suffering from temporary (I hope) brain fade.

On Fri, May 27, 2016 at 10:40 AM, Dale <dale at codefu.org<mailto:dale at codefu.org>> wrote:
I believe you want something like

comm = [row[0] for row in c.fetchall()]

Keep in mind that the list comprehension is going to evaluate that part before the "for" for each row it gets from the cursor, so you need the [0] inside there, not outside the list comprehension, which would be asking for the first element from the list comprehension—not what you want.

Note that c.fetchall() fetches all rows, which might be a waste of memory for large data sets.  Psycopg (PostgreSQL DB API library) lets you iterate over a cursor directly:

comm = [row[0] for row in c]

In theory this doesn't have to load all the rows into memory at once.  (In practice I bet it does unless you do something special with Psycopg/PostgreSQL.)  I'm not sure if you can just iterate over a cursor with your MySQL driver, but there's no harm in trying.

Dale

On Wed, May 25, 2016 at 10:38 AM, Dan Mahoney <catdude at gmail.com<mailto:catdude at gmail.com>> wrote:
I've got a question about using list comprehensions.
I've got a piece of code that is making a MySQL query:
sql = "SELECT tail FROM aircraft_state WHERE airline='WN'"
c.execute(sql)
I'm currently using a loop to get the values into a list:
rows = c.fetchall()
for row in rows:
    comm = row[0]

since the return value from MySQL is delivered as tuples. I'd like to use a list comprehension to build my final list (no important reason, I just want to learn how). I tried:
comm = [x for x in c.fetchall()][0]
but that just gives me the first tuple. If I use:
comm = [s for x in c.fetchall()]
I end up with a list of tuples. Any suggestions as to how I could do this without using a for loop?

--
--------------------------
Dan Mahoney
catdude at gmail.com<mailto:catdude at gmail.com>
Skype: catdude60440

"How you behave towards cats here below determines your status in Heaven."
Robert Heinlein

"There are two means of refuge from the miseries of
life - music and cats" - Albert Schweitzer

_______________________________________________
Chicago mailing list
Chicago at python.org<mailto:Chicago at python.org>
https://mail.python.org/mailman/listinfo/chicago<https://urldefense.proofpoint.com/v2/url?u=https-3A__mail.python.org_mailman_listinfo_chicago&d=CwMFaQ&c=gtIjdLs6LnStUpy9cTOW9w&r=VXIryE9UwJGlNMLzgMzDT4_t2NMrZf6alSphHwSEwC0&m=kYQYlbXSxLYEpib9zWoZvJh7DrH53-TXKcBdkW7hVpg&s=r2mGcW5SZddR3vIXezJBJYpsVc9GFEOnNXgZF7CNdtU&e=>

_______________________________________________
Chicago mailing list
Chicago at python.org<mailto:Chicago at python.org>
https://mail.python.org/mailman/listinfo/chicago<https://urldefense.proofpoint.com/v2/url?u=https-3A__mail.python.org_mailman_listinfo_chicago&d=CwMFaQ&c=gtIjdLs6LnStUpy9cTOW9w&r=VXIryE9UwJGlNMLzgMzDT4_t2NMrZf6alSphHwSEwC0&m=kYQYlbXSxLYEpib9zWoZvJh7DrH53-TXKcBdkW7hVpg&s=r2mGcW5SZddR3vIXezJBJYpsVc9GFEOnNXgZF7CNdtU&e=>

--
--------------------------
Dan Mahoney
catdude at gmail.com<mailto:catdude at gmail.com>
Skype: catdude60440

"How you behave towards cats here below determines your status in Heaven."
Robert Heinlein

"There are two means of refuge from the miseries of
life - music and cats" - Albert Schweitzer

_______________________________________________
Chicago mailing list
Chicago at python.org<mailto:Chicago at python.org>
https://mail.python.org/mailman/listinfo/chicago<https://urldefense.proofpoint.com/v2/url?u=https-3A__mail.python.org_mailman_listinfo_chicago&d=CwMFaQ&c=gtIjdLs6LnStUpy9cTOW9w&r=VXIryE9UwJGlNMLzgMzDT4_t2NMrZf6alSphHwSEwC0&m=kYQYlbXSxLYEpib9zWoZvJh7DrH53-TXKcBdkW7hVpg&s=r2mGcW5SZddR3vIXezJBJYpsVc9GFEOnNXgZF7CNdtU&e=>

--
Carl K

_______________________________________________
Chicago mailing list
Chicago at python.org<mailto:Chicago at python.org>
https://mail.python.org/mailman/listinfo/chicago<https://urldefense.proofpoint.com/v2/url?u=https-3A__mail.python.org_mailman_listinfo_chicago&d=CwMFaQ&c=gtIjdLs6LnStUpy9cTOW9w&r=VXIryE9UwJGlNMLzgMzDT4_t2NMrZf6alSphHwSEwC0&m=kYQYlbXSxLYEpib9zWoZvJh7DrH53-TXKcBdkW7hVpg&s=r2mGcW5SZddR3vIXezJBJYpsVc9GFEOnNXgZF7CNdtU&e=>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/chicago/attachments/20160527/722aa71c/attachment.html>