Psycopg2 to create a record using a FK

[Aaron Christensen]

On Sat, Mar 12, 2016 at 5:26 AM, Peter Otten <__peter__ at web.de> wrote:

> Aaron Christensen wrote:
>
> > Hello,
> >
> > I am running the following versions of software:
> >
> > Python 3.5
> > psycopg2==2.6.1
> > Postgres 9.4.5
> >
> > I have 2 tables.  Table User has UserId (serial PK), LastName, FirstName,
> > Gender, DateOfBirth, and DateEnrolled.  Table UserProfile has
> > UserProfileId
> > (serial, PK), UserId (FK), DateEntered, FaveNumber, and Activity.  There
> > is a one-to-many relationship.
> >
> > The following PostgreSQL works and ultimately creates a record in
> > UserProfile with an associated UserId (FK).
> >
> > \set last_name '''Sara'''
> > \set first_name '''Jackson'''
> > \set gender '''F'''
> > \set dob '''1941-1-12'''
> > \set fave_number '''3'''
> > \set activity '''volleyball'''
> >
> >
> > WITH ins_user AS (
> > INSERT INTO User
> > (LastName, FirstName, Gender, DateOfBirth, DateEnrolled)
> > VALUES (:last_name, :first_name, :gender, :dob, now())
> > RETURNING UserId)
> > INSERT INTO UserProfile
> > (UserId, DateEntered, FaveNumber, Activity)
> > VALUES ( (SELECT UserId FROM ins_user), now(), :fave_number :activity);
> >
> > How can I build a psycopg2 cur.execute query that will accomplish the
> > above
> > PostgreSQL?  I've read documentation but can't seem to get a handle on
> how
> > I should structure this command.
>
> I have not tried it, but wouldn't the straight-forward
>
> cur.execute("""
> WITH ins_user AS (
> INSERT INTO User
> (LastName, FirstName, Gender, DateOfBirth, DateEnrolled)
> VALUES (:last_name, :first_name, :gender, :dob, now())
> RETURNING UserId)
> INSERT INTO UserProfile
> (UserId, DateEntered, FaveNumber, Activity)
> VALUES ( (SELECT UserId FROM ins_user), now(), :fave_number :activity);
> """,
> dict(
>     first_name="Sara",
>     last_name="Jackson",
>     gender="F",
>     dob=datetime.date(1941, 1, 12),
>     fave_number=3,
>     activity="volleyball"
> ))
>
> work?
>

Hi Peter,

Yes, thank you.  It turns out that I can pass a dictionary as an argument.
I have combined your response with Dieter's and am running into some issues
now.  I need to figure out how to pass a dictionary AND the returned Id.

>
> > My starting point is:
> >
> > cur.execute( \
> > """INSERT INTO User \
> > (LastName, FirstName, Gender, DateOfBirth, DateEnrolled) \
> > VALUES (%s, %s, %s, %s, %s) RETURNING UserId;""", \
> > (last_name, first_name, gender, date_of_birth, now(), ??...??)
> >
> >
> > Also, I have a second question.  Is it possible to extract that value
> > derived from "RETURNING UserId" so that it can be used in a later query?
> >
> > Thank you for your time!
> > Aaron
>
>
> --
> https://mail.python.org/mailman/listinfo/python-list
>