Best way to batch select unique ids out of pool for sequences

[James P]

I have what is essentially a table which is a pool of available codes/sequences for unique keys when I create records elsewhere in the DB.

Right now I run a transaction where I might grab 5000 codes out of an available pool of 1 billion codes using the slice operator [:code_count] where code_count == 5000.

This works fine, but then for every insert, I have to run through each code and insert it into the record manually when I use the code.

Is there a better way?

Example code (omitting other attributes for each new_item that are similar to all new_items):

code_count=5000
pool_cds = CodePool.objects.filter(free_indicator=True)[:code_count]


        for pool_cd in pool_cds:
           
            new_item = Item.objects.create(
                pool_cd = pool_cd.unique_code,
            )
            new_item.save()

[James Schneider]

You probably want to look at bulk_create and do all of the inserts as a single query:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#bulk-create

This will probably be seconds, if not minutes faster.

-James

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/ddb2de4d-d094-4512-9ad7-acd8bb33e627%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[James P]

I forgot to include the code where I set the pool codes to NOTFREE, here is the whole snippet.

code_count=5000
pool_cds = CodePool.objects.filter(free_indicator=True)[:code_count]


        for pool_cd in pool_cds:
           
            new_item = Item.objects.create(
                pool_cd=pool_cd.unique_code,
            )
            new_item.save()

           
           
 cursor = connection.cursor()
        update_sql = 'update CodePool set free_ind=%s where pool_cd.id in %s'
        instance_param = ()
       
        #Create ridiculously long list of params (5000 items)
        for pool_cd in pool_cds:
            instance_param = instance_param + (pool_cd.id,)
        params = [False, instance_param]
        rows = cursor.execute(update_sql, params)

[James P]

The issue is that a bulk create doesn't allow me to use my unique values for each create which I need from the code pool. Does it?

On Thursday, September 4, 2014 10:42:52 AM UTC-6, James Schneider wrote:

You probably want to look at bulk_create and do all of the inserts as a single query:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#bulk-create

This will probably be seconds, if not minutes faster.

-James

On Thursday, September 4, 2014, James P <testd...@gmail.com> wrote:

I have what is essentially a table which is a pool of available codes/sequences for unique keys when I create records elsewhere in the DB.

Right now I run a transaction where I might grab 5000 codes out of an available pool of 1 billion codes using the slice operator [:code_count] where code_count == 5000.

This works fine, but then for every insert, I have to run through each code and insert it into the record manually when I use the code.

Is there a better way?

Example code (omitting other attributes for each new_item that are similar to all new_items):

code_count=5000
pool_cds = CodePool.objects.filter(free_indicator=True)[:code_count]


        for pool_cd in pool_cds:
           
            new_item = Item.objects.create(
                pool_cd = pool_cd.unique_code,
            )
            new_item.save()

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

To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

[James Schneider]

I believe it does, all you do is provide a list of lazily populated models (as if you were going to create them one by one) and give it to bulk_create. This SO post has a pretty good write up in the accepted answer, along with a few other options that may save you some time:

 

http://stackoverflow.com/questions/1136106/what-is-an-efficent-way-of-inserting-thousands-of-records-into-an-sqlite-table-u

 

-James (S.)

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/ddb2de4d-d094-4512-9ad7-acd8bb33e627%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

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

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/ba988e81-1d87-4d27-a250-23dd7d01b400%40googlegroups.com.

[Simon Charette]

The following should do:

Item.objects.bulk_create([
    Item(pool_cd=pool_cd.unique_code)
    for pool_cd in pool_cds
])