Login

Load customized SQL

Author:
roppert
Posted:
February 24, 2009
Language:
Python
Version:
1.0
Score:
2 (after 2 ratings)

A management.py loading customized SQL feeding it raw to the database backend.

Just put it as management.py in your app and put whatever SQL you want run after syncdb in app/sql/custom.backend_driver.sql. If the backend_driver is skipped the SQL will be loaded no matter database backend.

Since it is run after syncdb it will also be run for test.

 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
import os, sys
from django.db.models import signals
from django.db import connection, transaction
from django.conf import settings

def load_customized_sql(app, created_models, verbosity=2, **kwargs):
    app_dir = os.path.normpath(os.path.join(os.path.dirname(app.__file__),      'sql'))
    custom_files = [os.path.join(app_dir, "custom.%s.sql" % settings.           DATABASE_ENGINE),
                    os.path.join(app_dir, "custom.sql")]

    for custom_file in custom_files: 
        if os.path.exists(custom_file):
            print "Loading customized SQL for %s" % app.__name__
            fp = open(custom_file, 'U')
            cursor = connection.cursor()
            try:
                cursor.execute(fp.read().decode(settings.FILE_CHARSET))
            except Exception, e:
                sys.stderr.write("Couldn't execute custom SQL for %s" % app.    __name__)
                import traceback
                traceback.print_exc()
                transaction.rollback_unless_managed()
            else:
                transaction.commit_unless_managed()
        
signals.post_syncdb.connect(load_customized_sql)

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 10 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 10 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 6 months ago

Comments

writefaruq (on June 8, 2011):

Thanks, it works for me with some modifications as shown below:

def load_custom_sql(app,   **kwargs):
  app_dir = os.path.normpath(os.path.join(os.path.dirname(app.__file__),      'sql'))
  custom_files = os.listdir(app_dir)
  for custom_file in custom_files: 
        try:
            fp = open(os.path.join(app_dir, custom_file), 'U')
            cursor = connection.cursor()
            for line in fp.readlines():
               line = line.strip()
               cursor.execute(line.decode(settings.FILE_CHARSET))                   
        except Exception, e:
            transaction.rollback_unless_managed()
            raise
        else:
            transaction.commit_unless_managed()

#

writefaruq (on June 8, 2011):

Also add management command as below, it needs a logger. See below 

from lockfile import FileLock, AlreadyLocked, LockTimeout
from django.conf import settings
from django.core.management.base import BaseCommand
from django.db.models import  get_app

from logger import logger
from custom_sql_loader import  load_custom_sql

LOCK_WAIT_TIMEOUT  =  -1
class CustomSQLLoadException(Exception):
 pass

class Command(BaseCommand):
 help = "Insert custom SQL for ..."
 args = '<appname  ...>'

 def handle(self, appname, *args, **options):
    lock = FileLock("custom_sql_loader")
    logger.debug("Acquiring lock...")
    try:
        lock.acquire(LOCK_WAIT_TIMEOUT)
    except AlreadyLocked:
        logger.debug("Lock already in place. Quitting.")
        return
    except LockTimeout:
        logger.debug("Waiting for the lock timed out. Quitting.")
        return
    logger.debug("Lock acquired.")
    try:
        app = get_app(appname)
        load_custom_sql(app)
        logger.debug("Done")
        return
    except CustomSQLLoadException:
        logger.error("Fatal error: Custom SQL loading failed!")
         # TODO: Send an email to the admins
        raise
    finally:            
        logger.debug("Releasing lock...")
        lock .release()
        logger.debug("Lock released.")
logger.py
---------
import logging
logger= logging.getLogger('any_nam')
formatter = logging.Formatter('%(asctime)s %(levelname)s   %(message)s')
file_handler = logging.FileHandler('/tmp/debug.log')
file_handler.setLevel(logging.DEBUG)
file_handler.setFormatter(formatter)
logger.addHandler(file_handler)
logger.setLevel(logging.DEBUG)

#

Please login first before commenting.