Login

Tag "db"

34 snippets

Snippet List

JSONField

This is a great way to pack extra data into a model object, where the structure is dynamic, and not relational. For instance, if you wanted to store a list of dictionaries. The data won't be classically searchable, but you can define pretty much any data construct you'd like, as long as it is JSON-serializable. It's especially useful in a JSON heavy application or one that deals with a lot of javascript. **Example** (models.py): from django.db import models from jsonfield import JSONField class Sequence(models.Model): name = models.CharField(maxlength=25) list = JSONField() **Example** (shell): fib = Sequence(name='Fibonacci') fib.list = [0, 1, 1, 2, 3, 5, 8] fib.save() fib = Sequence.objects.get(name='Fibonacci') fib.list.append(13) print fib.list [0, 1, 1, 2, 3, 5, 8, 13] fib.get_list_json() "[0, 1, 1, 2, 3, 5, 8, 13]" *Note:* You can only save JSON-serializable data. Also, dates will be converted to string-timestamps, because I don't really know what better to do with them. Finally, I'm not sure how to interact with forms yet, so that realm is a bit murky.

  • models
  • model
  • json
  • db
  • field
  • json-field
Read More

Filter on Multiple M2M Objects Simultaneously

This snippet should allow you to do queries not before possible using Django's ORM. It allows you to "Split" up the m2m object you are filtering on. This is best described by example: Suppose you have `Article` and `Tag`, where `Article` has a m2m relation with `Tag` (`related_name = 'tag'`). If I run: from django.db.models.query import Q Article.objects.filter(Q(tag__value = 'A') & Q(tag__value = 'B')) > # no results I would expect to get no results (there are no tags with both a value of 'A' and 'B'). However, I *would* like to somehow get a list of articles with tags meeting that criteria. Using this snippet, you can: from django.db.models.query import Q from path.to.qsplit import QSplit Article.objects.filter(QSplit(Q(tag__value = 'A')) & QSplit(Q(tag__value = 'B'))) > # articles with both tags So now they are split into different `Tag` entries. Notice how the `QSplit()` constructor takes a `Q` object---it's possible to give this any complicated Q-type expression.

  • models
  • q
  • m2m
  • db
  • orm
Read More

Ordered items in the database - alternative

Every now and then you need to have items in your database which have a specific order. As SQL does not save rows in any order, you need to take care about this for yourself. No - actually, you don't need to anymore. You can just use this file - it is designed as kind-of plug-in for the Django ORM. Usage is (due to use of meta-classes) quite simple. It is recommended to save this snippet into a separate file called `positional.py`. To use it, you only have to import `PositionalSortMixIn` from the `positional` module and inherit from it in your own, custom model (but *before* you inherit from `models.Model`, the order counts). Usage example: Add this to your `models.py` from positional import PositionalSortMixIn class MyModel(PositionalSortMixIn, models.Model): name = models.CharField(maxlength=200, unique=True) Now you need to create the database tables: `PositionalSortMixIn` will automatically add a `postition` field to your model. In your views you can use it simply with `MyModel.objects.all().order_by('position')` and you get the objects sorted by their position. Of course you can move the objects down and up, by using `move_up()`, `move_down()` etc. In case you feel you have seen this code somewhere - right, this snippet is a modified version of [snippet #245](/snippets/245/) which I made earlier. It is basically the same code but uses another approach to display the data in an ordered way. Instead of overriding the `Manager` it adds the `position` field to `Meta.ordering`. Of course, all of this is done automatically, you only need to use `YourItem.objects.all()` to get the items in an ordered way. Update: Now you can call your custom managers `object` as long as the default manager (the one that is defined first) still returns all objects. This Mix-in absolutely needs to be able to access all elements saved. In case you find any errors just write a comment, updated versions are published here from time to time as new bugs are found and fixed.

  • db
  • orm
  • database
  • plugin
  • mixin
Read More

Allow filtering and ordering by counts of related query results

I know you're thinking, *what the heck could that title mean?* I often find myself wanting to filter and order by the result of a COUNT(*) of a query using a method similar to the [entry_count example](http://www.djangoproject.com/documentation/db-api/#extra-select-none-where-none-params-none-tables-none). Writing this many times is tedious and hardcoding the table and column names made me cringe, I also wanted the counts to result from more complex queries. This is a method you can add to your custom Manager to do this easily. It's not an ideal syntax, but it's good for the amount of code required. Example: suppose we have some articles we want to filter and order by comments and visit logs to show the most popular... class ArticleManager(models.Manager): count_related = _count_related class Article(models.Model): pub_date = models.DateTimeField(auto_now_add=True) objects = ArticleManager() class Comment(models.Model): article = models.ForeignKey(Article) is_spam = models.BooleanField(default=False) class Visit(models.Model): article = models.ForeignKey(Article) referrer = models.URLField(verify_exists=False) search_query = models.CharField(maxlength=200) Notice how the ArticleManager is given the `count_related` method. Now you can find the most popular like so... Order by non-spam comments: Article.objects.count_related(Comment.objects.filter( is_spam=False)).order_by('-comment__count') Order by incoming non-search-engine links: Article.objects.count_related(Visit.objects.filter( referrer__isnull=False, search_query__isnull=True), 'links').order_by('-links') Order by total visits: Article.objects.count_related(Visit).order_by('-visit__count') Note: Doesn't work if `query` contains joins or for many-to-many relationships, but those could be made to work identically if there's demand.

  • sql
  • model
  • db
  • count
  • manager
