Re: Query ID Values - Mailing list pgsql-general

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@gmail.com 
> <mailto:tangoward15@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 <melvin6925@gmail.com
>     <mailto:melvin6925@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 <tangoward15@gmail.com
>         <mailto:tangoward15@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.klaver@aklaver.com