Looping SQL query in python
<p>I am writing a python script which queries the database for a URL string. Below is my snippet.</p>

<pre><code>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()
</code></pre>

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

<pre><code>output: https://www.youtube.com/watch?v=uqcSJR_7fOc
</code></pre>

<p>SiteID is the primary key which is int and not in sequence. </p>

<p>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. </p>

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

<p>There are about 1178 records.</p>

<p>Thanks for your time and help in advance. </p>

<p>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 <code>sitevideobaseurl</code> from <code>site</code> and <code>videositestring</code> from <code>video</code>.</p>

<p>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.</p>

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

<pre><code>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()
</code></pre>

<p>If you have other reasons for wanting to fetch these ony by one an idea might be to fetch a list of all <code>SiteID</code>s 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.</p>

python mysql loops