6 ms·

The one thing I dislike about the Python DBAPi spec is the "cursor". Maybe the idea was good when the creators of the spec came up with it, but no other databas

The one thing I dislike about the Python DBAPi spec is the "cursor". Maybe the idea was good when the creators of the spec came up with it, but no other database API exposes a cursor so upfront to the users. When someone new to databases first learns about the Python DBAPI then he will probably think that cursors are something really important, but really - they aren't. Developers just want to fire queries at the database, the cursor is just something between the db connection and the query result. Maybe it is needed to keep the state of query objects, but it should have some other name.

quietbritishjim 7y ago
I don't understand this comment. A cursor is just an iterator over the results. Would you prefer "iterator" as a name? A concept like it exists in basically every database API (for row-oriented databases) so that you can start processing results as soon as they start coming in rather than having to load all results into application memory first.
- paulddraper 7y ago
  Though except with very large result sets (100k), there is little performance gain from having a cursor.
  - yingw787 7y ago
    Wait...are you saying 100,000 records is a lot? If so, I disagree. Any serious enterprise workload is millions, usually tens, hundreds of millions, and oftentimes billions or tens of billions or more over the lifetime of any given production UNIX process. Hundreds of thousands is dev testing range. I've dealt up to single-digit billions, and I can say having a cursor is absolutely a requirement when working with production data.
    - vosper 7y ago
      I don't really understand your point. You and the person you replied to are both agreeing that cursors are useful for large datasets. Whatever defines "large" isn't that interesting and is dependent on context: got a resource-constrained DB? Or very wide tables? Constraints on the client side for how much data it can consume and process? Then, maybe your "large" is a lot different to mine.
      - AlphaSite 7y ago
        They are arguing it’s useful for smaller datasets as well.
      - ben509 7y ago
        > Whatever defines "large" isn't that interesting 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.
    - stefan_ 7y ago
      For the database. If your application is retrieving millions of rows, you might want to reconsider what using a database with a complex query protocol is giving you and whether you are using it correctly.
      - marcosdumay 7y ago
        If my application is retrieving millions of rows, it may be because I need to pass a window function over millions of rows. Or it may be that I need to copy millions of rows from one place to another. Or I may need to compare them with other millions of rows from another source somewhere else. Or I may need to transform those millions of rows on my application because I can scale it better than the database... Just because you didn't think of some valid usage for a feature, it doesn't mean that there isn't any.
        tomnipotent 7y ago
        > it may be because I need to pass a window function over millions of rows 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?
        ben509 7y ago
        Probably related to why the database has millions and millions of rows in the first place. Maybe someone read about denormalization in "Super Webscale SQL!" and blew up a trivial database. Or they're hoovering up vast quantities of garbage data and running it through a BI tract to produce bullshit. 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.
        marcosdumay 7y ago
        Oh, God forgive if a company actually has a few million sales it must account for, or if a government body actually wants to control and verify what it does. No it's all because of stupid webscale! developers, because nobody ever does anything that isn't a web or finance startup! Ever!
    - paulddraper 7y ago
      Yes, I'm claiming that retrieving 100,000 records in a single query is a lot. 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.
  - 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.
- djrobstep 7y ago
  If you're exporting data en mass from the database then this is a concern, but for the typical CRUD application use case, it's never an issue. 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.
- luhn 7y ago
  I think OP has a point. He's not complaining about the functionality (of course a DB client needs to iterate through results) but that there's no reason a user needs to understand cursors for running simple DB queries. I should be able to do: 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" :)
  - quietbritishjim 7y ago
    You are right and I had forgotten about that. I mostly use SQLite from Python which actually does allow .execute() on the connection as a non-standard extension to DBAPI, so it supports exactly the two snippets you posted. It is a bit awkward that you have to create your cursor before you execute the query, I don't quite see the reason for that.
  - skeletal88 7y ago
    Yes, that's what I meant, it doesn't have to be exposed to the user, I just want to execute a query and get the results. If needed then maybe I should be able to create a cursor to go through the results. It's just that other database access APIs don't expose and force the developers using the API to use a cursor, so it's mildly annoying in Python.
- adamcharnock 7y ago
  This seems non obvious to me, purely because one has to create the cursor before ever executing any queries [1]. It therefore think it feels like a "sub-connection", or something the database is aware of at any rate. 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. [1] https://www.psycopg.org/docs/usage.html https://www.psycopg.org/docs/usage.html
  - quietbritishjim 7y ago
    > one has to create the cursor before ever executing any queries 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.
