Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

SQLite: how to get a number of rows from SELECT query

by igoryonya (Pilgrim)
on Jul 20, 2021 at 03:44 UTC ( #11135188=perlquestion: print w/replies, xml ) Need Help??

igoryonya has asked for the wisdom of the Perl Monks concerning the following question:

I used to do something like this on MySQL:
$sth = $dbh->prepare("SELECT * FROM table"); if($sth->execute()){ printf "Executed Statement - returned (%s) rows\n", $sth->rows; }
and it returned a number of rows, found by SELECT, but, no matter, what SELECT variation I use with SQLite, even when it returns the actual row data with $sth->fetchrow_hashref(), it always returns 0 (zero).
Is there a way to determine the number of rows, found by select, if not with $sth->rows?

Replies are listed 'Best First'.
Re: SQLite: how to get a number of rows from SELECT query
by Corion (Patriarch) on Jul 20, 2021 at 06:00 UTC

    The DBI documentation states in ->execute for SELECT statements:

    The execute method does not return the number of rows that will be returned by the query

    So you will have to run a second query to fetch the number of returned rows, or fetch all rows to get their number.

      Hm, pity.
      So, it's just MySQL feature, I guess.

        As the documentation mentions (DBI#rows; look for rows under statement handle methods if that link doesn't work) it's dependent on the underlying database driver to begin with, not to mention what your query actually does. The DBD documentation may mention the behavior, too.

        Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

        The cake is a lie.
        The cake is a lie.
        The cake is a lie.

Re: SQLite: how to get a number of rows from SELECT query
by derby (Abbot) on Jul 20, 2021 at 11:18 UTC

    This is why my favorite DBI method is selectall_arrayref with the attribute Slice set

    my $rows= $dbh->selectall_arrayref( "SELECT * FROM table", { Slice = +> {} } );

    Sure it looks wonky and pretentious but this approach gives my an array reference of hash references where the hash key is the column name and the values are well, the data values. Now you can just use perl to get the size:

    printf "Executed Statement - returned (%d) rows\n", scalar( @$rows ) +;

    As a developer the concept of prepare, execute, and fetch is alluring and I can see its' value in certain situations but pragmatically, wrapping those steps into one is the common case with re-use of statement handle being the uncommon case (and for the future comments, I said uncommon - not rare). I am aware of the downsides to this approach such as the result set being too large and needing to be fetched in smaller batch sizes but I find a lot of the downsides to be outlier cases that are rarely hit (at least for my domain).

    -derby
Re: SQLite: how to get a number of rows from SELECT query
by Arunbear (Prior) on Jul 21, 2021 at 11:28 UTC
    SQLite doesn't seem to have an equivalent of MySQL's FOUND_ROWS feature (though even that is now deprecated in MySQL).

    If using DBIx::Class, is an option, it makes this kind of usage simple (at the cost of doing two queries) e.g.

    my $cd_rs = $schema->resultset('CD')->search({ year => 2005 }); printf "returned (%s) rows\n", $cd_rs->count; while($cd = $cd_rs->next) { print $cd->title; }
Re: SQLite: how to get a number of rows from SELECT query
by erix (Prior) on Jul 29, 2021 at 22:58 UTC

    If the SQLite is new enough, you could use a window function:

    select * , count(*) over () as rowcount from t ;

    (But of course, now you have an extra column which is not always what you want. YMMV, etc)

Re: SQLite: how to get a number of rows from SELECT query
by perlfan (Vicar) on Jul 29, 2021 at 22:38 UTC
    scalar + selectall_array (or selectall_arrayref might be your friends here). If you need a memory "lite" option and can afford an second SQL call, there's always the SQL COUNT(*) dealio.
    But if say you have all rows in $results_ref (an array ref); it'd be like:
    my $results_ref = $dbh->selectall_arrayref(....); if (@$results_ref) { # ... do stuff } # or just get $count, (using `scalar` for clarity but it's already in +scalar # context so works without it here) my $count = scalar @$results_ref;
Re: SQLite: how to get a number of rows from SELECT query
by Anonymous Monk on Jul 20, 2021 at 20:55 UTC
    If all that you need is the count, then something like this should work much more efficiently: select count(*) as count from ...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11135188]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (3)
As of 2022-05-16 05:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (62 votes). Check out past polls.

    Notices?