Transform SQL into Python

This is the place for queries that don't fit in any of the other categories.

Transform SQL into Python

Postby abhi1988srivastava » Wed Nov 20, 2013 7:54 pm

Hi,

I was looking at the DB queries of basic select and insert functions and was trying how to do them more precisely using comprehension like:

EXEC SQL SELECT count(*) INTO :namecnt
FROM student,teacher
WHERE stud_id=teach_id;

I am not good at comprehension so was not able to get through but if someone could guide me through this and also could give a small example of update command using comprehensions.

Thanks & Regards
abhi1988srivastava
 
Posts: 11
Joined: Mon Sep 09, 2013 5:54 pm

Re: Transform SQL into Python

Postby ochichinyezaboombwa » Wed Nov 20, 2013 10:13 pm

EXEC SQL
doesn't sound like SQL to me. What language are you trying to translate from? Cobol?..
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm

Re: Transform SQL into Python

Postby abhi1988srivastava » Thu Nov 21, 2013 12:41 am

sorry my bad, I was executing it through some script so copy pasted the same..

so that would be:

select count(*) from student, teacher where stud_id=teach_id;

thanks
abhi1988srivastava
 
Posts: 11
Joined: Mon Sep 09, 2013 5:54 pm

Re: Transform SQL into Python

Postby ochichinyezaboombwa » Thu Nov 21, 2013 12:51 am

That translates literally into what you'd use with say MySQLdb (except for the trailing ":" : you don't need it).
I don't understand why do you ask about "comprehensions".
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm

Re: Transform SQL into Python

Postby abhi1988srivastava » Thu Nov 21, 2013 1:09 am

If I suppose, students and teachers are my sets containing tuples and supposing 1st element of tuple is the ID then:

j=[j++ for student in students for teacher in teachers if teacher[0] is equal student[0] ]

I don't know how to correctly transform my sql query into the comprehension like above (which is wrong).

Thanks for the reply.

Abhinav
abhi1988srivastava
 
Posts: 11
Joined: Mon Sep 09, 2013 5:54 pm

Re: Transform SQL into Python

Postby Kebap » Thu Nov 21, 2013 10:20 am

abhi1988srivastava wrote:If I suppose, students and teachers are my sets containing tuples and supposing 1st element of tuple is the ID then:

j=[j++ for student in students for teacher in teachers if teacher[0] is equal student[0] ]

I don't know how to correctly transform my sql query into the comprehension like above (which is wrong).

Hi Abhinav,

If something does not work, it may help to split it into multiple lines, just for clarity. Also, use print for easy debugging.

Code: Select all
my_results = []
for student in students:
  for teacher in teachers:
    print student, teacher
    if student[0] == teacher[0]:
      my_results.append(student) # or whatever you really want to do here
Learn: How To Ask Questions The Smart Way
Join the #python-forum IRC channel on irc.freenode.net and chat with uns directly!
Kebap
 
Posts: 400
Joined: Thu Apr 04, 2013 1:17 pm
Location: Germany, Europe

Re: Transform SQL into Python

Postby abhi1988srivastava » Thu Nov 21, 2013 6:25 pm

Hey Kebap,

Thanks for the reply..but I was wondering if that can be expressed in comprehension for the problem that if IDs are same then increment the count and finally return the count..thats why in my comprehension I used "j++" to increment but I am not getting the exact thing how to do..

Thanks & Regards

Abhinav
abhi1988srivastava
 
Posts: 11
Joined: Mon Sep 09, 2013 5:54 pm

Re: Transform SQL into Python

Postby ochichinyezaboombwa » Thu Nov 21, 2013 11:46 pm

Code: Select all
students = [(1,"John D."), (11, "Mike B."), (10,"Emily C."), (100,"Chris D.")]
teachers = [(1,"Mr. XXX"), (10,"Mr. YYY"), (15, "Mr. ZZZ")]
joined = [(student,teacher)  for student in students for teacher in teachers if teacher[0] == student[0]]
print (joined)
# [((1, 'John D.'), (1, 'Mr. XXX')), ((10, 'Emily C.'), (10, 'Mr. YYY'))]

Or, if you just need count(*):
Code: Select all
print (len(joined))
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm

Re: Transform SQL into Python

Postby abhi1988srivastava » Fri Nov 22, 2013 1:00 am

That's what I was looking for..Thanks

Abhinav
abhi1988srivastava
 
Posts: 11
Joined: Mon Sep 09, 2013 5:54 pm


Return to General Coding Help

Who is online

Users browsing this forum: No registered users and 3 guests