Login

Tag "mysql"

21 snippets

Snippet List

Bulk Insert - updated 5/9/2008

This Update adds requested support for self referential fields. This is useful if you need to compute and store potentially hundreds or thousands of objects and relationships quickly. To perform the inserts it will hit the database 1 plus N/100 times per affected table and where N is the number of rows to be inserted. It will use INSERT or LOAD DATA INFILE on MySQL. Run this on your test database first and make sure all of your field defaults and null values are set appropriately as you could attempt to insert a NULL where it isn't allowed and end up with a partial insert. This code is reasonably well tested and has been used for database pre-loading and operations on live sites. My test suite, however, is focused on my own use cases. Any input, i.e. failures, for creating more tests would be appreciated. Lots of Details in the Doc String. Currently only MySQL, however there is some crude skeleton code to support other databases.

  • mysql
  • bulk
  • insert
  • load-data
Read More

Django Using Stored Procedure

Here is an clean example of using stored procedure using django. It sounds pretty weird "stored procedures in django" but for legacy database system we still need a clean approach to implement stored procedures using django. In this example, I've implemented logic inside models.py by creating a dummy class, i.e a django table (which is comparable to package in your database) and inside this package/class i added stored procedure wrappers. I tested it with boulder-oracle-sprint branch, there is minor issues with LazyDate in db/backend/oracle/base.py but it still working after minor edits in base.py. It worked absolutely fine with MySQL and MSSQL. View is pretty straight forward. Dont forget to create form.html as template and in body just put {{ form }}

  • django
  • stored
  • procedures
  • oracle
  • mysql
Read More

Clean up expired django.contrib.session's in a huge MySQL InnoDB table

While django provides the `django_admin.py cleanup` script, if sessions get out of control sometimes you have to go lower level to get everything cleaned up. If the problem gets out of hand and you hit the resource limits of the machine, it is very difficult to get anything done in the database. Attached is SQL code which was used to cleanup 27GB of expired session data in 3h. Run it like this to make sure it runs to completion: `nohup mysql --user=username --password=password --host=hostname database < delete_expired_sessions.sql` nohup causes the script to run detached from a terminal, so if your session gets disconnected it will keep running.

  • mysql
  • django.contrib.session
  • expire
  • innodb
Read More

Arbitrary auto-generated primary keys

Auto-incremented primary keys are the default in Django and they are supported natively in most databases but for anything more complex things are less trivial. DB sequences are not standard, may not be available and even if they are, they are typically limited to simple integer sequence generators. This snippet bypasses the problem by allowing arbitrary auto-generated keys in Python. The implementation needs to determine whether an IntegrityError was raised because of a duplicate primary key. Unfortunately this information is not readily available, it can be found only by sniffing the DB-specific error code and string. The current version works for MySQL (5.1 at least); comments about how to determine this in other databases will be incorporated in the snippet.

  • mysql
  • sequence
  • uuid
  • auto decrement
  • auto increment
  • primary key
Read More

dumpdata/loaddata with MySQL and ForeignKeys, as django command

