for row in cur_t:
course = row['course']
cur_p.execute("""
SELECT id
FROM education_program
WHERE name=%s
AND department_id
IN (SELECT id FROM profile_department WHERE school_id=1)
""", (course,))
x = cur_p.fetchall()
print x
So far I can see the program IDs but I am still getting empty list. Also the program_id seems to be in a nested list. Why is that?course = row['course']
cur_p.execute("""
SELECT id
FROM education_program
WHERE name=%s
AND department_id
IN (SELECT id FROM profile_department WHERE school_id=1)
""", (course,))
x = cur_p.fetchall()
print x
On Tue, May 15, 2018 at 10:47 AM, tango ward <tangoward15@xxxxxxxxx> wrote:
I have already migrated the course data to another table. What I would like to do is get the value of program_id and department_id for the profile table. I want to check if the course exist in profile_program table, then get it's ID. I think I can use the same logic for getting and setting value for the department_id column of profile table. I am using psycopg2 to access and move the data.So I am migrating the data from one database to another. Here, I am moving data of student from student_profile table to profile table.Hi,Yes sorry, here's the tables:
[cur_t DB] [student_profile table]
Column | Type | Collation | Nullable | Default |
----------------------+------------------------+-----------+ ----------+------------------- ----+
studentnumber | character varying(45) | | not null | ''::character varying |
firstname | character varying(60) | | | |
middlename | character varying(60) | | | |
lastname | character varying(60) | | | |
course | character varying(150) | | not null | ''::character varying |[cur_p DB] [profile table]
Column | Type | Collation | Nullable | Default |
----------------------+------------------------+-----------+ ----------+------------------- ----+
studentnumber | character varying(45) | | not null | ''::character varying |
firstname | character varying(60) | | | |
middlename | character varying(60) | | | |
lastname | character varying(60) | | | |
program_id | integer | | not null | |
department_id | integer | | not null | |
campus_id | integer | | not null | |for row in cur_t:course = row['course']# Here I would like to get the value of program_id and department_id and insert it to the said columns but I don't know how to do it yet# I put ?? in department_id coz I don't know how to access the 3 department IDs in this query.cur_p.execute(""" SELECT id from st_profile where name='$[course]' and department_id=?? """)x = cur_p.fetchall()# This will print an error since I added department_id without value yet but if I remove it, I will get "None"print xSorry for asking questions a lot, we don't have DBA at the moment.Thanks,JOn Tue, May 15, 2018 at 9:57 AM, melvin6925 <melvin6925@xxxxxxxxx> wrote:Perhaps if you care to provide us with the structure of all tables involved, we could suggest a reasonable query.Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone-------- Original message --------From: tango ward <tangoward15@xxxxxxxxx>Date: 5/14/18 21:08 (GMT-05:00)Subject: Query ID ValuesJThanks,May I ask an advice on how to approach this?I need to run an SQL query and get a program_id and department_id of a specific course for each student. I am thinking of running an IF condition to check if the course name is in program and get it's ID but I don't know yet where to use the IF condition in the query.Good Day,sample code:for row in cur_t:course = row['course']cur_p.execute("""SELECT id from program where name='$[course]']WHERE department_id=?? """)Problem is I have 3 department IDs ( Senior High, Vocational, Undergraduate ) and each ID have multiple programs/courses. Each program/course is connected to the deparment table via department_id.
