Psycopg2 to create a record using a FK

[dieter]

Aaron Christensen <aaron.christensen at gmail.com> writes:
> 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.
>
> 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(), ??...??)

You can add "returning UserId" to this SQL command to get back
the id of the created user in your Python program. You can
then use this "UserId" to create the row in your dependent table.

I use it like this in one of my programs:

  cursor.execute("insert into service(...) "
                 "values (...) returning id",
                 (...)
                 )
  id = cursor.fetchone()[0]
  cursor.execute(
    "insert into product(..., f_service_id) "
    "values (..., %s) returning id",
    (..., id)
    )
  id = cursor.fetchone()[0]



Likely, there is also a way to bind the "UserId" inside SQL (maybe
via "SET") and use it in a second "INSERT" in the same call
to "cur.execute". Check the Postgres documentation for this.


> 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?

Sure -- see above.