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.postgresq
l.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&gt;&quot;
<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 <http://initd.org/psycopg/docs/cursor.html#cursor&gt; objects are
iterable, so, instead of calling explicitly fetchone()
<http://initd.org/psycopg/docs/cursor.html#cursor.fetchone&gt; 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
<http://initd.org/psycopg/docs/usage.html#server-side-cursors&gt; fetches
itersize <http://initd.org/psycopg/docs/cursor.html#cursor.itersize&gt;
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.

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@aklaver.com

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@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&gt;&quot;
<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