Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: Posgres batch read with DBI?

by choroba (Archbishop)
on Jan 26, 2021 at 22:48 UTC ( #11127498=note: print w/replies, xml ) Need Help??


in reply to Posgres batch read with DBI?

See the LIMIT and OFFSET clauses in the documentation.
map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

Replies are listed 'Best First'.
Re^2: Posgres batch read with DBI?
by cormanaz (Chaplain) on Jan 26, 2021 at 23:19 UTC
    Many thanks for the pointer. Running this (SqlSupport is a homebrew helper module):
    use lib qw ( c:/perlmodules ); use SqlSupport; my $dbh = connectpgdb(*,*,*,*,*); my $query = "select id,post from blog where language = 'en'"; my $sth = $dbh->prepare($query); $sth->execute || die "Could not execute MySQL statement: $sqlstatement +"; my $rows = []; # cache for batches of rows while( my $row = ( shift(@$rows) || shift(@{$rows=$sth->fetchall_array +ref(undef,10_000) || []}))) { my $foo = 1; } $sth->finish();
    It's saying the last bracket is unmatched. But my IDE shows every bracket has a mate, and I clipped the whole while loop from the DBI docs.
      This works for me:
      #!/usr/bin/perl use warnings; use strict; use feature qw{ say }; use DBI; my ($dbname, $user, $password) = @ARGV; my $db = 'DBI'->connect('dbi:Pg:dbname=' . $dbname, $user, $password, {AutoCommit => 0}); $db->do('CREATE TABLE t (id INT, name TEXT)'); my $populate = $db->prepare('INSERT INTO t (id, name) VALUES (?,?)'); my $max = 250_000; for my $i (0 .. $max) { $populate->execute($i, join "", map chr, map 64 + int rand 26, 1 . +. 10); print "$i\r"; } my $from = 0; my $fetch = $db->prepare('SELECT * FROM t LIMIT ? OFFSET ?'); while ($from <= $max) { $fetch->execute(1000, $from); while (my @row = $fetch->fetchrow_array) { say join "\t", @row; } say '---'; $from += 1000; } $db->disconnect;
      map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
        Works for me too. Thanks for the code. I still don't get the unmatched bracket error...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11127498]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2021-04-13 05:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?