UPDATE in raw sql does not hit all records although they meet the criteria

I am trying to update several records when I hit the save button in the admin with a raw sql which is located in models.py (def save(self, *args, **kwargs)

The raw sql is like this as a prototype

cursor=connection.cursor()
cursor.execute("UPDATE sales_ordered_item SET oi_delivery = %s WHERE oi_order_id = %s", ['2011-05-29', '1105212105'])

Unfortunately it does not update all records which meet the criteria. Only one and sometimes more but never all.

With the SQLite Manager and the following SQL everything works great and all the records get updated:

UPDATE sales_ordered_item
SET oi_delivery = '2011-05-29'
WHERE oi_order_id = '1105212105'

I was thinking of using a manager to update the table but I have no idea how this would work when not using static data like '2011-05-29'. Anyways, it would be great to understand in the first place how to hit all records with the raw sql. Any recommendations how to solve the problems in a different way are also highly appreciated

Here ist the code which I stripped a little to kee开发者_运维知识库p it short

# Orders of the customers
class Order(models.Model):
"""
Defines the order data incl. payment, shipping and delivery
"""
# Main Data
o_customer = models.ForeignKey(Customer, related_name='customer',
verbose_name=_u'Customer'), help_text=_(u'Please select the related Customer'))
o_id = models.CharField(_(u'Order ID'), max_length=10, primary_key=True,
                             help_text=_(u'ID has the format YYMMDDHHMM'))
o_date = models.DateField(_(u'created'))

and more...

# Order Item
class Ordered_item(models.Model):
"""
Defines the ordered item to which order it belongs, pricing is decoupled from the 
catalogue to be free of any changes in the pricing. Pricing and description is copied
from the item catalogue as a proposal and can be altered
"""
oi_order = models.ForeignKey(Order, related_name='Order', verbose_name=_(u'Order ID'))
oi_pos = models.CharField(_('Position'), max_length=2, default='01')
oi_quantity = models.PositiveIntegerField(_('Quantity'), default=1)

# Date of the delivery to determine the status of the item: ordered or already delivered
oi_delivery = models.DateField(_(u'Delivery'), null=True, blank=True)

and more ...

def save(self, *args, **kwargs):

    # does not hit all records, use static values for test purposes
    cursor=connection.cursor()
    cursor.execute("UPDATE sales_ordered_item SET oi_delivery = %s WHERE oi_order_id = %s", ['2011-05-29', '1105212105'])

    super(Ordered_item, self).save(*args, **kwargs)

This is probably happening because you are not commiting the transaction (See https://docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-sql-directly)

Add these lines after your cursor.execute:

from django.db import transaction
transaction.commit_unless_managed()

You asked for a manager method.

SalesOrderedItem.objects.filter(oi_order='1105212105').update(oi_delivery='2011-05-29')

should do the job for you!

Edit: I assume that you have two models (I am guessing this code from your raw SQL):

class OiOrder(models.Model):
    pass

class SalesOrderedItem(models.Model):
    oi_order = models.ForeignKey(OiOrder)
    oi_delivery = models.DateField()

So:

SalesOrderedItem.objects.filter(oi_order='1105212105')

gives you all SalesOrderedItem which have a oi_order of 1105212105.

   ... update(oi_delivery='2011-05-29')

The update method updates all oi_delivery attributes.