Re: Query ID Values

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?

On Tue, May 15, 2018 at 10:47 AM, tango ward <tangoward15(at)gmail(dot)com> wrote:

> 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
> | |
>
>
>
> 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.
>
> 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.
>
>
> 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 x
>
>
>
> Sorry for asking questions a lot, we don't have DBA at the moment.
>
>
> Thanks,
> J
>
>
> On Tue, May 15, 2018 at 9:57 AM, melvin6925 <melvin6925(at)gmail(dot)com> 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(at)gmail(dot)com>
>> Date: 5/14/18 21:08 (GMT-05:00)
>> To: "pgsql-generallists.postgresql.org" <pgsql-general(at)lists(dot)postgresq
>> l.org>
>> Subject: Query ID Values
>>
>>
>> Good Day,
>>
>> 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.
>>
>> 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.
>>
>> May I ask an advice on how to approach this?
>>
>>
>> Thanks,
>> J
>>
>
>