mysql python combine results

Question

trying to write the following in python more elegantly...from mysql perspective. You will notice I am trying to combine results in one table based upon the entries in another. What is the proper,clean, industry smart way of writing such SQL query?

Essentially, I would like to say this... "select all urls from urls table, which belong to some site group, in sites table"

Thanks!

site = sys.argv[0]
checksanity (log,site) #check syntax, etc

log.info ("Running site %s", site)
cursor = conn.cursor ()

#get siteid
query = "SELECT sites.id from sites WHERE sitename LIKE '" + site + "'"
cursor.execute (query)
siteidlong = cursor.fetchone()
siteid = str(siteidlong[0])

query = "SELECT search_for,urls.url FROM urls WHERE site_id LIKE '" + siteid + "'"
print query
cursor.execute (query)
resultstring = cursor.fetchall()
print resultstring

cursor.close ()
conn.close ()

Don't build queries with string formatting - it leaves you very open to SQL injection attacks. — Swiss
– Swiss, Commented Sep 13, 2011 at 22:56

Alain Collins · Accepted Answer · 2011-09-13 22:51:48Z

4

Welcome to SQL. Now that you've written two queries, it's time to learn about JOIN and SQL injection.

select *
from urls, sites
where urls.site_id = sites.id
and sitename like ?

Good luck.

answered Sep 13, 2011 at 22:51

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

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

1 Comment

Neil Over a year ago

Note that we prefer people to use SELECT * FROM urls INNER JOIN sites ON urls.site_id = sites.id WHERE sitename LIKE ? rather than the old join syntax.

Collectives™ on Stack Overflow

mysql python combine results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related