Postgres Pro
Downloads
Home   >   mailing lists

Re: Query ID Values - Mailing list pgsql-general

From tango ward
Subject Re: Query ID Values
Date
Msg-id CAA6wQLLSW9YCe9PcLF1AiFMCtsq5dR3FK4kPZPkPgqYbk4YojQ@mail.gmail.com
Whole thread Raw
In response to Query ID Values  (tango ward <tangoward15@gmail.com>)
Responses Re: Query ID Values
List pgsql-general
Tree view
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)
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

pgsql-general by date:

Previous
From: Michael Paquier
Date:
Subject: Re: 10.4 upgrade, function markings, and template0
Next
From: tango ward
Date:
Subject: Re: Query ID Values