converting a MySQL query to use in web2py DAL

Question

n a test (non-web2py) program, I'm using a MySQL query that invokes SELECT SUBSTRING_INDEX. What is the easiest way to convert this to the proper usage within web2py's DAL specifications?

The query is as follows:

http://pastie.textmate.org/3848916

SELECT SUBSTRING_INDEX( ipaddress, '.', 3 ) AS first_three_octet, count( * ) AS ipCount, updated
            FROM ips
            GROUP BY SUBSTRING_INDEX( ipaddress, '.', 3 )
            HAVING ipCount = 254 
            ORDER BY ipCount DESC

FYI - I've kludged together this code in the meantime to accomplish what I need:

def ListFullRanges():
    import re
    f3o = '(\d{1,3}\.\d{1,3}\.\d{1,3})'
    fullrange = []

    rg1 = re.compile(f3o,re.IGNORECASE|re.DOTALL)
    for row in db(db.ips).select():
        m = rg1.findall(row.ipaddress)
        if not m[0] in fullrange:
            if db(db.ips.ipaddress.startswith(m[0])).count() == 254:
                fullrange.append(m[0])
    print fullrange

    return dict(fr=fullrange)

score 1 · Answer 1 · answered May 02 '12 at 19:05

Sometimes there are very complex queries like these that are made specifically for a single database engine. While not the "perfect" solution, you can use the query you have already built for MySQL using:

db.executesql(
        "SELECT SUBSTRING_INDEX( ipaddress, '.', 3 ) AS first_three_octet, count( * ) AS ipCount, updated
        FROM ips
        GROUP BY SUBSTRING_INDEX( ipaddress, '.', 3 )
        HAVING ipCount = 254 
        ORDER BY ipCount DESC", as_dict=True
)

This will return a list of dictionaries, which will be similar to what you would get using a DAL query. Using executesql is also faster. The only downside to this is that it will likely only work with MySQL and you cannot use this with SQLFORM. But if you are only planning on using MySQL, then this might be the best solution.

converting a MySQL query to use in web2py DAL

1 Answers1