2

I have an application that keeps track on items and uses SQLObject python ORM. At some point of time app checks if table is not too big, and if it's more than 10000 items in table it removes first N items so it's 10000 items in the table. What is the elegant and effective way to write this down via SQLObject? All solutions i come after are slow and awkward :(.

grigoryvp
  • 40,413
  • 64
  • 174
  • 277
  • Not sure if this is the right direction...perhaps you could write a trigger to check the table row count before insert, then on insert delete older records where the count will be equivalent to the number of new records you plan to insert.. – bonCodigo Jan 04 '13 at 15:29
  • @bonCodigo Does SQLObject support triggers? Is it possible? It can connect database that don't have any triggers, for example 'SQLite' O_O. – grigoryvp Jan 04 '13 at 15:47
  • It says in this [article](http://sourceforge.net/projects/sqlobject/) that it can support any of the major RDBMSes... [SQLite can create triggers](http://www.sqlite.org/lang_createtrigger.html). But why not a simple `delete` statment work for you? Sorry you really need an expert's advise here, Me: Ancora Imparo...and still exploring :D – bonCodigo Jan 04 '13 at 16:23
  • @bonCodigo Simple 'delete' statement don't work for me since i don't know how to delete first `N` items with *single* `delete` statement :(. Also, triggers will be database-specific and i don't like to write database-specific code :(. SQLite can use only simple SQL statements in triggers, i cant' seee a way to create trigger with condition. – grigoryvp Jan 04 '13 at 16:28
  • How do you define `first N` items? By ordering for oldest records or latest records? `case when(Table_Count) > 10000 then Table_count - 10000` gives the excess records... – bonCodigo Jan 04 '13 at 16:33
  • Items in the table are ordered by `id` key field that autoincrements with each new item being inserted (this is hos `SQLObject` works). So first `N` items are `N` items with lowest `id` numerical values. it's no problem with taking them - it's problem with efectively removing them, preferably with one `SQLObject` command / expression. – grigoryvp Jan 04 '13 at 17:03

3 Answers3

1

Personally I have not used SQLOBJECT. So this is a simple delete statement based on the ordering records by inserted date, oldest N records will be removed and it's in MYSQL. Unable to set limit clause using a variable due to this bug, so I used the most direct method.

For this sampel I used the threshold as 6 as it's hard to have 10000...

Sample table:

COL1    COL2
1       112
2       0
3       20
4       10
5       100
6       30
7       200
8       108

set @table_count:=0;
set @excess_count:=0;

select count(*) into @table_count
from tablex;

select (case when @table_count > 6
then (@table_count -6)
else 0 end) into @excess_count
;

select * 
from tablex
where col1 between 1 and @excess_count
order by col1 asc
;

Records selected:

COL1    COL2
1       112
2       0

Above query sorts records by col1 you may change it according to yours. So the delete query can be built upon the last select. So it's best to write a storedprocedure...as this is all one akward bunch of statements...

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
1

I know this thread is old, but I thought to post an entry in case someone comes across it via a Google search, just like I did.

I had a similar problem where I needed custom delete call. SQLObject gives you two options for creating a SQL DELETE with a custom where clause:

I went with the second option as it was more flexible and streamlined:

dsqlMetricseries._connection.query("DELETE FROM dsql_metricseries WHERE metric_id = %s"%metric_id)
  • dsqlMetricseries : SQLObject subclass, model class representing a DB table
  • _connection : handle to SQLObject's internal DB connection
  • query : SQLObject method for executing any SQL statement
John Shafaee
  • 11
  • 1
0

As above, I realise this is an old post however other viewers may find this useful. I'm not sure how efficient this would be slicing the select (assuming record id is ascending as you add records).

myselect = tableobject.select(orderBy=tableobject.q.id)
if myselect.count() > 10000:
    tableobject.deleteMany(tableobject.q.id<myselect[10001].id)

Untested, but I routinely use deleteMany().

user3109122
  • 81
  • 1
  • 1