Re: Query ID Values

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