Postgresql – Sqlite – Django — Migrating postgresql to sqlite3 with pg_dump

I needed to migrate live data from my production server to my development environment which would be postgresql to sqlite.

Sqlite3 is an amazing database fort development – it would be a nightmare to get postgresql and psycopg2 working on 3 different environments (Mac, windows, cygwin), let alone one.

I’ve figured out a way to export data reliably although it is a bit of a pain. Let me document it for future reference.

Generate SQL Dump

Use the –inserts flag to make all of the statements an insert statement. This is helpful for several reasons… it won’t kill the whole import if one line fails for example. 

pg_dump --inserts db_name -t table_name > pg_dump.sql

Modify file to be sqlite compatible

Next step, open the dump, strip out the PG specific stuff before the CREATE TABLE statement.

Next, modify the CREATE TABLE statement to be compatible with sqlite.

First, make sure your primary key field is specified with PRIMARY_KEY, and AUTO_INCREMENT. This will cause headaches if you don’t have an auto incrementing key.

Example:

CREATE TABLE table_name (
    id integer PRIMARY_KEY AUTO_INCREMENT,
    ....
)

We must use the statement generated by pg_dump because the field ordering is very important – a fresh syncdb would generate fields in alphabetical order.

Next, make sure there are no PG specific functions that sqlite can’t understand — for me, the date functions.

Simply remove the special clauses on timestamp fields such as DEFAULT now()

Next, replace all instances of true with 1, and false with 0.

sed -i 's/true/1/g' pg_dump.sql
sed -i 's/false/0/g' pg_dump.sql

Destroy your table in sqlite, and start executing the file

Drop your table, since we’ll be re-creating it via reading our dump file. Then, use the .read command to start executing SQL.

DROP TABLE table_name;
.read pg_dump.sql

Wait a long time, and when it’s done you’ll have your data!

PS: A common gotcha: in sqlite commands, do NOT close with a semicolon, it will read as part of your argument.

Django — Proxy Model ContentTypes are not created

Proxy model ContentType objects are not automatically created, causing problems if you want to give users permission to add/change/delete your proxy models.

Thanks to Mark Haus for a simple fix to connect to the post_syncdb signal to generate contenttypes by name.

Source: http://dev.markhaus.com/blog/2011/04/django-setting-the-right-permissions-associated-with-proxy-models/


from django.db.models import get_models
from django.db.models.signals import post_syncdb
from django.utils.encoding import smart_unicode

from django.contrib.auth.management import create_permissions, _get_all_permissions

# Hack the postsyncdb signal, so we can fix the misbehavior of the
# content_type
# assignment to the proxy models.
# see http://code.djangoproject.com/ticket/11154

def create_permissions_respecting_proxy(
    app, created_models, verbosity, **kwargs
    ):
    if not kwargs['sender'].__name__ == 'myproject.myapp.models':
        # if not in 'customer' app, then use the original function
        create_permissions(app, created_models, verbosity, **kwargs)
        return

    from django.contrib.contenttypes.models import ContentType
    from django.contrib.auth import models as auth_app
    app_models = get_models(app)
    searched_perms = list()
    ctypes = set()
    for klass in app_models:
        # this is where the difference is: the original create_permissions
        # use ctype = ContentType.objects.get_for_model(klass)
        opts = klass._meta
        ctype, created = ContentType.objects.get_or_create(
            app_label=opts.app_label,
            model=opts.object_name.lower(),
            defaults = {'name': smart_unicode(opts.verbose_name_raw)}
            )
        # end of the modification
        ctypes.add(ctype)
        for perm in _get_all_permissions(klass._meta):
            searched_perms.append((ctype, perm))

    all_perms = set(auth_app.Permission.objects.filter(
            content_type__in=ctypes
            ).values_list("content_type", "codename"))

    for ctype, (codename, name) in searched_perms:
        if(ctype.pk, codename) in all_perms:
            continue
        p = auth_app.Permission.objects.create(
            codename=codename, name=name, content_type=ctype
            )
        if verbosity >=2:
            print "Adding permission '%s'" % p


post_syncdb.disconnect(
    create_permissions,
    dispatch_uid='django.contrib.auth.management.create_permissions',
    )

post_syncdb.connect(
    create_permissions_respecting_proxy,
    dispatch_uid='django.contrib.auth.management.create_permissions',
    )

Django — ‘int’ object is not iterable

I was led on another wild goose chase bug hunting this in an ajax function that had heavily nested try/except blocks because it hid the real line number of the error.

For me, this was a simple error caused by passing an argument to myobject.get() without a keyword.

MyModel.objects.get(3)

This makes me want to explore more robust exception handling that pulls line numbers into the error message. I’m sure it would pay for itself in time.

try:
    MyModel.objects.get(3)

except Exception, e:
    log.debug("Exception {e} thrown on line {lineno}".format(
        e=e,
        lineno=sys.exc_info[2].tb_lineno,
    )

Django — str has no attribute default_alias

Jebus, it took me a long time to debug this since it was in an AJAX call and I assumed the source was from something else.

This is an error thrown when you pass in a string to an aggregation function.

objects.annotate('field') # fails with default_alias error.
objects.annotate(Sum('field')) # success

Django — How to include links in ModelAdmin Inlines

Just like with ModelAdmins, we can include arbitrary python code in our inlines.

For a ModelAdmin, I often set up a python function that returns color coded html or processes data from the object somehow.

With Inlines, you might get an error that the field doesn’t exist in the form — the trick is to add the method you are adding as a readonly_field.

class OrderInline(admin.TabularInline):
    model = Order
    fields = ('total', 'tasting_date', '_link')
    readonly_fields = ('_link', )

    def _link(self, obj):
        return 'hello'

Cygwin — Gitk won’t start suddenly

Setting up a new environment always causes problems.

For whatever reason, gitk won’t start. It just immediately returns with no error message.

After trying many things… the fix that worked for me is to reinstall python-tkinter and tcltk.

Good luck.

Cygwin — Installing PIL on Cygwin and getting gcc: vfork: Resource temporarily unavailable.

I’m moving from mac to PC environment and converting everything the mac did to the PC.

One of the most important aspects is the unix environment.

While running my local deployment script, I ran into multiple problems. One involved running out of PIDs, solved by running the ash shell and running /bin/rebaseall.

Now, PIL is choking on the error gcc: vfork: Resource temporarily unavailable

The solution that worked for me is from this post:
http://cygwin.com/ml/cygwin/2007-01/msg00498.html

Run the following:

rebase -b 0x1000000000 /bin/tk84.dll

And your pip install PIL will work…

Magic.