Deleting a db table row

[Auden RovelleQuartz]

I hope this is a simple question:

Specifically (syntax-wise) how does one delete a specific table row,
but INDEPENDENT of the ".id" field?

for example, lets say that I have the following rows in a database
table:

bid.id bid.bid_id
1 LJWI
2 LJWJ
3 LJWK
4 LJWL

I know that I can use the id field to specify the row to be deleted
and end up with:

bid.id bid.bid_id
1 LJWI
3 LJWK
4 LJWL

but what if the table will be regularly experiencing row deletions
(from multiple other users), I have a table now looking like for
example

bid.id bid.bid_id
17 LJWI
32 PWRT
54 AWQS
116 IYRF

If i want to repeatedly delete the SECOND ROW of the table (and I
don't know nor care what the id field is), what is the syntax to
specify that I simply want to delete the second row of table 'bid'
without referencing any of the fields?

[Thadeus Burgess]

rows = db(db.bid.id > 0).select(limitby=(1, 2), orderby=db.bid.id)

rows.first().delete_record()

Test that out a bit see if it does what you want.

-Thadeus

> --
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
>
>

[Thadeus Burgess]

This does delete the row from the actual database table....

acts on the database
row.delete_record()
row.update_reocrd()

works on the recordset.
row.delete()
row.update()

This is because delete() and update() are python builtin dict functions, that
is why it uses this naming scheme.

-Thadeus

On Wed, Feb 10, 2010 at 11:13 AM, Auden RovelleQuartz
<oves...@gmail.com> wrote:
> thanks for the quick response!
> question this seems to be modifying a record set - what if I need to
> literally delete the row from the actual database table (instead of from a
> recordset copy of the table)?

>
> On Wed, Feb 10, 2010 at 11:06 AM, Thadeus Burgess <thad...@thadeusb.com>
> wrote:
>>
>> rows = db(db.bid.id > 0).select(limitby=(1, 2), orderby=db.bid.id)
>>
>> rows.first().delete_record()
>>
>> Test that out a bit see if it does what you want.
>>
>> -Thadeus
>>
>>
>>
>>
>>
>> On Wed, Feb 10, 2010 at 10:59 AM, Auden RovelleQuartz
>> <oves...@gmail.com> wrote:

[Thadeus Burgess]

A Rows object is just an array with database specific operations
added. So you can go

rows = db(db.table.id > 0).select()
rows[n].delete_record()

if you know what (n) is.

first() syntax has been added since the manual was published. However,
it is just a shortcut to the following, but returns None instead of an
index error if there are no records.

.first() - rows[0]
.last() - rows[-1]

May I ask what design you are using that you actually need to delete N
row and you don't know its ID ?

-Thadeus

On Wed, Feb 10, 2010 at 12:05 PM, Auden RovelleQuartz
<oves...@gmail.com> wrote:
> e to understand why/how it works - is that "first()" syntax covered in any
> reference manual or tutorial that you are aw

[mdipierro]

db(db.bid.id.belongs(db()._select(db.bid.id,orderby=db.bid.id,limitby=(1,2))).delete()

does not work on GAE on GAE you would do something like

id = db()._select(orderby=db.bid.created_on,limitby=(1,2)).first().id

db(db.bid.id==id).delete()

[Maurice Waka]

Hi.
I happened to have the same issue as this:

Specifically (syntax-wise) how does one delete a specific table row,
but INDEPENDENT of the ".id" field?

for example, lets say that I have the following rows in a database
table:

bid.id bid.bid_id
1 LJWI
2 LJWJ
3 LJWK
4 LJWL

I know that I can use the id field to specify the row to be deleted
and end up with:

bid.id bid.bid_id
1 LJWI
3 LJWK
4 LJWL

but what if the table will be regularly experiencing row deletions
(from multiple other users), I have a table now looking like for
example

bid.id bid.bid_id
17 LJWI
32 PWRT
54 AWQS
116 IYRF

In this case, there are multiple users inserting and deleting rows from the db. I want ONLY the specific user who inserted the record to delete it. How do I code it. It has to be dependent on the logged in user. I have tried the above examples with some frustration.
Kind regards

[黄祥]

think you can do it by store the user created id (auth.signature or code it by your self) in your table and use it as a condition
e.g. not tested

query = ((db.table.id == id) & (db.table.created_by == auth.user_id) )

db(query).delete()

best regards,

stifan

[Maurice Waka]

I have tried this example but I get this error below:

db(query).delete()
 File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2277, in delete
 ret = db._adapter.delete(table, self.query)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/sqlite.py", line 86, in delete
 deleted = [x[table._id.name] for x in db(query).select(table._id)]
 File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2250, in select
 return adapter.select(self.query, fields, attributes)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/sqlite.py", line 82, in select
 return super(SQLite, self).select(query, fields, attributes)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 757, in select
 colnames, sql = self._select_wcols(query, fields, **attributes)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 672, in _select_wcols
 query = self.expand(query, query_env=query_env)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 434, in _expand
 rv = op(first, second, **optional_args)
 File "/usr/local/lib/python2.7/dist-packages/pydal/dialects/base.py", line 228, in _and
 self.expand(second, query_env=query_env))
 File "/usr/local/lib/python2.7/dist-packages/pydal/dialects/__init__.py", line 96, in expand
 return self.adapter.expand(*args, **kwargs)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 453, in _expand
 return str(rv)
 File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2090, in __repr__
 return '<Set %s>' % str(self.query)
 File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 1963, in __str__
 return str(self.db._adapter.expand(self))
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 434, in _expand
 rv = op(first, second, **optional_args)
 File "/usr/local/lib/python2.7/dist-packages/pydal/dialects/base.py", line 344, in eq
 self.expand(second, first.type, query_env=query_env))
 File "/usr/local/lib/python2.7/dist-packages/pydal/dialects/__init__.py", line 96, in expand
 return self.adapter.expand(*args, **kwargs)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 444, in _expand
 rv = self.represent(expression, field_type)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 381, in represent
 return super(SQLAdapter, self).represent(obj, field_type)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 334, in represent
 obj = obj()
