Обсуждение: how to insert with a single cur.execute()/SQL command in 3 tables?
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?Thanks in advance,
Octavi.
CREATE TABLE filename
(
fn_id bigserial NOT NULL, -- Primary key
fn_pathname character varying(90),
fn_origname character varying(70),
CONSTRAINT pk_fn_id PRIMARY KEY (fn_id)
)
CREATE TABLE image
(
img_id bigserial NOT NULL, -- Primary key
img_nameid bigint NOT NULL,
img_naxis1 smallint NOT NULL,
img_naxis2 smallint NOT NULL,
img_bitpix smallint NOT NULL,
img_bscale smallint NOT NULL DEFAULT 1,
img_bzero smallint NOT NULL DEFAULT 0,
CONSTRAINT pk_img_id PRIMARY KEY (img_id),
CONSTRAINT fk_img_nameid FOREIGN KEY (img_nameid)
REFERENCES filename (fn_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE sourcecat
(
src_id integer NOT NULL DEFAULT nextval('srccat_src_id_seq'::regclass),
src_imgid bigint NOT NULL DEFAULT nextval('srccat_src_imgid_seq'::regclass),
src_flux_auto real,
src_fluxerr_auto real,
src_xwin_image real,
src_ywin_image real,
src_alphawin_j2000 double precision,
src_deltawin_j2000 double precision,
src_elongation real,
src_flags character varying(3),
CONSTRAINT pk_src_imgid_id PRIMARY KEY (src_imgid, src_id),
CONSTRAINT fk_src_imgid FOREIGN KEY (src_imgid)
REFERENCES image (img_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
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
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, postgresql doesn't seem to be supported in flavor parameter.
Could you or anybody please provide a snippet code example I could start with?
On Thu, Feb 12, 2015 at 3:30 PM, Adrian Klaver <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
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
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