redshift

In this post I’ll describe how to create an ajax search page using Django

I’ve really liked using SGJR – “server generated javascript responses” in rails and wanted something as easy in Django.

Here’s the pattern we’re developing

CLIENT: load html page with minimal content
CLIENT: once page is loaded: make and ajax request, showing spinner
SERVER: perform query from search parameters
SERVER: return a html table from the data
CLIENT: replace the html in a div with that returned
CLIENT: stop spinner

model

	class System(models.Model):
	system_id = models.IntegerField(primary_key=True)
	name = models.CharField(max_length=200)
	comments = models.TextField()
	# ASSOCIATIONS
	site = models.ForeignKey(Site,
	on_delete=models.SET_NULL,
	null=True)

view raw

django_ajax_search_models.py

hosted with ❤ by GitHub

search form

	<form id='system_search_form' action="{% url 'metadata:_systems_search' %}" method="post" accept-charset="utf-8">
	{% csrf_token %}
	<div class="row">
	<div class="col-12">
	<h3>SYSTEMS SEARCH</h3>
	</div>
	<div class="col">
	<label>Name</label><br />
	<input name="name" type="input" class="form-control" />
	</div>
	<div class="col">
	<label>Comments</label><br />
	<input name="comments" type="input" class="form-control" />
	</div>
	<div class="col-2 push-right">
	<label class="">Search</label><br />
	<input id='system_search_form_btn' class='btn btn-primary' type="submit" />
	</div>
	</div>
	</form>

view raw

django_ajax_search_form.html

hosted with ❤ by GitHub

initial table div

	<div id="systems_search_table">
	<div class="alert alert-info text-center" role="alert">
	Click search to find Systems.
	</div>
	</div>

view raw

django_ajax_search_table_div_initial.html

hosted with ❤ by GitHub

the form to submit search criteria

A few things to note:

beforeSend() – perform any pre-request items., i’m starting a full-page css only spinner.
always() – stopping that spinner regardless of request result
done() – this is where the magic happens by replacing the html contents of the div with response, also html.
$(‘input#system_search_form_btn‘).click(); – this is to submit the form after the page loads. This results is a fast initial page load since your only loading a static page.

	<script>
	$(document).ready(function() {
	$('input#system_search_form_btn').on('click', function(e) {
	// submits related form
	});
	$('form#system_search_form').on('submit', function(e) {
	e.preventDefault();
	$.ajax({
	type: $(this).attr('method'),
	url: $(this).attr('action'),
	data: $(this).serialize(),
	beforeSend: function( xhr ) {
	$('#loading').addClass('loading');
	}
	}).done(function(response) {
	$( '#systems_search_table' ).html( response );
	}).fail(function(response) {
	}).always(function() {
	$('#loading').removeClass('loading');
	});
	});
	$('input#system_search_form_btn').click();
	});
	</script>

view raw

django_ajax_search_form_ajax.html

hosted with ❤ by GitHub

view that receives ajax request

I’ve left out pagination, it’s coming next. Basically, this uses the inputs from the form and if there are no results uses a generic template saying something to the affect of “there are no search results, try different search criteria”

	@csrf_protect
	def systems_search(request):
	name = request.POST['name']
	comments = request.POST['comments']
	systems = System.objects.filter(name__icontains=name, comments__icontains=comments)[:10]
	# systems = System.objects.all()[:5]
	template = 'no_search_results.html'
	context = None
	if systems.count() > 0:
	template = 'systems/_search.html'
	context = {
	"systems": systems
	}
	# https://docs.djangoproject.com/en/2.1/ref/template-response/
	return render(request, template, context)

view raw

django_ajax_search_request_view.py

hosted with ❤ by GitHub

template rendered in ajax response

	{% block content %}
	<table class="table table-striped table-bordered table-condensed table-hover">
	<thead>
	<tr>
	<th class="">Id</th>
	<th class="">Name</th>
	<th class="">Site</th>
	</thead>
	<tbody>
	{% for system in systems %}
	<tr>
	<td>{{ system.system_id }}</td>
	<td>{{ system.name }}</td>
	<td>{{ system.site.site_name }}</td>
	</tr>
	{% endfor %}
	</tbody>
	</table>
	{% endblock content %}

view raw

django_ajax_search_request_template.py

hosted with ❤ by GitHub

In this post I’ll describe my approach for connecting to multiple databases in a Django app using both PostgresSQL and AWS Redshift. The approach is basically the same used described in the Django docs “Multiple databases, using routers”

The reason for having multiple databases is the following:

Use a Postgres database for tables supporting the Django framework.
Use a Redshift database for time-series data and related meta-data.

For example, I didn’t want the tables supporting auth, admin, migrations etc in redshift:

>\dt
                      List of relations
 Schema |            Name            | Type  | 
--------+----------------------------+-------+-
 public | auth_group                 | table | 
 public | auth_group_permissions     | table | 
 public | auth_permission            | table | 
 public | auth_user                  | table | 
 public | auth_user_groups           | table | 
 public | auth_user_user_permissions | table | 
 public | django_admin_log           | table | 
 public | django_content_type        | table | 
 public | django_migrations          | table | 
 public | django_session             | table | 
(10 rows)

Side note, I’m not using Django ORM over the big data tables, only on smaller ‘meta-data’ tables that have a distribution style set to all.

Database configuration – app/settings.py

Since we are using docker I’m storing the environmental variables below in .env at the root of my project.

DATABASES = {
 'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': os.environ.get('DB_NAME'),
    'USER': os.environ.get('DB_USER'),
    'PASSWORD': os.environ.get('DB_PWD'),
    'HOST': os.environ.get('DB_HOST'),
    'PORT': os.environ.get('DB_PORT'),
  },
  'redshift': {
    'ENGINE': 'django_redshift_backend',
    'NAME': os.environ.get('REDSHIFT_NAME'),
    'USER': os.environ.get('REDSHIFT_USER'),
    'PASSWORD': os.environ.get('REDSHIFT_PWD'),
    'HOST': os.environ.get('REDSHIFT_HOST'),
    'PORT': os.environ.get('REDSHIFT_PORT'),
    # Disabled CURSORS is needed for associations in RedShift
    'DISABLE_SERVER_SIDE_CURSORS': True,
} }

Using app-label

What’s nice about this approach is when you create a new model you only need to modify your models.py file, adding app_label the model’s meta.

Models

The quick read is use Model Meta.app_label as described here https://docs.djangoproject.com/en/1.7/topics/db/multi-db/#an-example

class System(models.Model):
   
    class Meta:
        db_table = 'systems'
        app_label = 'metadata'

Database Router

class RedshiftDBRouter(object):
    def __init__(self):
        """ Initializing a square """
        self.__redshift_tables = ['systems',
                                  'sites',
                                  ]

    def db_for_read(self, model, **hints):
        db = 'default'
        if self.is_redshift(model._meta.app_label):
            db = 'redshift'
        return db

    def db_for_write(self, model, **hints):
        db = 'default'
        if self.is_redshift(model._meta.app_label):
            db = 'redshift'
        return db

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
            Only run migrations for non metadata
        """
        db = None
        if not self.is_redshift(app_label):
            db = 'default'
        return db

    def is_redshift(self, app_label):
        flag = False
        if app_label == 'metadata':
            flag = True
        return flag

tech brown bags

stuff your face and learn something at the same time!

django – searching with ajax