- skeletal88 7y ago
  Yes, but with other database access APIs I can just get a connection and execute a statement on it. Other APIs don't have an additional abstraction between the connection and the execute() method. It's especially useless when executing anything besides SELECT. An UPDATE or INSERT shouldn't need a cursor.

quietbritishjim 7y ago

I don't understand this comment. A cursor is just an iterator over the results. Would you prefer "iterator" as a name? A concept like it exists in basically every database API (for row-oriented databases) so that you can start processing results as soon as they start coming in rather than having to load all results into application memory first.

paulddraper 7y ago
Though except with very large result sets (100k), there is little performance gain from having a cursor.
- yingw787 7y ago
  Wait...are you saying 100,000 records is a lot? If so, I disagree. Any serious enterprise workload is millions, usually tens, hundreds of millions, and oftentimes billions or tens of billions or more over the lifetime of any given production UNIX process. Hundreds of thousands is dev testing range. I've dealt up to single-digit billions, and I can say having a cursor is absolutely a requirement when working with production data.
  - vosper 7y ago
    I don't really understand your point. You and the person you replied to are both agreeing that cursors are useful for large datasets. Whatever defines "large" isn't that interesting and is dependent on context: got a resource-constrained DB? Or very wide tables? Constraints on the client side for how much data it can consume and process? Then, maybe your "large" is a lot different to mine.
    - AlphaSite 7y ago
      They are arguing it’s useful for smaller datasets as well.
    - ben509 7y ago
      > Whatever defines "large" isn't that interesting 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.
  - stefan_ 7y ago
    For the database. If your application is retrieving millions of rows, you might want to reconsider what using a database with a complex query protocol is giving you and whether you are using it correctly.
    - marcosdumay 7y ago
      If my application is retrieving millions of rows, it may be because I need to pass a window function over millions of rows. Or it may be that I need to copy millions of rows from one place to another. Or I may need to compare them with other millions of rows from another source somewhere else. Or I may need to transform those millions of rows on my application because I can scale it better than the database... Just because you didn't think of some valid usage for a feature, it doesn't mean that there isn't any.
      - tomnipotent 7y ago
        > it may be because I need to pass a window function over millions of rows 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?
        ben509 7y ago
        Probably related to why the database has millions and millions of rows in the first place. Maybe someone read about denormalization in "Super Webscale SQL!" and blew up a trivial database. Or they're hoovering up vast quantities of garbage data and running it through a BI tract to produce bullshit. 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.
        marcosdumay 7y ago
        Oh, God forgive if a company actually has a few million sales it must account for, or if a government body actually wants to control and verify what it does. No it's all because of stupid webscale! developers, because nobody ever does anything that isn't a web or finance startup! Ever!
  - paulddraper 7y ago
    Yes, I'm claiming that retrieving 100,000 records in a single query is a lot. 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.
- 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.
djrobstep 7y ago
If you're exporting data en mass from the database then this is a concern, but for the typical CRUD application use case, it's never an issue. 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.
luhn 7y ago
I think OP has a point. He's not complaining about the functionality (of course a DB client needs to iterate through results) but that there's no reason a user needs to understand cursors for running simple DB queries. I should be able to do: 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" :)
- quietbritishjim 7y ago
  You are right and I had forgotten about that. I mostly use SQLite from Python which actually does allow .execute() on the connection as a non-standard extension to DBAPI, so it supports exactly the two snippets you posted. It is a bit awkward that you have to create your cursor before you execute the query, I don't quite see the reason for that.
- skeletal88 7y ago
  Yes, that's what I meant, it doesn't have to be exposed to the user, I just want to execute a query and get the results. If needed then maybe I should be able to create a cursor to go through the results. It's just that other database access APIs don't expose and force the developers using the API to use a cursor, so it's mildly annoying in Python.
adamcharnock 7y ago
This seems non obvious to me, purely because one has to create the cursor before ever executing any queries [1]. It therefore think it feels like a "sub-connection", or something the database is aware of at any rate. 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. [1] https://www.psycopg.org/docs/usage.html https://www.psycopg.org/docs/usage.html
- quietbritishjim 7y ago
  > one has to create the cursor before ever executing any queries 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.
skeletal88 7y ago
Yes, but with other database access APIs I can just get a connection and execute a statement on it. Other APIs don't have an additional abstraction between the connection and the execute() method. It's especially useless when executing anything besides SELECT. An UPDATE or INSERT shouldn't need a cursor.