Re: how to insert with a single cur.execute()/SQL command in 3 tables?

Thanks Adrian for helping.

Since you have the FK relationships you will need to do three queries from
> the bottom up filename, image, sourcecat. If you have not already I would
> take a look at Pandas IO functions:
>
> http://pandas.pydata.org/pandas-docs/stable/io.html
>
> In particular to_sql. Pandas can take numpy arrays and turn them into
> DataFrames to export and do the reverse on import.
>

I'm new with Pandas, but after reading pandas.DataFrame.to_sql documentation
<http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html#pandas.DataFrame.to_sql>,
postgresql doesn't seem to be supported in flavor parameter.

Could you or anybody please provide a snippet code example I could start
with?

Cheers,

Octavi.

On Thu, Feb 12, 2015 at 3:30 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 02/12/2015 12:06 PM, Octavi Fors wrote:
>
>> Hello psycopg gurus,
>>
>> my question might not be specific for psycopg mailing list, but since
>> I'm interfacing PostgreSQL server with python.psycopg module, I thought
>> it'd be a good place to ask.
>>
>>
>> I have the 3 below tables created in a postgres database.
>> As you see sourcecat has a <fk> linked to image, and image another <fk>
>> linked to filename.
>>
>> I'm trying to populate these 3 tables using python.psycopg module, since
>> I have all the data stored in FITS binary files, and I want to have such
>> data well formalized in a relational db such as postgres.
>>
>> Assuming I have all rows from these 3 tables stored in numpy arrays, is
>> there any way to insert them in a single cur.execute() command?
>>
>> If a single cur.execute()/SQL command is not possible, which would be
>> the commands sequence?
>>
>
> Since you have the FK relationships you will need to do three queries from
> the bottom up filename, image, sourcecat. If you have not already I would
> take a look at Pandas IO functions:
>
> http://pandas.pydata.org/pandas-docs/stable/io.html
>
> In particular to_sql. Pandas can take numpy arrays and turn them into
> DataFrames to export and do the reverse on import.
>
>
>> Thanks in advance,
>>
>> Octavi.
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>