TypeError: id() takes exactly one argument (0 given)

Issues:

1. What could be wrong.

2. Is there a way of following this example but deleting rows upto a certain limit, say anything above 50 like in this example:

DELETE FROM post WHERE id IN(SELECT id FROM post ORDER BY id LIMIT 50 ASC)

[sandeep patel]

You can delete row this way!!

db((db.table.id==id_s)&(db.table.created_by == auth.user.id)).delete()

if you want delete rows upto a certain limit so you can you put this quarry under for loop.

Best,

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Maurice Waka]

What's id_s? Am getting error that id_s not defined 

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/LPxSiL3u0MI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Maurice Waka]

Sorry. I got it.

Regards 

On Sun, 22 Jul 2018, 14:33 sandeep patel <patelsa...@gmail.com> wrote:

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/LPxSiL3u0MI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Anthony]

On Sunday, July 22, 2018 at 7:13:13 AM UTC-4, Maurice Waka wrote:

I have tried this example but I get this error below:

db(query).delete()

Show the code for "query". Looks like it might include a Set object where a field or value is expected.

 

DELETE FROM post WHERE id IN(SELECT id FROM post ORDER BY id LIMIT 50 ASC)

Does this work?

first_fifty_posts = db(db.post)._select(db.post.id, orderby=db.post.id, limitby=(0, 50))
db(db.post.id.belongs(first_fifty_posts)).delete()

See "nested selects" at http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#belongs.

Anthony

[Maurice Waka]

This works well.

I guess I failed to clarify:  2. Is there a way of following this example but deleting rows upto a certain limit, say anything above 50 like in this example:...

Should have been retain latest inputs up a certain limit e.g. the last 50, and delete older inputs.

I got a better deal, maybe:

older = request.now - datetime.timedelta(days=1)

db(db.post.modified_on < yesterday).delete()

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/LPxSiL3u0MI/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.