Login

Tag "database"

47 snippets

Snippet List

Friendly ID(Python 3.X)

This is just modified version of [friendly id](https://djangosnippets.org/snippets/1249/) for make this script compatible with python 3.x Invoice numbers like "0000004" are a little unprofessional in that they expose how many sales a system has made, and can be used to monitor the rate of sales over a given time. They are also harder for customers to read back to you, especially if they are 10 digits long. This is simply a perfect hash function to convert an integer (from eg an ID AutoField) to a unique number. The ID is then made shorter and more user-friendly by converting to a string of letters and numbers that wont be confused for one another (in speech or text). To use it: import friendly_id class MyModel(models.Model): invoice_id = models.CharField(max_length=6, null=True, blank=True, unique=True) def save(self, *args, **kwargs): super(MyModel, self).save(*args, **kwargs) # Populate the invoice_id if it is missing if self.id and not self.invoice_id: self.invoice_id = friendly_id.encode(self.id) self.save() if self.id and not self.invoice_id When an object from this model is saved, an invoice ID will be generated that does not resemble those surrounding it. For example, where you are expecting millions of invoices the IDs generated from the AutoField primary key will be: obj.id obj.invoice_id 1 TTH9R 2 45FLU 3 6ACXD 4 8G98W 5 AQ6HF 6 DV3TY ... 9999999 J8UE5 The functions are deterministic, so running it again sometime will give the same result, and generated strings are unique for the given range (the default max is 10,000,000). Specifying a higher range allows you to have more IDs, but all the strings will then be longer. You have to decide which you need: short strings or many strings :-) This problem could have also been solved using a random invoice_id generator, but that might cause collisions which cost time to rectify, especially when a decent proportion of the available values are taken (eg 10%). Anyhow, someone else has now already written this little module for you, so now you don't have to write your own :-)

  • database
  • field-id
  • invoice-id
  • invoice
Read More

Django chunked queryset iterator

The function slices a queryset into smaller querysets containing chunk_size objects and then yield them. It is used to avoid memory error when processing huge queryset, and also database error due to that the database pulls whole table at once. Concurrent database modification wouldn't make some entries repeated or skipped in this process.

  • django
  • python
  • database
  • queryset
  • iterator
  • memoryerror
Read More

Testing for pending migrations in Django

DB migration support has been added in Django 1.7+, superseding South. More specifically, it's possible to automatically generate migrations steps when one or more changes in the application models are detected. Definitely a nice feature! I've written a small generic unit-test that one should be able to drop into the tests directory of any Django project and that checks there's no pending migrations, ie. if the models are correctly in sync with the migrations declared in the application. Handy to check nobody has forgotten to git add the migration file or that an innocent looking change in models.py doesn't need a migration step generated. Enjoy!

  • testing
  • unittest
  • database
  • migration
Read More

Database file storage

Class DatabaseStorage can be used with either FileField or ImageField. It can be used to map filenames to database blobs: so you have to use it with a **special additional table created manually**. The table should contain: *a pk-column for filenames (I think it's better to use the same type that FileField uses: nvarchar(100)) *a blob column (image type for example) *a size column (bigint type). You can't just create blob column in the same table, where you defined FileField, since there is no way to find required row in the save() method. Also size field is required to obtain better perfomance (see size() method). So you can use it with different FileFields and even with different "upload_to" variables used. Thus it implements a kind of root filesystem, where you can define dirs using "upload_to" with FileField and store any files in these dirs. Beware saving file with the same "virtual path" overwrites old file. It uses either settings.DB_FILES_URL or constructor param 'base_url' (@see __init__()) to create urls to files. Base url should be mapped to view that provides access to files (see example in the class doc-string). To store files in the same table, where FileField is defined you have to define your own field and provide extra argument (e.g. pk) to save(). Raw sql is used for all operations. In constractor or in DB_FILES of settings.py () you should specify a dictionary with db_table, fname_column, blob_column, size_column and 'base_url'. For example I just put to the settings.py the following line: DB_FILES = {'db_table': 'FILES', 'fname_column': 'FILE_NAME', 'blob_column': 'BLOB', 'size_column': 'SIZE', 'base_url': 'http://localhost/dbfiles/' }" And use it with ImageField as following: player_photo = models.ImageField(upload_to="player_photos", storage = DatabaseStorage() ) DatabaseStorage class uses your settings.py file to perform custom connection to your database. The reason to use custom connection: http://code.djangoproject.com/ticket/5135 Connection string looks like "cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')" It's based on pyodbc module, so can be used with any database supported by pyodbc. I've tested it with MS Sql Express 2005. Note: It returns special path, which should be mapped to special view, which returns requested file: **View and usage Example:** def image_view(request, filename): import os from django.http import HttpResponse from django.conf import settings from django.utils._os import safe_join from filestorage import DatabaseStorage from django.core.exceptions import ObjectDoesNotExist storage = DatabaseStorage() try: image_file = storage.open(filename, 'rb') file_content = image_file.read() except: filename = 'no_image.gif' path = safe_join(os.path.abspath(settings.MEDIA_ROOT), filename) if not os.path.exists(path): raise ObjectDoesNotExist no_image = open(path, 'rb') file_content = no_image.read() response = HttpResponse(file_content, mimetype="image/jpeg") response['Content-Disposition'] = 'inline; filename=%s'%filename return response **Warning:** *If filename exist, blob will be overwritten, to change this remove get_available_name(self, name), so Storage.get_available_name(self, name) will be used to generate new filename.* For more information see docstrings in the code. Please, drop me a line if you've found a mistake or have a suggestion :)

  • files
  • database
  • storage
  • filestorage
Read More

Manager for multiple database connections

A Django model manager capable of using different database connections. Inspired by: * [Eric Florenzano](http://www.eflorenzano.com/blog/post/easy-multi-database-support-django/) * [Kenneth Falck](http://kfalck.net/2009/07/01/multiple-databases-and-sharding-with-django) There's a more detailed version in Portuguese in my blog: [Manager para diferentes conexões de banco no Django](http://ricobl.wordpress.com/2009/08/06/manager-para-diferentes-conexoes-de-banco-no-django/)

  • multiple
  • manager
  • database
  • connection
  • databases
  • connections
Read More
Author: rix
  • 0
  • 6

Multi-DB Reconnecting Persistent Postgres Connection

This is a modification of http://djangosnippets.org/snippets/1707/ that handles the database going down or PG Bouncer killing the connection. This also works in things like Twisted to make sure the connection is alive before doing a real query. Thanks @mike_tk for the original post! EDIT: Updated the wrapper to handle multi-db. Before it was using the first connection it made, now it creates an attribute name for the connection based on the name of the database.

  • database
  • multi-db
  • twisted
  • connection
  • persistent
  • multiple-databases
  • socket
  • web-socket
Read More

Validation for full e-mails (e.g. "Joe Hacker <[email protected]>")

Out of the box, Django e-mail fields for both database models and forms only accept plain e-mail addresses. For example, `[email protected]` is accepted. On the other hand, full e-mail addresses which include a human-readable name, for example the following address fails validation in Django: Joe Hacker <[email protected]> This package adds support for validating full e-mail addresses. **Database model example** from django import models from full_email.models import FullEmailField class MyModel(models.Model): email = FullEmailField() **Forms example** from django import forms from full_email.formfields import FullEmailField class MyForm(forms.Form): email = FullEmailField(label='E-mail address') I maintain this code in a [GitHub gist](https://gist.github.com/1505228). It includes some unit tests as well.

  • forms
  • model
  • email
  • validation
  • orm
  • database
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

Database backup with admin command

Detect type of database (MySQL, PostgreSQL or SQLite) and make backup. In this moment ONLY WORK in GNU/Linux, NOT WIN.

  • database
  • admin-actions
  • backup
  • MySQL
  • admin-command
  • SQLite
  • PostgreSQL
Read More
Author: jhg
  • 1
  • 3

Email queue in DB

This is what I use to send simple status emails from my sites. Instead of a django.core.mail.send_mail call, which can take an irrritatingly, nondeterministically long time to return (regardless of error state), you can stow the emails in the database and rely on a separate interpreter process send them off (using a per-minute cron job or what have you). You then also have a record of everything you've sent via email from your code without having to configure your outbound SMTP server to store them or anything like that. Usage notes are in the docstring; share and enjoy.

  • django
  • python
  • email
  • mail
  • database
  • queue
  • asynchronous
Read More

MySQL "Text" Type Model Field

Custom field for using MySQL's `text` type. `text` is more compact than the `longtext` field that Django assigns for `models.TextField` (2^16 vs. 2^32, respectively)

  • text
  • models
  • mysql
  • db
  • database
  • field
  • custom-field
Read More

Transparent encryption for model fields

Here's a simple way to transparently encrypt a field (just repeat the idiom for more than one) so that it is not stored plaintext in the database. This might be useful for social security numbers, etc. The storage size for the ciphertext depends on which algorithm you use. Blowfish here requires 32 characters to store an encrypted 16 characters. Note also that Blowfish requires a block size of a multiple of 8, so that is what the repeat in the _set_ssn() is all about. The Crypto module is from http://www.amk.ca/python/code/crypto.html I make no claims as to how secure this scheme is overall, comments on this are welcome.

  • database
  • encryption
Read More