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.kla...@aklaver.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 <tangowar...@gmail.com >> <mailto:tangowar...@gmail.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 <melvin6...@gmail.com >> <mailto:melvin6...@gmail.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 <tangowar...@gmail.com >> <mailto:tangowar...@gmail.com>> >> Date: 5/14/18 21:08 (GMT-05:00) >> To: "pgsql-generallists.postgresql.org >> <http://pgsql-generallists.postgresql.org>" >> <pgsql-general@lists.postgresql.org >> <mailto:pgsql-general@lists.postgresql.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.kla...@aklaver.com >