Re: Query ID Values
От | Adrian Klaver |
---|---|
Тема | Re: Query ID Values |
Дата | |
Msg-id | 927dc5f8-b343-c7ff-e131-17c5c3dc6814@aklaver.com обсуждение исходный текст |
Ответ на | Re: Query ID Values (tango ward <tangoward15@gmail.com>) |
Ответы |
Re: Query ID Values
(tango ward <tangoward15@gmail.com>)
Re: Query ID Values (Ian Zimmerman <itz@very.loosely.org>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: cwlistsДата:
Сообщение: Re: RPM packages 10.4 for rhel7 x86_86 are build as f25.x86_64.rpm