Psycopg2 to create a record using a FK

[Peter Otten]

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?

> 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