Psycopg2 to create a record using a FK
Aaron Christensen
aaron.christensen at gmail.com
Fri Mar 11 22:01:55 EST 2016
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.
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
More information about the Python-list
mailing list