CODEWITHSUNDEEP
pythonsqldatabasepostgresqlpsycopg2
enter_display_name_here
enter_display_name_here

Reputation: 708

Psycopg2 to create a record using a foreign key

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(), ??...??)

In addition, 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!

Upvotes: 2

Views: 3028

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125504

It is clearer to use a dictionary to pass the parameters

ins = """
    with ins_user as (
        insert into User (
            LastName, FirstName, Gender, DateOfBirth, DateEnrolled
        ) values (%(last_name)s, %(first_name)s, %(gender)s, %(dob)s, now())
        returning UserId
    )
    insert into UserProfile (
        UserId, DateEntered, FaveNumber, Activity
    ) values ((select UserId from ins_user), now(), %(fave_number)s, %(activity)s)
"""

ins_dict = dict (
    last_name = 'Sara',
    first_name = 'Jackson',
    gender = 'F',
    dob = '1941-1-12',
    fave_number = '3',
    activity = 'volleyball'
)
print cur.mogrify(ins, ins_dict) # inspect the rendered command
cur.execute(ins, ins_dict)
connection.commit()

If DateEnrolled will always receive now() then make it the default

DateEnrolled date default now(),

Upvotes: 4

xenon
xenon

Reputation: 21

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(), ??...??)

Upvotes: -2

Related Questions