Login

Tag "mysql"

21 snippets

Snippet List

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

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

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

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

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

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

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

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

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

dumpdata/loaddata with MySQL and ForeignKeys

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. This code uses [Ofer Faigon's](http://www.bitformation.com) 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
  • loaddata
  • foreign-key
  • fixture
  • dumpdata
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

Creating MySQL Alter table commands for Foreign Keys

When using mysql the sql that is generated by syncdb doesn't create the foreign key relationship in all cases. This code will run through a file called create_table.sql in which you store all your create sql statements ( use "python manage.py sqlall app1 app2 > create_table.sql" ) and outputs all the neccesary alter table scripts that add the foreign key. Its not 100% proof since the generated names can end up being more than 40 characters. Need to work on that. I have [written](http://vidyanand.wordpress.com/2008/06/16/is-it-a-mysql-or-django-fault/) about it a little more in detail.

  • mysql
  • foreign-keys
Read More