CODEWITHSUNDEEP
pythonmysql
GobiasKoffi
GobiasKoffi

Reputation: 4084

Python and MySQLdb

I have the following query that I'm executing using a Python script (by using the MySQLdb module). 

conn=MySQLdb.connect (host = "localhost", user = "root",passwd = "<password>",db = "test")
cursor = conn.cursor ()
preamble='set @radius=%s; set @o_lat=%s; set @o_lon=%s; '%(radius,latitude,longitude)
query='SELECT *, (6371*1000 * acos(cos(radians(@o_lat)) * cos(radians(lat)) * cos(radians(lon) - radians(@o_lon)) + sin(radians(@o_lat)) * sin(radians(lat))) as distance FROM poi_table HAVING distance < @radius ORDER BY distance ASC LIMIT 0, 50)'
complete_query=preamble+query
results=cursor.execute (complete_query)
print results

The values of radius, latitude, and longitude are not important, but they are being defined when the script executes. What bothers me is that the snippet of code above returns no results; essentially meaning that the way that the query is being executed is wonky. I executed the SQL query (including the set variables with actual values, and it returned the correct number of results).

If I modify the query to just be a simple SELECT FROM query (SELECT * FROM poi_table) it returns results. What is going on here?

EDIT: Encapsulated Haversine formula calculation within parenthesis

Upvotes: 3

Views: 870

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169264

AFAIK you can't run multiple statements using execute().
You can, however, let MySQLdb handle the value substitutions.

Note that there are two arguments being passed to execute().
Also, just running execute() doesn't actually return any results.
You need to use fetchone() or fetchmany() or fetchall().

cursor.execute('''
    SELECT *, 
        6371*1000 * acos(cos(radians(%s)) * 
        cos(radians(lat)) * cos(radians(lon) - radians(%s)) + 
        sin(radians(%s)) * sin(radians(lat))) as distance 
    FROM 
        poi_table 
    WHERE distance < %s
    ORDER BY distance ASC 
    LIMIT 0, 50''', (latitude, longitude, latitude, radius,))
results = cursor.fetchall()
print results

Upvotes: 3

Ben Hoffstein
Ben Hoffstein

Reputation: 103325

Are you sure that the HAVING clause shouldn't be WHERE distance < @radius instead?

Upvotes: 2

Related Questions