Looping SQL query in python

Question 1

I am writing a python script which queries the database for a URL string. Below is my snippet.

db.execute('select sitevideobaseurl,videositestring '
 'from site, video '
 'where siteID =1 and site.SiteID=video.VideoSiteID limit 1')
result = db.fetchall()
filename = '/home/Site_info'
output = open(filename, "w")
for row in result:
 videosite= row[0:2]
 link = videosite[0].format(videosite[1])
 full_link = link.replace("http://","https://")
 print full_link
 output.write("%s\n"%str(full_link))
output.close()

The query basically gives a URL link.It gives me baseURL from a table and the video site string from another table.

output: https://www.youtube.com/watch?v=uqcSJR_7fOc

SiteID is the primary key which is int and not in sequence.

I wish to loop this sql query to pick a new siteId for every execution so that i have unique site URL everytime and write all the results to a file.

desired output: https://www.youtube.com/watch?v=uqcSJR_7fOc
 https://www.dailymotion.com/video/hdfchsldf0f

There are about 1178 records.

Thanks for your time and help in advance.

Question 2

Why not one query: select distinct...?

Question 3

I'm not sure if I completely understand what you're trying to do. I think your goal is to get a list of all links to videos. You get a link to a video by joining the sitevideobaseurl from site and videositestring from video.

From my experience it's much easier to let the database do the heavy lifting, it's build for that. It should be more efficient to join the tables, return all the results and then looping trough them instead of making subsequent queries to the database for each row.

The code should look something like this: (Be careful, I didn't test this)

query = """
select s.sitevideobaseurl, 
 v.videositestring
 from video as v
 join site as s
 on s.siteID = v.VideoSiteID
 """
db.execute(query)
result = db.fetchall()
filename = '/home/Site_info'
output = open(filename, "w")
for row in result:
 link = "%s%s" % (row[0],row[1])
 full_link = link.replace("http://","https://")
 print full_link
 output.write("%s\n" % str(full_link))
output.close()

If you have other reasons for wanting to fetch these ony by one an idea might be to fetch a list of all SiteIDs and store them in a list. Afterwards you start a loop for each item in that list and insert the id into the query via a parameterized query.

Question 4

nits, I think @Maurice answer is exactly what you need. Your code was almost correct, but you are limiting the query to get just one row each time you execute the application, and for each run, you probably would need to change the id... Maurice solution is much more efficient and more like we use to code in applications like this.

Question 5

Hi Maurice.That is exactly what i want. But your query gives me the output: youtube.com/watch?v= veoh.com/videos. Its not joining the site string. Been long time that i wrote sql queries. any input regarding this is appreciated.

Question 6

Hey Nits, I just took your code for further processing. I edited my response and changed the processing a little bit, please try again.

Question 7

Any help in storing the list of siteId into a list and start a loop by inserting id into query via parameterized query. I am not able to figure that out. Thanks in advance

Maurice 13.4k2 gold badges31 silver badges55 bronze badges · Accepted Answer · 2016-09-15 20:28:17Z

I'm not sure if I completely understand what you're trying to do. I think your goal is to get a list of all links to videos. You get a link to a video by joining the sitevideobaseurl from site and videositestring from video.

From my experience it's much easier to let the database do the heavy lifting, it's build for that. It should be more efficient to join the tables, return all the results and then looping trough them instead of making subsequent queries to the database for each row.

The code should look something like this: (Be careful, I didn't test this)

query = """
select s.sitevideobaseurl, 
 v.videositestring
 from video as v
 join site as s
 on s.siteID = v.VideoSiteID
 """
db.execute(query)
result = db.fetchall()
filename = '/home/Site_info'
output = open(filename, "w")
for row in result:
 link = "%s%s" % (row[0],row[1])
 full_link = link.replace("http://","https://")
 print full_link
 output.write("%s\n" % str(full_link))
output.close()

If you have other reasons for wanting to fetch these ony by one an idea might be to fetch a list of all SiteIDs and store them in a list. Afterwards you start a loop for each item in that list and insert the id into the query via a parameterized query.

nits, I think @Maurice answer is exactly what you need. Your code was almost correct, but you are limiting the query to get just one row each time you execute the application, and for each run, you probably would need to change the id... Maurice solution is much more efficient and more like we use to code in applications like this.
Hi Maurice.That is exactly what i want. But your query gives me the output: youtube.com/watch?v= veoh.com/videos. Its not joining the site string. Been long time that i wrote sql queries. any input regarding this is appreciated.
Hey Nits, I just took your code for further processing. I edited my response and changed the processing a little bit, please try again.
Any help in storing the list of siteId into a list and start a loop by inserting id into query via parameterized query. I am not able to figure that out. Thanks in advance

CollectivesTM on Stack Overflow

Looping SQL query in python

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

CollectivesTM on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related