5 ms·

That's objectively not true though, so I'm not sure why you would say something like that. Ignorance? A hugely significant portion of database workloads involv

by orf 7y ago

That's objectively not true though, so I'm not sure why you would say something like that. Ignorance? A hugely significant portion of database workloads involve iterating over the rows returned from a query. Exposing a cursor/iterator/whatever in the spec is the only sensible way of handling this - your code works regardless of if the results are fetched in a single bulk operation or streamed to the client, or if you're returning 1 row or 1,000,000. It's the same reason you wouldn't do "for i in list(range(100_000))`, you'd just do `for i in range(100_00)` - iterating over a generator (which is what streaming results from the database really is) is far more efficient than creating a huge structure upfront and _then_ iterating over it. Also 100k rows isn't very large.

somacert 7y ago
That vaguely touches (warning going off topic here, my apologies) my main problem with python. dict.keys() returns an iterator, this is all fine and good, very efficient I suppose, however, the only reason I ever use dict.keys() is so I can sort the keys and you can't sort an iterator so every single dict.keys() in my code base is in the form list(dict.keys()). And it is not even like you need the iterator. A dict works perfectly fine as a key iterator on it's own. /rant
- orf 7y ago
  > sorted(my_dict.keys()) Or just: > sorted(my_dict) Sorting keys like that is also weird. Keys are insertion ordered, utilize that property where you can and avoid needlessly re-sorting.
  - somacert 7y ago
    Glorious, Thank you kind soul for listening to my slightly raving rant. Now I feel several sorts of ashamed at missing what is kind of a core function. salutes
    - hansvm 7y ago
      Fwiw, make sure you're using a newish python version. They weren't always sorted by default.
      - ledauphin 7y ago
        3.6+
- marcosdumay 7y ago
  > he only reason I ever use dict.keys() is so I can sort the keys Hum... I use dict.keys() every time I use dictionaries to describe some unspecified data set, what happens way more frequently than any use case that requires sorting the keys. It's even incentivized by the language with that kwargs construct and a mainstream usage within libraries. I do agree that the result of dict.keys() should have a sort method. There is no reason not to, but from there it doesn't follow that that making it an iterator is a minor gain. That's the same situation as the GP asking for the removal of a main feature of the library just because he doesn't work with a kind of software that uses it. Congratulations, remove cursors from the library and suddenly Python is a lot less useful for data science.
  - orf 7y ago
    It doesn’t and shouldn’t have a sort method because python prefers functions that act on objects rather than methods attached to objects. That’s why you use “len(obj)” rather than “obj.len()”, and why you use “sorted(dict_keys)” rather than adding a “sort” method to random iterables.
    - marcosdumay 7y ago
      Oh, ok. I was comparing it with 'list.sort()', but yeah, this one mutates the list and returns None (just checked it), so 'sorted' is the expected way to sort a dictionary keys, and my post is just wrong.
      - orf 7y ago
        'list.sort()' is one of those leftovers from "Python before it was really Python" and is not one that's particularly worth it to change because it's useful sometimes. In general Python prefers builtins that operate on protocols. You use "len(x)" because it's consistent, otherwise you might call "x.len()", "x.length", "x.size()" or any other number of possibilities. And if an object doesn't define it? You're out of luck. With this any object that supports the protocol (__len__) can be passed to length. The same applies to "sorted()". list.sort() is useful in some specific circumstances where "sorted()" is not adequate, but in general the answer to "how do I sort something" is to use sorted.
aeyes 7y ago
One could argue that pumping huge amounts of data with psycopg2 is slow in any case due to the blocking nature of the network calls. I recently copied some big tables (100M+ rows) into a different system, when I switched the simple script to asyncpg I gained 5x over psycopg2. Experimenting with the amount of rows cursor.fetchmany returns changed absolutely nothing. I wonder how much gain there is from using a cursor, for CRUD applications you most likely need the full result set before being able to do anything with it. Having a cursor could be made optional for when you actually want to iterate over the resultset. The cursor interface could also be hidden, I just want to iterate over the resultset - I don't care what happens in the background.
- ghostwriter 7y ago
  > when I switched the simple script to asyncpg I gained 5x over psycopg2. This should really be a comparison between asyncpg and psycopg2+gevent+psycogreen to be technically aligned, as otherwise it's a comparison between two scripts with non-blocking and blocking IO.
  - 1st1 7y ago
    Here's a benchmark [1] against aiopg, which is psycopg2+asyncio. [1] http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/report.html http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...
    - ddorian43 7y ago
      You can't scale that. Adding clients will have a lot of overhead (ex: 100K clients to 1 db server). Only way is autocommit with proxy (pgbouncer etc).
    - ghostwriter 7y ago
      Unless aiopg patches psycopg2 around its C extension with available callback hooks (see [1] and [2] - psycogreen uses it to register gevent callbacks), this comparison is still between blocking and non-blocking IO scripts. Quick GitHub search for `set_wait_callback` didn't return any result in the aiopg repository - https://github.com/aio-libs/aiopg/search?q=set_wait_callback&unscoped_q=set_wait_callback https://github.com/aio-libs/aiopg/search?q=set_wait_callback... [1] https://www.psycopg.org/docs/extensions.html#coroutines-support-functions https://www.psycopg.org/docs/extensions.html#coroutines-supp... [2] https://www.psycopg.org/docs/advanced.html#green-support https://www.psycopg.org/docs/advanced.html#green-support

