Get the `.rowcount` of SQLAlchemy ORM query
How do I get both the rowcount and the instances of an SQLAlchemy ORM Query in a single roundtrip to the database?
If I call query.all(), I get a standard list, which I can get the len() of, but that requires loading the whole resultset in memory at once.
If I call iter(query), I get back a standard python generator, with no access to the .rowcount of the underlying ResultProxy.
If I call query.count() then iter(query) I'm doing two roundtrips of a potentially expensive query to the database.
if I managed to get hold of a ResultProxy for a Query, that would give me the .rowcount, then I could use Query.instances() to get the same generator that Query.__iter__() would give me.
But is there a convenient way of getting at the ResultProxy of a Query other than repeating what Query.__iter__() and Query._execute_and_instances() do? Seems rather inconvenient.
Notice
As mentioned by This answer (thanks @terminus), getting the ResultProxy.rowcount might or might not be useful, and is explicitly warned against in SQLAlchemy documentation for pure "select" statements.
That said, in the case of psycopg2, the .rowcount of the underlying cursor is documented to return the correct number of records returned by any query, even "SELECT" queries, unless you're using the stream_results=True option (thanks @SuperShoot).
python orm sqlalchemy psycopg2
asked Nov 12 at 14:13
LeoRochael
5,52731729
add a comment |
How do I get both the rowcount and the instances of an SQLAlchemy ORM Query in a single roundtrip to the database?
If I call query.all(), I get a standard list, which I can get the len() of, but that requires loading the whole resultset in memory at once.
If I call iter(query), I get back a standard python generator, with no access to the .rowcount of the underlying ResultProxy.
If I call query.count() then iter(query) I'm doing two roundtrips of a potentially expensive query to the database.
if I managed to get hold of a ResultProxy for a Query, that would give me the .rowcount, then I could use Query.instances() to get the same generator that Query.__iter__() would give me.
But is there a convenient way of getting at the ResultProxy of a Query other than repeating what Query.__iter__() and Query._execute_and_instances() do? Seems rather inconvenient.
Notice
As mentioned by This answer (thanks @terminus), getting the ResultProxy.rowcount might or might not be useful, and is explicitly warned against in SQLAlchemy documentation for pure "select" statements.
That said, in the case of psycopg2, the .rowcount of the underlying cursor is documented to return the correct number of records returned by any query, even "SELECT" queries, unless you're using the stream_results=True option (thanks @SuperShoot).
python orm sqlalchemy psycopg2
asked Nov 12 at 14:13
LeoRochael
5,52731729
3
Maybe useful info.
– Terminus
Nov 12 at 14:43
Found this blog which I think does what you are after: blog.rectalogic.com/2017/04/…
– SuperShoot
Nov 13 at 6:00
1
@SuperShoot, thanks, but that blog post is only showing how to use the standard SQLAlchemy query mechanism, not the ORM query mechanism, while using streaming results AND getting the right rowcount. It's really interesting, but not what I'm after.
– LeoRochael
Nov 13 at 12:31
add a comment |
How do I get both the rowcount and the instances of an SQLAlchemy ORM Query in a single roundtrip to the database?
If I call query.all(), I get a standard list, which I can get the len() of, but that requires loading the whole resultset in memory at once.
If I call iter(query), I get back a standard python generator, with no access to the .rowcount of the underlying ResultProxy.
If I call query.count() then iter(query) I'm doing two roundtrips of a potentially expensive query to the database.
if I managed to get hold of a ResultProxy for a Query, that would give me the .rowcount, then I could use Query.instances() to get the same generator that Query.__iter__() would give me.
But is there a convenient way of getting at the ResultProxy of a Query other than repeating what Query.__iter__() and Query._execute_and_instances() do? Seems rather inconvenient.
Notice
As mentioned by This answer (thanks @terminus), getting the ResultProxy.rowcount might or might not be useful, and is explicitly warned against in SQLAlchemy documentation for pure "select" statements.
That said, in the case of psycopg2, the .rowcount of the underlying cursor is documented to return the correct number of records returned by any query, even "SELECT" queries, unless you're using the stream_results=True option (thanks @SuperShoot).
python orm sqlalchemy psycopg2
asked Nov 12 at 14:13
LeoRochael
5,52731729
How do I get both the rowcount and the instances of an SQLAlchemy ORM Query in a single roundtrip to the database?
If I call query.all(), I get a standard list, which I can get the len() of, but that requires loading the whole resultset in memory at once.
If I call iter(query), I get back a standard python generator, with no access to the .rowcount of the underlying ResultProxy.
If I call query.count() then iter(query) I'm doing two roundtrips of a potentially expensive query to the database.
if I managed to get hold of a ResultProxy for a Query, that would give me the .rowcount, then I could use Query.instances() to get the same generator that Query.__iter__() would give me.
But is there a convenient way of getting at the ResultProxy of a Query other than repeating what Query.__iter__() and Query._execute_and_instances() do? Seems rather inconvenient.
Notice
As mentioned by This answer (thanks @terminus), getting the ResultProxy.rowcount might or might not be useful, and is explicitly warned against in SQLAlchemy documentation for pure "select" statements.
That said, in the case of psycopg2, the .rowcount of the underlying cursor is documented to return the correct number of records returned by any query, even "SELECT" queries, unless you're using the stream_results=True option (thanks @SuperShoot).
python orm sqlalchemy psycopg2
python orm sqlalchemy psycopg2
asked Nov 12 at 14:13
LeoRochael
5,52731729
asked Nov 12 at 14:13
LeoRochael
5,52731729
edited Nov 13 at 12:42
asked Nov 12 at 14:13
LeoRochael
5,52731729
asked Nov 12 at 14:13
LeoRochael
5,52731729
asked Nov 12 at 14:13
LeoRochael
5,52731729
5,52731729
3
Maybe useful info.
– Terminus
Nov 12 at 14:43
Found this blog which I think does what you are after: blog.rectalogic.com/2017/04/…
– SuperShoot
Nov 13 at 6:00
1
@SuperShoot, thanks, but that blog post is only showing how to use the standard SQLAlchemy query mechanism, not the ORM query mechanism, while using streaming results AND getting the right rowcount. It's really interesting, but not what I'm after.
– LeoRochael
Nov 13 at 12:31
add a comment |
3
Maybe useful info.
– Terminus
Nov 12 at 14:43
Found this blog which I think does what you are after: blog.rectalogic.com/2017/04/…
– SuperShoot
Nov 13 at 6:00
1
@SuperShoot, thanks, but that blog post is only showing how to use the standard SQLAlchemy query mechanism, not the ORM query mechanism, while using streaming results AND getting the right rowcount. It's really interesting, but not what I'm after.
– LeoRochael
Nov 13 at 12:31
3
3
Maybe useful info.
– Terminus
Nov 12 at 14:43
Maybe useful info.
– Terminus
Nov 12 at 14:43
Found this blog which I think does what you are after: blog.rectalogic.com/2017/04/…
– SuperShoot
Nov 13 at 6:00
Found this blog which I think does what you are after: blog.rectalogic.com/2017/04/…
– SuperShoot
Nov 13 at 6:00
1
1
@SuperShoot, thanks, but that blog post is only showing how to use the standard SQLAlchemy query mechanism, not the ORM query mechanism, while using streaming results AND getting the right rowcount. It's really interesting, but not what I'm after.
– LeoRochael
Nov 13 at 12:31
@SuperShoot, thanks, but that blog post is only showing how to use the standard SQLAlchemy query mechanism, not the ORM query mechanism, while using streaming results AND getting the right rowcount. It's really interesting, but not what I'm after.
– LeoRochael
Nov 13 at 12:31
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263984%2fget-the-rowcount-of-sqlalchemy-orm-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263984%2fget-the-rowcount-of-sqlalchemy-orm-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Maybe useful info.
– Terminus
Nov 12 at 14:43
Found this blog which I think does what you are after: blog.rectalogic.com/2017/04/…
– SuperShoot
Nov 13 at 6:00
1
@SuperShoot, thanks, but that blog post is only showing how to use the standard SQLAlchemy query mechanism, not the ORM query mechanism, while using streaming results AND getting the right rowcount. It's really interesting, but not what I'm after.
– LeoRochael
Nov 13 at 12:31