Re: Query ID Values

Noted Sir Adrian. The course name for the ones that are blank are not match
with the ones in the profile_program table. I am writing a CASE Statement
right now to verify the data but I can't make it work.:

for row in cur_t:
course = row['course']
cur_p.execute("""
SELECT id
FROM education_program
WHERE name=%s,
CASE
WHEN name='SENIOR HIGH SCHOOL GAS'
THEN name='General Academic Strand'
WHEN name='SENIOR HIGH SCHOOL HUMSS'
THEN name='Humanities and Social Sciences'
WHEN name='SENIOR HIGH SCHOOL STEM'
THEN name='Science, Technology, Engineering and
Mathematics'
END
AND department_id
IN (SELECT id
FROM profile_department
WHERE school_id=1)
""", [course])
x = cur_p.fetchone()
print row['firstname'], row['lastname'], course, x

On Tue, May 15, 2018 at 12:12 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/14/2018 08:30 PM, tango ward wrote:
>
>> 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
>>
>
> That would seem to indicate that the value of course is not matching any
> value in the field name for the given school_id. Maybe do:
>
> print(course)
>
> to see if they are valid values.
>
>
> the program_id seems to be in a nested list. Why is that?
>>
>
> Because you are doing fetchall(). That is going to fetch a list of row
> tuples. Either iterate over that list or iterate over the cursor:
>
> for row in cur_p:
> print(row)
>
> For more info see:
> http://initd.org/psycopg/docs/cursor.html
>
>
>> On Tue, May 15, 2018 at 10:47 AM, tango ward <tangoward15(at)gmail(dot)com
>> <mailto: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
>> <mailto: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
>> <mailto:tangoward15(at)gmail(dot)com>>
>> Date: 5/14/18 21:08 (GMT-05:00)
>> To: "pgsql-generallists.postgresql.org
>> <http://pgsql-generallists.postgresql.org>"
>> <pgsql-general(at)lists(dot)postgresql(dot)org
>> <mailto:pgsql-general(at)lists(dot)postgresql(dot)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
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>