Thread: Query ID Values

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>

Date: 5/14/18 21:08 (GMT-05:00)

To: "pgsql-generallists.postgresql.org" <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

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> 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>

Date: 5/14/18 21:08 (GMT-05:00)

To: "pgsql-generallists.postgresql.org" <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

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

On 2018-05-14 21:12, Adrian Klaver wrote:

> 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

Where does that webpage say that I can use the cursor itself for
iteration?  I can't find it there.  (OTOH it is clearly documented for
the sqlite3 library).

Until now, with psycopg2 I have done it like this:

cur.execute(stmt)
results = iter(cur.fetchone, None)
for r in results:
    ...

--
Please don't Cc: me privately on mailing lists and Usenet,
if you also post the followup to the list or newsgroup.
To reply privately _only_ on Usenet and on broken lists
which rewrite From, fetch the TXT record for no-use.mooo.com.

I thing its this:

"

Note

cursor objects are iterable, so, instead of calling explicitly fetchone() in a loop, the object itself can be used:

>>> cur.execute("SELECT * FROM test;")
>>> for record in cur:
...     print record
...
(1, 100, "abc'def")
(2, None, 'dada')
(3, 42, 'bar')

Changed in version 2.4: iterating over a named cursor fetches itersize records at time from the backend. Previously only one record was fetched per roundtrip, resulting in a large overhead.

"

On Tue, May 15, 2018 at 1:04 PM, Ian Zimmerman <itz@very.loosely.org> wrote:

On 2018-05-14 21:12, Adrian Klaver wrote:

> 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

Where does that webpage say that I can use the cursor itself for
iteration?  I can't find it there.  (OTOH it is clearly documented for
the sqlite3 library).

Until now, with psycopg2 I have done it like this:

cur.execute(stmt)
results = iter(cur.fetchone, None)
for r in results:
    ...

--
Please don't Cc: me privately on mailing lists and Usenet,
if you also post the followup to the list or newsgroup.
To reply privately _only_ on Usenet and on broken lists
which rewrite From, fetch the TXT record for no-use.mooo.com.