Re: Query ID Values - Mailing list pgsql-general

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.klaver@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 <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