Postgres Pro
Downloads
Home   >   mailing lists

Thread: importing downloaded data

importing downloaded data

From
Sim Zacks
Date:
body p { margin-bottom: 0cm; margin-top: 0pt; }

  <body style="direction: ltr;"
    bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
    text="#000000">
    Postgresql 9.3
    I am downloading data that I want to import into a table. The data
    comes in tab delimited, CRLF format.
    I am using plpython to get the data and I wanted to use copy with
    stdin to import it without having to save it to a file.
    To do this, I am setting sys.stdin to a StringIO object with my data
    inside.

    However, when I call copy I am getting SPI_ERROR_COPY, which the
    docs define as "if COPY TO stdout or COPY FROM stdin was attempted" 
    which is exactly what I am trying to do (so the error is logical).

    Is there another method of loading delimited data in bulk without
    saving it as a file<span style="color: rgb(0, 0, 0); font-family:
      verdana, sans-serif; font-size: 12.499999046325684px; font-style:
      normal; font-variant: normal; font-weight: normal; letter-spacing:
      normal; line-height: 18.240001678466797px; orphans: auto;
      text-align: left; text-indent: 0px; text-transform: none;
      white-space: normal; widows: auto; word-spacing: 0px;
      -webkit-text-stroke-width: 0px; background-color: rgb(255, 255,
      255); display: inline !important; float: none;">?

      Thanks
      Sim

Re: importing downloaded data

From
Adrian Klaver
Date:
On 04/22/2014 07:39 AM, Sim Zacks wrote:
> Postgresql 9.3
> I am downloading data that I want to import into a table. The data comes
> in tab delimited, CRLF format.
> I am using plpython to get the data and I wanted to use copy with stdin
> to import it without having to save it to a file.
> To do this, I am setting sys.stdin to a StringIO object with my data inside.


Not sure that is going to work as:

STDIN

     Specifies that input comes from the client application.

I would say a plpython function is actually running as the server and is
not a client.

>
> However, when I call copy I am getting SPI_ERROR_COPY, which the docs
> define as "if COPY TO stdout or COPY FROM stdin was attempted" which is
> exactly what I am trying to do (so the error is logical).
>
> Is there another method of loading delimited data in bulk without saving
> it as a file?

I do it by pushing the data in using a Python script that uses the the
COPY FROM/TO methods of psycopg2:

http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from

>
> Thanks
> Sim


--
Adrian Klaver
adrian.klaver@aklaver.com