Psycopg2 to create a record using a FK

[Aaron Christensen]

On Sat, Mar 12, 2016 at 9:57 PM, Aaron Christensen <
aaron.christensen at gmail.com> wrote:

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


Dieter,

I think I finally figured it out!

In this section:
UserId = cur.fetchone()[0]        #< ----------   HERE is the UserId
print("UserId = %s" % UserId)

I added user_id to the dictionary and passed the dictionary to the
cur.execute():
user_id = cur.fetchone()[0]
user_profile_input['user_id'] = user_id

cur.execute("""
INSERT INTO UserProfile
(...)
VALUES (...)
RETURNING UserProfileId""",
(user_profile_input)

That seemed to have done the trick.  However, I hope that the way my code
is structured is a decent design with the multiple conn.commit()s.  Should
I need to close the cursor after every Table cur.execute() command?

Thanks!
Aaron