Read More

Template tag to dump database query info

This snippet introduces two tags: `{%dbinfo%}` and `{%dbquerylist%}`. The `{%dbinfo%}` tag returns a string with the # of database queries and aggregate DB time. The `{%dbquerylist%}` tag expands to a set of <LI> elements containing the actual SQL queries executed. If `settings.TEMPLATE_DEBUG` is False, both tags return empty strings.

  • tag
  • db
  • debugging
Read More

Pickled Object Field

*Incredibly useful for storing just about anything in the database (provided it is Pickle-able, of course) when there isn't a 'proper' field for the job:* A field which can store any pickleable object in the database. It is database-agnostic, and should work with any database backend you can throw at it. Pass in any object and it will be automagically converted behind the scenes, and you never have to manually pickle or unpickle anything. Also works fine when querying. **Please note that this is supposed to be two files, one fields.py and one tests.py (if you don't care about the unit tests, just use fields.py)**

  • model
  • db
  • orm
  • database
  • pickle
  • object
  • field
  • pickled
Read More

SQLite database vacuum script

This script can be run periodically (e.g. as a nightly cronjob) to keep a SQLite database with high churn from growing unnecessarily large.

  • db
  • utility
  • sqlite
Read More
Author: pbx
  • 4
  • 8

Improved Pickled Object Field

