Looping SQL query in python

Question

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.

Why not one query: select distinct...?

Parfait
– Parfait

2016-09-15 20:34:29 +00:00
Commented Sep 15, 2016 at 20:34 — Parfait
– Parfait, Commented Sep 15, 2016 at 20:34

Maurice · Accepted Answer · 2016-09-15 22:25:28Z

1

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.

edited Sep 15, 2016 at 22:25

answered Sep 15, 2016 at 20:28

Maurice

13.3k2 gold badges31 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Walter_Ritzel Over a year ago

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.

nits Over a year ago

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.

Maurice Over a year ago

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

nits Over a year ago

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

Collectives™ on Stack Overflow

Looping SQL query in python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related