results = conn.execute("select * from mytable").fetchall()
or query = conn.execute("select * from mytable")
for row in query:
print(row.id)
You can do the same with cursors, but it's extra mental overhead and a somewhat awkward API.Plus the name conflicts with Postgres' CURSOR, which confused me when I first started. So, yes, I would prefer "iterator" :)
It would be much clearer to me that the cursor is an iterator if it was actually returned as the result of executing a query.
You are right, that is a bit weird, although only a very slight annoyance IMO. As I said in reply to the sibling comment, I had forgotten this as I mostly use SQLite from Python and it doesn't actually require this: you can call .execute() on the connection object and it returns the cursor, which does seem a lot cleaner.
It's especially useless when executing anything besides SELECT. An UPDATE or INSERT shouldn't need a cursor.
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.
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.
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.
[1] http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...
[1] https://www.psycopg.org/docs/extensions.html#coroutines-supp...
[2] https://www.psycopg.org/docs/advanced.html#green-support
> psycopg2 is slow in any case due to the blocking nature of the network calls.
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.
Now I feel several sorts of ashamed at missing what is kind of a core function.
salutes
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.
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.
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.
If thousands of records weren't a lot, HN wouldn't have to manually break up comment threads anytime a U.S. president is elected.
Just because you didn't think of some valid usage for a feature, it doesn't mean that there isn't any.
Every major RDBMS supports window functions (even MySQL, though recent). Why would you bring remote data into a local process to do something the remote process can do with its own local data vastly more efficiently?
I worked at a company that bilked the government for vast sums of money by doing all this and charging them for the hardware. The guvvies knew what was going on, but their little empires were more important if they got a bigger budget, so they went along with it.
Some people get excited about huge datasets; I don't get it myself, but "big data" is a successful buzzword because it pushes people's buttons.
If there's enough results that loading them is causing performace problems, there's enough results that you need to be paging them in your UI.