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

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.klaver@aklaver.com