Re: Query ID Values

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(at)gmail(dot)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(at)gmail(dot)com>
> Date: 5/14/18 21:08 (GMT-05:00)
> To: "pgsql-generallists.postgresql.org" <pgsql-general(at)lists(dot)
> 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
>