by orf 7y ago

somacert 7y ago

That vaguely touches (warning going off topic here, my apologies) my main problem with python. dict.keys() returns an iterator, this is all fine and good, very efficient I suppose, however, the only reason I ever use dict.keys() is so I can sort the keys and you can't sort an iterator so every single dict.keys() in my code base is in the form list(dict.keys()). And it is not even like you need the iterator. A dict works perfectly fine as a key iterator on it's own. /rant

orf 7y ago
> sorted(my_dict.keys()) Or just: > sorted(my_dict) Sorting keys like that is also weird. Keys are insertion ordered, utilize that property where you can and avoid needlessly re-sorting.
- somacert 7y ago
  Glorious, Thank you kind soul for listening to my slightly raving rant. Now I feel several sorts of ashamed at missing what is kind of a core function. salutes
  - hansvm 7y ago
    Fwiw, make sure you're using a newish python version. They weren't always sorted by default.
    - ledauphin 7y ago
      3.6+
marcosdumay 7y ago
> he only reason I ever use dict.keys() is so I can sort the keys Hum... I use dict.keys() every time I use dictionaries to describe some unspecified data set, what happens way more frequently than any use case that requires sorting the keys. It's even incentivized by the language with that kwargs construct and a mainstream usage within libraries. I do agree that the result of dict.keys() should have a sort method. There is no reason not to, but from there it doesn't follow that that making it an iterator is a minor gain. That's the same situation as the GP asking for the removal of a main feature of the library just because he doesn't work with a kind of software that uses it. Congratulations, remove cursors from the library and suddenly Python is a lot less useful for data science.
- orf 7y ago
  It doesn’t and shouldn’t have a sort method because python prefers functions that act on objects rather than methods attached to objects. That’s why you use “len(obj)” rather than “obj.len()”, and why you use “sorted(dict_keys)” rather than adding a “sort” method to random iterables.
  - marcosdumay 7y ago
    Oh, ok. I was comparing it with 'list.sort()', but yeah, this one mutates the list and returns None (just checked it), so 'sorted' is the expected way to sort a dictionary keys, and my post is just wrong.
    - orf 7y ago
      'list.sort()' is one of those leftovers from "Python before it was really Python" and is not one that's particularly worth it to change because it's useful sometimes. In general Python prefers builtins that operate on protocols. You use "len(x)" because it's consistent, otherwise you might call "x.len()", "x.length", "x.size()" or any other number of possibilities. And if an object doesn't define it? You're out of luck. With this any object that supports the protocol (__len__) can be passed to length. The same applies to "sorted()". list.sort() is useful in some specific circumstances where "sorted()" is not adequate, but in general the answer to "how do I sort something" is to use sorted.

aeyes 7y ago

One could argue that pumping huge amounts of data with psycopg2 is slow in any case due to the blocking nature of the network calls. I recently copied some big tables (100M+ rows) into a different system, when I switched the simple script to asyncpg I gained 5x over psycopg2. Experimenting with the amount of rows cursor.fetchmany returns changed absolutely nothing. I wonder how much gain there is from using a cursor, for CRUD applications you most likely need the full result set before being able to do anything with it. Having a cursor could be made optional for when you actually want to iterate over the resultset. The cursor interface could also be hidden, I just want to iterate over the resultset - I don't care what happens in the background.

ghostwriter 7y ago
> when I switched the simple script to asyncpg I gained 5x over psycopg2. This should really be a comparison between asyncpg and psycopg2+gevent+psycogreen to be technically aligned, as otherwise it's a comparison between two scripts with non-blocking and blocking IO.
- 1st1 7y ago
  Here's a benchmark [1] against aiopg, which is psycopg2+asyncio. [1] http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/report.html http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...
  - ddorian43 7y ago
    You can't scale that. Adding clients will have a lot of overhead (ex: 100K clients to 1 db server). Only way is autocommit with proxy (pgbouncer etc).
  - ghostwriter 7y ago
    Unless aiopg patches psycopg2 around its C extension with available callback hooks (see [1] and [2] - psycogreen uses it to register gevent callbacks), this comparison is still between blocking and non-blocking IO scripts. Quick GitHub search for `set_wait_callback` didn't return any result in the aiopg repository - https://github.com/aio-libs/aiopg/search?q=set_wait_callback&unscoped_q=set_wait_callback https://github.com/aio-libs/aiopg/search?q=set_wait_callback... [1] https://www.psycopg.org/docs/extensions.html#coroutines-support-functions https://www.psycopg.org/docs/extensions.html#coroutines-supp... [2] https://www.psycopg.org/docs/advanced.html#green-support https://www.psycopg.org/docs/advanced.html#green-support