Based on [http://www.djangosnippets.org/snippets/662/](http://www.djangosnippets.org/snippets/662/) and updated to be runnable as custom django management command. Also added option support for --exclude=someapp --exclude=otherapp.SomeModel From original description: InnoDB tables within MySQL have no ability to defer reference checking until after a transaction is complete. This prevents most dumpdata/loaddata cycles unless the dump order falls so that referenced models are dumped before models that depend on them. Caveats 1. You use this snippet to dump the data and the built in manage.py loaddata to load the fixture output by this program. A similar solution could be applied to the XML processing on the loaddata side but this sufficed for my situations. 2. This code does not handle Circular or self-references. The loaddata for those needs to be much smarter

  • mysql
  • fixtures
  • dumpdata
  • command
Read More

Locking tables

Sometimes you need to prevent concurrent access to update/calculate some properties right. Here is (MySQL) specific example to lock one table with new object manager functions.

  • model
  • mysql
  • manager
  • table
  • lock
Read More

Models with database views

This example shows, how to use database views with django models. NewestArticle models contains 100 newest Articles. Remember, that NewestArticle model is read-only. Tested with mysql.

  • sql
  • models
  • views
  • view
  • model
  • mysql
  • database
Read More

get next mysql autoincrement value

i use this to get the pk of a record before creation, in my scenario to name an uploaded image: def UPLOADTO( i,n ): if not i.id: id = get_nextautoincrement( i.__class__ ) else: id = i.id return str(id)+'.jpg'

  • mysql
  • autoincrement
Read More

EncryptField

I was trying to create a custom field to use the mysql encrypt() function on some data I wanted to store in the DB. initcrash on IRC pointed me to [this code](https://tracpub.yaco.es/cmsutils/browser/trunk/db/fields.py?rev=66) which I butchered as best as my little brain could. Amazingly enough I got it working (thanks to a couple answers from initcrash). If anyone can clean this up that would be great. I'm also trying to figure out how I can a) create password reset link in the admin interface for this field without displaying the field or b) decrypt it so that the password field is pre-populated with the decrypted password. Otherwise it is submitting the encrypted string as a new password. Anyways, I'm only a week or two into Django with no python experience so any suggestions are very welcome. Hope this helps someone!

  • mysql
  • crypt
  • custom-field
  • encrypt()
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

dumpdata/loaddata with MySQL and ForeignKeys (Revision 2)

nnoDB tables within MySQL have no ability to defer reference checking until after a transaction is complete. This prevents most dumpdata/loaddata cycles unless the dump order falls so that referenced models are dumped before models that depend on them. This code uses Ofer Faigon's topological sort to sort the models so that any models with a ForeignKey relationship are dumped after the models they reference. class Entry(models.Model): txt = .... class Comment(models.Model): entry = models.ForeignKey(Entry) This code will ensure that Entry always gets dumped before Comment. Fixtures are an important part of the django Unit Testing framework so I really needed to be able to test my more complicated models. Caveats 1. You use this snippet to dump the data and the built in manage.py loaddata to load the fixture output by this program. A similar solution could be applied to the XML processing on the loaddata side but this sufficed for my situations. 2. This code does not handle Circular or self-references. The loaddata for those needs to be much smarter.

  • mysql
  • fixtures
  • dumpdata
Read More

Many 2 Many Admin Ordering with Mysql

My Models has a FK to translations and also a many 2 many to categories which also them are translated With this code I concatenate the translation of the categories and allow the changelist to order them. works only on mysql but you can adapt to your DB SET SESSION is required by mysql.

  • admin
  • mysql
  • m2m
  • ordering
Read More

Model Locking Mixin & Decorator (MySQL Advisory Locks)

This code provides a mixin and decorator which, when used together, can provide advisory locking on model methods. It provides locking by using MySQL's advisory lock system. See the example at the bottom of the code. This is a convenient and easy way to guarantee your model methods have exclusive access to a model instance. The LockableObjects class requires a MySQL backend, but it could be modified to use other back-end locking systems. The lock name generation in `LockableObject.get_lock_name()` could be altered to create much more complex locking schemes. Locking per-object, per-method for example.. Lock attempts have a timeout value of 45 seconds by default. If a timeout occurs, EnvironmentError is raised. **See the bottom of the script for an example** > **Instructions:** * **1:** Place the code in locking.py somewhere in your path * **2:** In your models.py (or any script with an object you want to lock): `from locking import LockableObject, require_object_lock` * **3:** In the model you want locking for, add the `LockableObject` mixin * **4:** Decorate the method you want to be exclusively locked with `@require_object_lock`

  • model
  • mysql
  • decorator
  • mixin
  • locking
Read More
Author: pio
  • 0
  • 2