This project is archived and is in readonly mode.

#180 ✓resolved
Federico Di Gregorio

Return number of rows for copy operations

Reported by Federico Di Gregorio | September 20th, 2013 @ 02:05 PM | in psycopg2 2.5.3 (closed)

As asked by Ryan Kelly on the ML:

I'm wondering if it's possible to get the number of rows copied when
using copy_from? I would've thought the .rowcount attribute of the cursor
would have this value, but that doesn't seem to be the case.

Comments and changes to this ticket

  • Federico Di Gregorio

    Federico Di Gregorio September 20th, 2013 @ 02:05 PM

    If I remember correctly the libpq docs don't say if it is possible to
    retrieve the number of inserted columns and we're not sending data
    line-by-line but as chunks so counting the lines isn't possible. Maybe
    (a big maybe) the final PGresult contains that but I can't find this documented anywhere. We'll need to instrument psycopg to print PGresult
    fields and do a bit of guesswork.

  • Rui Da Costa

    Rui Da Costa December 9th, 2013 @ 01:49 PM

    I'm no expert, but isn't it described here?:

    This function returns a string containing the number of rows affected by the SQL statement that generated the PGresult. This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement, ...

  • Nicholas Chammas

    Nicholas Chammas May 5th, 2014 @ 09:59 PM

    On a related note, I have been using psycopg2 with Amazon Redshift and recently asked a similar question about getting the rowcount for COPY statements executed via cursor.execute. I've attached a PDF of the linked AWS forum page in case you can't access it.

    Amazon claims that Redshift correctly reports rowcount to the calling interface and that psycopg2 doesn't seem to pick it up.

    How can I validate Amazon's claim?

  • Daniele Varrazzo

    Daniele Varrazzo May 5th, 2014 @ 11:00 PM

    • State changed from “open” to “resolved”
    • Milestone set to psycopg2 2.5.3

    As Rui pointed out the number of rows is returned by PQcmdTuples. We weren't calling this function on copy results but only after normal opreations.

    Fixed in 2.5.3. Nicholas, try checking out and see if it works as expected, thank you.

  • Bilal Aslam

    Bilal Aslam May 27th, 2014 @ 06:02 PM

    Daniele, thanks for checking in the fix. I tried this out on May 27 using the branch you suggested, but I am still getting a rowcount of -1.

    In [36]: cursor.rowcount
    Out[36]: -1

    Nicholas, did you have any better luck than me?

  • Daniele Varrazzo

    Daniele Varrazzo May 27th, 2014 @ 07:32 PM

    Hello Bilal,

    yes, it works for me: check the test suite. The changeset is released in psycopg 2.5.3

    Note that old postgres versions (before 8.2) don't return the rowcount. So it depends on a message the server must send. If the server doesn't send it we cannot help.

  • Nicholas Chammas

    Nicholas Chammas May 27th, 2014 @ 10:09 PM

    Hey Daniele,

    It appears that we are still seeing the -1 rowcount on COPYs in psycopg 2.5.3 when testing against Redshift 1.0.789. This may well be an issue on Redshift's side, and I think it's beyond psycopg2's mandate to work cleanly with forks of PostgreSQL. (I think Bilal is also testing against Redshift.)

    FYI: Redshift is a distant fork of PostgreSQL 8 with a column-oriented storage engine, hosted as a service by Amazon. Its primary use case is for massive analytics workloads. Amazon recommends using PostgreSQL drivers to interact with the database.

    Anyway, I am following up with Amazon support here to get to the bottom of this. They claim it should work.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="">the new tracker</a>.
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins