converting a MySQL query to use in web2py DAL

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)

Answers


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.


Need Your Help

How to bundle the Azure accounts included in our MS VS Licenses

azure accounts

Our company has until bought a lot VS Pro/Premium and Ultimate Licenses, and each includes a free Azure Account.

How to get the 'current' navigation controller from tab bar controller

objective-c ios uinavigationcontroller uitabbarcontroller

Is there is a method to retrieve tab bar controller's current visible navigation controller?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.