Archive
Tags
android (3)
ant (2)
beautifulsoup (1)
debian (1)
decorators (1)
django (9)
dovecot (1)
encryption (1)
fix (4)
gotcha (2)
hobo (1)
htmlparser (1)
imaplib (2)
java (1)
json (2)
kerberos (2)
linux (7)
lxml (5)
markdown (4)
mechanize (6)
multiprocessing (1)
mysql (2)
nagios (2)
new_features (3)
open_source (5)
optparse (2)
parsing (1)
perl (2)
postgres (1)
preseed (1)
pxe (4)
pyqt4 (1)
python (41)
raid (1)
rails (1)
red_hat (1)
reportlab (4)
request_tracker (2)
rt (2)
ruby (1)
scala (1)
screen_scraping (7)
shell_scripting (8)
soap (1)
solaris (3)
sql (2)
sqlalchemy (2)
tips_and_tricks (1)
twitter (2)
ubuntu (1)
vmware (2)
windows (1)
zimbra (2)

Want to have fun? Try migrating an existing web application between different database technologies! With Django and SQLAlchemy, it actually isn't that hard! I used the following procedure to migrate both deathcat.org and this blog to Postgres. I'm assuming your know you to use Postgres and you are doing this as a Postgres superuser. All of this assumes ident authentication for Postgres, but should be easily tweaked for other configurations.

Make a directory in your Django application to store these scripts, like scripts/. Make sure this directory resides at the same level as manage.py. Now, get the code for my SQLAlchemy table copier and put it in a new file called puller.py. Comment out the line that reads table.metadata.create_all(dengine).

Now put this in a file called migrate2pg.sh:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#!/bin/bash

database=my_django_db
mysql_user=django_user
mysql_pass=django_passwd
mysql_connection_string="mysql://$mysql_user:$mysql_pass@localhost/$database?charset=utf8"
postgres_connection_string="postgres:///$database"

tables=$(echo 'show tables' | mysql -u $mysql_user -p"$mysql_pass" $database | xargs echo | cut -d ' ' -f 2-)

echo $tables

echo "Dropping old postgres database, if any"
dropdb $database

echo "Creating new database"
createdb $database

echo "Setting up Django schema"
../manage.py syncdb --noinput

echo "Removing initial data"
echo 'DELETE FROM auth_permission' | psql $database
echo 'DELETE FROM django_content_type' | psql $database

echo "Importing data from MySQL"
python puller.py \
    -f $mysql_connection_string \
    -t $postgres_connection_string \
    $tables

echo "Fixing sequences"
for table in $tables
do
    echo Fixing "${table}'s sequence"
    echo "select setval('${table}_id_seq', max(id)) from ${table};" | psql $database
done

Tweak the variables at the top as necessary for your case. Run a bash migrate2pg.sh and read the messages. One error you will see is a during the Fixing sequences phase when the script attempts to fix django_session_id_seq sequence. Ignore this error.

The final part is to give permissions or ownership to the user who will be accessing the data. I'm assuming you can do this, but if you are using Postgres ident authentication and apache, then here's a helpful script for you.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/bin/bash

database='my_django_db'

echo "Granting apache rights to ${database}"
echo "GRANT ALL ON DATABASE ${database} TO apache;" | psql $database

tables=$(echo '\dt' | psql $database | awk -F '|' '/table/ {print $2}')
sequences=$(echo '\ds' | psql $database | awk -F '|' '/sequence/ {print $2}')

echo "Tables:" $tables
echo

echo "Sequences:" $sequences
echo

tablesql=$(for table in $tables; do echo "ALTER TABLE $table OWNER TO apache;"; done)
seqsql=$(for seq in $sequences; do echo "ALTER TABLE $seq OWNER TO apache;"; done)

echo "Table Alteration SQL:" $tablesql
echo
echo "Sequence Alteration SQL:" $seqsql
echo

echo $tablesql $seqsql | psql $database
Posted by Tyler Lesmann on September 4, 2009 at 16:47 and commented on 3 times
Tagged as: django mysql postgres python sql sqlalchemy

Adding tagging to your Django application is easy, but can have some complications if you have existing data. In this post, I will cover how I accomplished it on this blog.

The first thing to do is install django-tagging. I am using Django 1.0, so I have to get the code from their subversion repository. You will not have to do this once version 0.3 comes out. Once django-tagging is installed, we can add tagging to our INSTALLED_APPS in our settings.py and do a ./manage.py syncdb. That's all you have to do for basic functionality in django-tagging. You will want more than basic functionality, am I right?

The next step you will want to take is registering your models with tagging.register as shown below.

import tagging
from django.db import models

class Entry(models.Model):
    # Imagine the rest of your Entry model here
    pass

try:
    tagging.register(Entry)
except tagging.AlreadyRegistered:
    # You might wonder what is going on here.
    # This is a bug in the svn release.  For some reason, the authors' cannot handle
    # re-registering gracefully, so we have to.
    # This should be fixed in later releases, but your code will still work here after it is.
    pass

After your model is registered, you will have access to several convenience methods and attributes, like yourmodelinstance.tags. It makes handling tags in your views and templates a dream.

What about Django's awesome admin though? It is a pain in the butt with just this to attach tags to a model instance. You have go to Tagged Objects, select the tag to apply to your model instance, select the model to be tagged, and finally the id of the instance. You do not want to do that. There is a smarter and faster way. We first need to add a TagField to our model like so.

import tagging
from django.db import models
from tagging.fields import TagField

class Entry(models.Model):
    # Imagine your other model fields preceded this
    tags_string = TagField()
    # Don't name this tags as it will conflict with the tags attribute gained
    # through registering the model with tagging

try:
    tagging.register(Entry)
except tagging.AlreadyRegistered:
    pass

If this is a fresh application without real data, you are done. You may not be so lucky, like myself. You will have to alter the database table yourself because Django, as great as it is, does not do this...yet. Here is something like commands you would run to alter your table, assuming your database is called yourdb, your application is called yourapp, your model is called yourmodel, and the last field of your model before tags_string is called last_field:

ALTER TABLE yourdb.yourapp_yourmodel ADD tags_string VARCHAR(255) AFTER last_field;
UPDATE yourdb.yourapp_yourmodel SET tags_string = '';
ALTER TABLE yourdb.yourapp_yourmodel MODIFY COLUMN tags_string VARCHAR(255) NOT NULL;

You can inject this into mysql easily by running something like this:

mysql -u root -p < file_the_earlier_sql_in

Now, your table is just like a fresh one, schema-wise. Your data is the same with a blank tags_string field. Tags can be added from the model edit page in Django admin now. I will cover adding tags to your views and templates in the next post.

Posted by Tyler Lesmann on March 6, 2009 at 9:26 and commented on 3 times
Tagged as: django mysql python sql