Psycopg2 to create a record using a FK

[Aaron Christensen]

On Sat, Mar 12, 2016 at 5:03 AM, dieter <dieter at handshake.de> wrote:

> 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.
>
> --
> https://mail.python.org/mailman/listinfo/python-list


Hi Dieter,

Thanks for the response.  I managed to get it working and also combined it
with Peter's suggestion of passing a dictionary as an argument.  However, I
am trying to figure out how I can get the RETURNING ID to be used in the
next cur.execute().  Here is what I have been working on but have found
many ways for it not to work.  My latest response is that the tuple indices
must be integers or slices.

# Here I initialize the dictionaries.  I will use each dictionary as an
input into each cur.execute()
user_input = dict(
last_name = 'Jackson',
first_name = 'Sara',
gender = 'F',
date_of_birth = '1941-1-12'
)

user_profile_input = dict(
fave_number = 3,
activity = 'volleyball'
)



# Create record in User // cur.execute(query, user_input)
cur.execute("""
INSERT INTO User
(LastName, FirstName, Gender, DateOfBirth)
VALUES (%(last_name)s, %(first_name)s, %(gender)s, %(date_of_birth))
RETURNING UserId""",
user_input)
conn.commit()
UserId = cur.fetchone()[0]        #< ----------   HERE is the UserId
print("UserId = %s" % UserId)

# Create record in UserProfile // cur.execute(query, user_profile_input)
cur.execute("""
INSERT INTO UserProfile
(FaveNumber, Activity, UserId)
VALUES (%(fave_number)s, %(activity)s, %s)    <------------ I tried
following your format
RETURNING UserProfileId""",
(user_profile_input, UserId)              # <---- This is what I'm trying
to figure out..  How to pass the UserId.
)
conn.commit()
UserProfileId = cur.fetchone()[0]
print("UserProfileId = %s" % UserProfileId)