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

On 02/12/2015 02:30 PM, Octavi Fors wrote:
> 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.

Used to be Pandas maintained its own database code, which is where
'flavor' comes from. For legacy purposes that can still be used. Now
Panda SQLAlchemy for its database connectivity, so if SQLAlchemy
supports a database, so does Pandas.

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

http://stackoverflow.com/questions/24189150/pandas-writing-dataframe-to-other-postgresql-schema

>
> Cheers,
>
> Octavi.
>
> On Thu, Feb 12, 2015 at 3:30 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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
>     <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 <mailto:adrian.klaver@aklaver.com>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com