Here's what I used:
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():#db.ips.ipaddress, db.ips.reportedby,
db.ips.dateadded):
m = rg1.findall(row.ipaddress)
if not m[0] in fullrange:
if (db(db.ips.ipaddress.startswith(m[0])).count() == 254) and
(row.reportedby == "RBLmilter"):
fullrange[m[0]] = row.dateadded
On Wednesday, May 2, 2012 2:14:26 PM UTC-4, Richard wrote:
>
> You could try also this :
>
> a = [ row.ipaddress for row in db().select(db.ips.ipaddress) if
> row.ipaddress.split('.')[3] == '35' ]
>
> db(db.ips.ipaddress.belongs(tuple(a))).select(db.ips.ipaddress).first().ipaddress
>
> And for the HAVING clause I solve it earlier this week like this :
>
> list_value = []
> count = db.table1.field1.count()
> for row in db().select(db.table1.field1, count, groupby=db.table1.field1):
> if row[count]>1:
> #print row.table1.field1, row[count]
> list_value.append(row.table1.field1)
>
> So, if you combine both you should have something that do what you want!
>
> Richard
>
> On Wed, May 2, 2012 at 1:49 PM, Richard Vézina <
> [email protected]> wrote:
>
>> Maybe raw SQL??
>>
>> Richard
>>
>>
>> On Wed, May 2, 2012 at 10:54 AM, Larry Wapnitsky <[email protected]>wrote:
>>
>>> Here's the actual query:
>>>
>>> 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
>>>
>>>
>>> On Tuesday, May 1, 2012 3:19:27 PM UTC-4, Larry Wapnitsky wrote:
>>>
>>>> In 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?
>>>>
>>>> Thanks,
>>>> Larry
>>>>
>>>
>>
>