[Based on snippet #513 by obeattie.](http://www.djangosnippets.org/snippets/513/) **Update 10/10/09:** [Further development is now occurring on GitHub, thanks to Shrubbery Software.](http://github.com/shrubberysoft/django-picklefield) Incredibly useful for storing just about anything in the database (provided it is Pickle-able, of course) when there isn't a 'proper' field for the job. `PickledObjectField` is database-agnostic, and should work with any database backend you can throw at it. You can pass in any Python object and it will automagically be converted behind the scenes. You never have to manually pickle or unpickle anything. Also works fine when querying; supports `exact`, `in`, and `isnull` lookups. It should be noted, however, that calling `QuerySet.values()` will only return the encoded data, not the original Python object. *Please note that this is supposed to be two files, one fields.py and one tests.py (if you don't care about the unit tests, just use fields.py).* This PickledObjectField has a few improvements over the one in [snippet #513](http://www.djangosnippets.org/snippets/513/). 1. This one solves the `DjangoUnicodeDecodeError` problem when saving an object containing non-ASCII data by base64 encoding the pickled output stream. This ensures that all stored data is ASCII, eliminating the problem. 2. `PickledObjectField` will now optionally use `zlib` to compress (and uncompress) pickled objects on the fly. This can be set per-field using the keyword argument "compress=True". For most items this is probably **not** worth the small performance penalty, but for Models with larger objects, it can be a real space saver. 3. You can also now specify the pickle protocol per-field, using the protocol keyword argument. The default of `2` should always work, unless you are trying to access the data from outside of the Django ORM. 4. Worked around a rare issue when using the `cPickle` and performing lookups of complex data types. In short, `cPickle` would sometimes output different streams for the same object depending on how it was referenced. This of course could cause lookups for complex objects to fail, even when a matching object exists. See the docstrings and tests for more information. 5. You can now use the `isnull` lookup and have it function as expected. A consequence of this is that by default, `PickledObjectField` has `null=True` set (you can of course pass `null=False` if you want to change that). If `null=False` is set (the default for fields), then you wouldn't be able to store a Python `None` value, since `None` values aren't pickled or encoded (this in turn is what makes the `isnull` lookup possible). 6. You can now pass in an object as the default argument for the field without it being converted to a unicode string first. If you pass in a callable though, the field will still call it. It will *not* try to pickle and encode it. 7. You can manually import `dbsafe_encode` and `dbsafe_decode` from fields.py if you want to encode and decode objects yourself. This is mostly useful for decoding values returned from calling `QuerySet.values()`, which are still encoded strings. The tests have been updated to match the added features, but if you find any bugs, please post them in the comments. My goal is to make this an error-proof implementation. **Note:** If you are trying to store other django models in the `PickledObjectField`, please see the comments for a discussion on the problems associated with doing that. The easy solution is to put django models into a list or tuple before assigning them to the `PickledObjectField`. **Update 9/2/09:** Fixed the `value_to_string` method so that serialization should now work as expected. Also added `deepcopy` back into `dbsafe_encode`, fixing #4 above, since `deepcopy` had somehow managed to remove itself. This means that lookups should once again work as expected in **all** situations. Also made the field `editable=False` by default (which I swear I already did once before!) since it is never a good idea to have a `PickledObjectField` be user editable.

  • model
  • db
  • orm
  • database
  • pickle
  • object
  • field
  • type
  • pickled
  • store
Read More

JSONField

This is a custom field that lets you easily store JSON data in one of your model fields. This is updated to work with Django 1.1. **Example: (models.py)** from django.db import models import JSONField class MyModel(models.Model): info = JSONField() ** Example: (shell)** >>> obj = MyModel.objects.all()[0] >>> type(obj.info) <type 'NoneType'> >>> obj.info = {"test": [1, 2, 3]} >>> obj.save() **[Code at GitHub](http://github.com/bradjasper/django-jsonfield/tree/master)**

  • models
  • fields
  • model
  • json
  • db
  • field
  • json-field
  • jsonfield
Read More

Manager for something like __inall

Provides the method from_related_ids, which lets you select some objects by providing a list of related ids (The huge difference to __in is that the objects have to match al of the ids, not only one) Model Example:: class Article(models.Model): text = models.TextField() tags = ManyToManyField('Tag') objects = AllInManager() Usage:: Article.objects.from_related_ids((1,2,3,4), 'tags')

  • model
  • db
  • manager
Read More

Yet another SQL debugging facility

Inspired by http://www.djangosnippets.org/snippets/159/ This context processor provides a new variable {{ sqldebug }}, which can be used as follows: {% if sqldebug %}...{% endif %} {% if sqldebug.enabled %}...{% endif %} This checks settings.SQL_DEBUG and settings.DEBUG. Both need to be True, otherwise the above will evaluate to False and sql debugging is considered to be disabled. {{ sqldebug }} This prints basic information like total number of queries and total time. {{ sqldebug.time }}, {{ sqldebug.queries.count }} Both pieces of data can be accessed manually as well. {{ sqldebug.queries }} Lists all queries as LI elements. {% for q in sqldebug.queries %} <li>{{ q.time }}: {{ q }}</li> {% endfor %} Queries can be iterated as well. The query is automatically escaped and contains <wbr> tags to improve display of long queries. You can use {{ q.sql }} to access the unmodified, raw query string. Here's a more complex example. It the snippet from: http://www.djangosnippets.org/snippets/93/ adjusted for this context processor. {% if sqldebug %} <div id="debug"> <p> {{ sqldebug.queries.count }} Quer{{ sqldebug.queries|pluralize:"y,ies" }}, {{ sqldebug.time }} seconds {% ifnotequal sql_queries|length 0 %} (<span style="cursor: pointer;" onclick="var s=document.getElementById('debugQueryTable').style;s.display=s.display=='none'?'':'none';this.innerHTML=this.innerHTML=='Show'?'Hide':'Show';">Show</span>) {% endifnotequal %} </p> <table id="debugQueryTable" style="display: none;"> <col width="1"></col> <col></col> <col width="1"></col> <thead> <tr> <th scope="col">#</th> <th scope="col">SQL</th> <th scope="col">Time</th> </tr> </thead> <tbody> {% for query in sqldebug.queries %}<tr class="{% cycle odd,even %}"> <td>{{ forloop.counter }}</td> <td>{{ query }}</td> <td>{{ query.time }}</td> </tr>{% endfor %} </tbody> </table> </div> {% endif %}

  • sql
  • debug
  • queries
  • db
  • database
  • contextprocessor
Read More

auto image field w/ prepopulate_from & default

Given such code: class ProductImage(models.Model): fullsize = models.ImageField(upload_to= "products/%Y/%m/%d/") display = AutoImageField(upload_to= "products/%Y/%m/%d/",prepopulate_from='fullsize', size=(300, 300)) thumbnail = AutoImageField(upload_to="products/%Y/%m/%d/",null=True,default='/media/noimage.jpg')) display will be automatically resized from fullsize, and thumbnail will default to /media/noimage.jpg if no image is uploaded Note: At some point the code broke against trunk, which has now been updated to work properly

  • image
  • models
  • db
  • field
Read More

freshdb management command

This is useful especially during the model-creation stage, when things are in constant flux. The `freshdb` command will drop the project's database, then create a new one. A common use case: manage.py freshdb manage.py syncdb

  • db
  • database
  • management
Read More

Another means of updating a subset of a model's fields

Based on the UPDATE query section of `Model.save()`, this is another means of limiting the fields which are used in an UPDATE statement and bypassing the check for object existence which is made when you use `Model.save()`. Just make whatever changes you want to your model instance and call `update`, passing your instance and the names of any fields to be updated. Usage example: import datetime from forum.models import Topic from forum.utils.models import update topic = Topic.objects.get(pk=1) topic.post_count += 1 topic.last_post_at = datetime.datetime.now() update(topic, 'post_count', 'last_post_at') (Originally intended as a comment on [Snippet 479](/snippets/479/), but comments aren't working for me)

  • model
  • db
  • update
Read More