[Chicago] List comprehension question

Fri May 27 18:35:43 EDT 2016

To add to the soup....

ORMs are the future, don't fight it.

First my resume:
hopefully you find this interesting.

I consider myself fluent in 6502 and 6809 assembly, when I looked at 8088,
80286 and 68000 I was not put off.  I have written C professionally and had
my patches to an open source C++ project accepted. From what I hear, it is
hard to write assembly code that is better than compiled C because the
compilers can do optimizations that are hard for a human, maybe because
brute force?

I am also fluent in SQL.  I have studied the SQL97 spec to try and figure
out what should happen when you ORDER BY with null's. (spoiler alert: it is
left up to the implementation.  I think this means they couldn't come to a
consensus and gave up.)  I have written frameworky code to construct SQL
commands, mostly WHERE clauses. I have studded the lex/yack syntax
definition files in hopes to parse SQL commands back into an object
representation so I could augment them.  (don't try to write your own
parser, you can only get 90% there, and it is the 10% that is hard and what
I needed to fiddle with anyway.)

I can also rant about Python's db-api spec and how parameter syntax is
another implementation detail which means everyone has to write a layer of
drivers to handle all the implementations of ? and {} and % and whatever
syntax the authors dream up. Gee thanks.

/resume

However, I kinda cringe at the idea of ever using those skills again.   I
much prefer Python over C, even if there are parts that would run faster if
I wrote them in C.  I suspect my Python version will be more maintainable
than anyone's C version.  This is because Python is a very well designed
implemented language.  Same with SQL.  Only once in the last 3 years have I
coded SQL:

(veyepar) carl at twist:~/src/veyepar$ ack-grep "\.raw\("
dj/main/views.py
1678:    raw_files=Raw_File.objects.raw(
1729:    episodes=Episode.objects.raw('select distinct e1.* from
main_episode e1, main_episode e2 where e1.id != e2.id and e1.start<e2.end
and e1.end>e2.start and e1.location_id=e2.location_id and e1.show_id=%s and
e2.show_id=%s order by e1.location_id, e1.start', [show.id,show.id])

(That first one is pretty much the same as the 2nd one)

I did that because I couldn't figure out the ORM code to do it.
(If someone can give me the ORM code to do that, I'll be happy to give it a
whirl.)

This is in an app with a bunch of views and stuff:
(veyepar) carl at twist:~/src/veyepar$ grep "^def " dj/main/views.py |wc
     49     139    1901

I think ORMs are currently pretty good but will get better over the next
decade.  Maybe something new will take their place, but it will be some
abstraction or whatever that is easier to work with than SQL.  ORMs kinda
struggle to work well with NoSQL db. They work, but I think they restrict
things to the point of losing most of the benefits of NoSQL.  (I guess.  I
have only made videos of such things, never got my hands dirty.)

So...

There are some good augments against using an ORM, but I think they are the
way to go right from the start.  Like me, you may need something they can't
do and you are forced to code SQL, but make that the exception not the norm.

On Fri, May 27, 2016 at 2:55 PM, Robare, Phillip (TEKSystems) <
proba at allstate.com> wrote:

> 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>
> 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> 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> 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> 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
>
> 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
> 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
> 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
>
> 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
> 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
> 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
> https://mail.python.org/mailman/listinfo/chicago
>
>

-- 
Carl K
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/chicago/attachments/20160527/c39b00b1/attachment-0001.html>