Search Postgresql Archives

Re: Query ID Values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'd bottom-post, as is the convention for these lists, but it seems pointless now...

CASE _expression_
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END
Try that where _expression_ is the %s.  The values and results are simple literals.  And you compare the result of the _expression_ to "name".

Or just do the rename in python, not sql.

David J.

On Monday, May 14, 2018, tango ward <tangoward15@xxxxxxxxx> wrote:
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@xxxxxxxxxxx> 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@xxxxxxxxx <mailto:tangoward15@xxxxxxxxx>> 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@xxxxxxxxx
    <mailto:melvin6925@xxxxxxxxx>> 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@xxxxxxxxx
        <mailto:tangoward15@xxxxxxxxx>>
        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@xxxxxxxxstgresql.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@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux