django

All posts tagged django

One of the biggest things I’ve run into with Bootstrap are their tables which are dynamic and grow and shrink depending on content size.  For the most part this is completely desired.  Every now and then you want a bit more control over your table column widths.  This is a very simple trick that I wanted to document for those who have run into the problem of wanting more control than Bootstrap gives initially.

Say you’ve created your table as shown below.

Let’s say once you render this table the description for the test is way too big and is pushing the other columns away and making them smaller than you think they should be.  The quickest way to fix this is to simply give column sizes to your table headers.  Whatever the width of your table headers your table data will conform to that size.  Knowing this we can alter the above example to make description take up less space and give a little bit more to other columns.

Super simple.  Works great.  Give it a try.  The biggest request I’ve had, that I have yet to find an answer for, is how to stop the overflow for a table data and show ellipsis instead using Bootstrap.  It’s a simple enough procedure in HTML but Bootstrap is doing something else that doesn’t allow the overflow.  If anyone knows how to keep a table data on one line and have it show ellipsis please leave a comment.

A big request we had in designing our new system was an easier way for users to get data from the database.  With the large amounts of data that we use across dozens of tables it can be slow going to use the Django methods.  Instead they have given the ability to use and write raw SQL and use that result how you please.  You can read about using cursors here, I may do a post in the future about them but today I want to focus on the SQL we wrote and the problems we encountered.

The requested query was to get a list of customers, how much business we did with them, and break that up by year.  The year could be inputted by the user so it needed to be dynamic enough to scale.  The first way we tried was to create a temporary table for every year needed.  Each table was created from a SELECT statement that was specified for the office as well as the year.  Below is the SQL command for one of the offices and years but you can imagine we copied this code, almost verbatim, but changed the SELECT statement to get the correct data.

Once all of these tables were created we had to do a ton of LEFT JOINs against the main office to get the split of the total between the other offices.  Needless to say this ran very slowly.  Using Python to change the %s to a year a typical loop through creating the tables, gathering the data, and returning took roughly 16 seconds.  Yeah, not good.  The good part was that it automatically put everything into one line for each client and one column for every office total and annual total.

Obviously we had to cut down on the time.  By changing how we did our whole process we can cut the time down significantly without using JOINs.  Now this may not work in perfectly raw SQL because we are saving the output to a variable and then combining them.  Below is the new SQL code again changing the first one for each office.  The difference here is that instead of doing a table and SELECT for every year we are doing one SELECT for all the years and creating a row for each annual total.  So requesting data 5 years back could give a possible five rows for each customer, one for every year.

After every select we save the output to a variable named for the office.  In Python these are saved as lists of dictionaries.  In the end we combine the lists and then create an output of the combined totals.  I wrote an algorithm to loop through the giant list and adding the data to an output dictionary that had only one entry for each client.

By changing the way that we create the tables we were able to cut that time to ~5 seconds.  It’s a lot of data so it takes some time but 1/3 the last time is a big improvement. Obviously this code won’t work in just SQL but it is useful for Django cursors.  If there is a way to accomplish what we are doing, in a very fast query I would totally be up for hearing about it in the comments.  I have altered our actual MySQL statements to be more readable.  In the end we just wanted a list of clients and the total work orders done for a year as well as the total for each office for that year.  Our solution works and we are fairly happy with it.  Always room for improvement though.

Recently I tried sending a list through a form using something like <input name="my_list[]" value="some val">.  When this form is submitted all of the inputs with that name are grouped together for easy access.  In Django, one would think you could simply access the list using request.POST['my_list[]'].  This is not the case. Using that command will give you the last value.  Django says this is a feature and to get the list use request.POST.getlist('my_list[]').

Just an FYI and reminder to myself.

map-title

There’s just something about having a map of an address that is appealing to people.  Why just list the address like a mailing label when you can place a nice marker on an interactive Google Map?  That’s what I thought too when I was adding content to our customer profiles.  I’d never done this before but knew that Google has a great API for accessing their data as well as some pretty good examples to follow.  The API covers how to use Google Maps on any device but for the purposes of this post I will be focusing solely on web.

Let’s go over some ground rules for using Google Maps API and then move on to a very simple example and build upon it slightly.  To start off with, you can only make a limited number of requests to Google for maps data before you have to start paying.  For a small company or webpage this limit will not be a problem.

For-profit web sites are permitted to generate up to 25 000 map loads per day using the Google Maps JavaScript API v3. A map load is counted when a map is initialized on a web page. User interaction with a map after it has loaded (eg. panning the map, zooming the map, or changing from roadmap to satellite map) does not have any impact on usage limits.

That’s a lot of requests, 25,000, and I know I won’t ever get to that point but if you do Google’s got your back.  Say one day your site becomes an overnight success.  You’re serving tons of traffic and your host is demanding money for the increased CPU usage and storage and you have complaints piling up about site downtimes and slow server times the last thing you want is one more person, Google, coming to complain to you.

In order to ensure that sites which experience short term spikes in usage are not adversely affected by the Maps API usage limits, only sites that exceed the limits for more than 90 consecutive days are subject to the limits.

Nice.  Thanks Google.  After that you better apply for a Google Maps API Business License or purchase additional quota or deal with your high traffic problem and make it go away.  Now, onto our map example.  Google uses straight vanilla JavaScript and in my later example I change a few things to leverage JQuery.

Simple Map

simplemap

This is the example given by Google and it is fairly straight forward.  We have some basic HTML5 page with a map-canvas div.  Since div’s have an initial height of 0 the style is set to give it a height.  I had some issues when I tried this exact code and needed to modify it to use specific pixel height.  The specific pixel height is nice because it gives you straight control over how tall it will be.  Applying this with Bootstrap columns will give you a nice width and in my case a widescreen looking map.

The map has a number of options shown above.  Zoom is how zoomed in the map is, the higher the number the closer you are.  Center tells the map where to be centered and uses latitude and longitude, this can be changed dynamically and is what I will show in a second.  MapTypeId refers to the type of map and you can do ROADMAP, SATTELITE, HYBRID, or TERRAIN.  After that we create the map in the DOM and add it to our div.  That’s it!  Now this map is kind of simple and boring, right?  It only shows a specific location, no markers, and is hard-coded.  Let’s change a bit of that and make, still a basic map, but a geolocating map.

I wanted to create a map that would update to the address of the customer.  I have the address on the screen I was just going to pull it and make a request to Google to center the map on that location and place a marker.  Simple enough.

Simple Geolocating Map

newmap

JavaScript

HTML

I have used a little bit of JQuery to get the text from my address section and a little bit of Bootstrap to make everything look nice.  I also set the map-canvas to have a height of 150px, but that was personal preference.  It does need a height though or you will not see it.  Going through the JavaScript, I have set an initial starting location as well as set the zoom all the way out.  If the address is not found I want to just show a map of the world.  Create the map and then we need to geolocate the customer’s address.  Using Google’s geocoder this is easy because they have a variable named address and you can enter anything in there that you would normally enter into a Google Map’s search bar.  I simply grab all of the address, including the customer name, and go for the search.  If Google returns with an OK response we focus the map on that location, zoom in a bit, and add a marker to look nice.  I also created a URL to link to Google maps itself when the marker is clicked.  This was a request from some users that wanted to go to a larger map.

I also added a button that lets the user change the address if the customer is not found.  Google will make the attempt and if they can’t find it we let the user know that that address may be invalid and to check on it.  If it is right then they can just ignore this problem or report it to Google with the link at the bottom right of the map.

That’s it.  There are a lot of different things you can do with the Google Maps API including directions, custom markers, adding more markers, etc.  Take a look at all of the API documentation if you have further needs.  For us this was sufficient and a lot of people like it even if it is just a visually pleasing item and no one will actually use.  Comment below on how you are using the Maps API or if you have any questions on what I’ve done here.

UPDATE:  If you’d like to read more about Bootstrap compatability with Google Maps check out their Third party support page.

urls

With so many templates in a large project that all link together in creative and complicated ways it can be a pain to keep track of URLs.  Every URL is an address that can also have a number of variables associated with it.  If these need to be changed it can be very tedious to go through every file and replace the hard-coded URLs to reflect the change.  Helping with this, Django has a template feature that will look those URLs up for you and insert the relative URL, without the domain name, into your templates on render.

It’s a waste to have hard-coded URLs in your templates and if you are adhering to the DRY principle, don’t repeat yourself, you’ll not want to have the same hard-coded URL in many places in the same project.  Using the {% url %} tag you can reference a view and no matter what its URL is Django will handle it for you.

Example time:

urls.py

views.py

base.html

This application, Marketing, uses a base.html page that is extended in all sub pages.  This gives the ability to have the same menu across all pages in the app.  I also limit the links based on whether a customer has been selected or not.  Let’s focus on the url tags though.  In the menu we see there are three links; marketing home, overview, and profile.  The first url tag demonstrates the most basic use of the Django url tag.  Simply it in your href and you have a link.  Make sure to have quotations around the view you are referencing, this was an update starting with Django 1.5.

Some URLs take parameters and we will need to pass those on occasion.  This is very easy to do by putting a space after the view and then adding the parameters, in order, with spaces between them.  If you don’t want to use the default ordering of the URL you can call out the variable name in the url tag.  Changing the marketing overview link to use the named parameter it would change to {% url "marketing.views.overview" cus_id=customer.customer_number %}.  Simple enough, right?  You can even reference views used outside of the immediate app like I have done with the Profile link that moves you to the customers application.  Django will handle all of your URL needs.

Hope this clears up how to leverage this great tool from Django.  I know when I first started with Django I didn’t use this as much as I should have but I’m loving it now.

Screenshot from 2013-08-22 09:04:36

Sometimes it’s necessary to display a message to the user in a quiet and clean manner.  To do this, Django has given us the messages framework for cookie- and session-based messaging.  After a form or other input is submitted we can quickly send a short message to the user without much effort at all.  Here are all the documents related to the framework but I’m going to give you the quick version as well as some customization options and Bootstrap fixes.

The messaging framework is enabled by default when you create a new Django project so good job you’re half way there!  Now just add this bit of code to the top of any page you want messages to appear:

We have this in our base that is inherited by every page to allow the functionality to appear on the entire site.  You need to have the loop even if you only have one message because it helps clear the message storage needs to be cleared.  Now the great thing is you don’t need to return messages when you render your template it will just come with it because of the context processor involved.

To add messages to your page, in your view:

That’s it!  You just have to have the line messages.add_message(request, MESSAGE_TYPE, MESSAGE_STRING) and then use the context_instance in your render and you’re done.

Bootstrap Compatability

To use this with Bootstrap you need to leverage the message.tags dictionary that returns values for each of the message levels.  Here is our code that we use for messages in our Bootstrap site.

As you can see we surround the message in an alert class with an alert-{{ message.tags }} class and add the Bootstrap close button.  This works out of the box for all Django messages except messages.ERROR.  This is because messages.ERROR returns ‘error’ which will make the alert class alert-error which is not what Bootstrap uses, they use alert-danger.  To fix this, as well as add new message types, you need to alter your settings file by adding this variable:

The import just above the line helps to avoid circular imports.  At this point you can see that we have changed the constant for messages.ERROR to return ‘danger’ instead of ‘error’.  This fixes our Bootstrap problem but you can see that now you can add new message constants not already created like messages.AWESOME or whatever else you’d like to make.

There is a lot more to the Django messages framework including error levels that I didn’t get into here.  Check out their docs for the low down on all the different things you can do with Django messages.

Here is a screenshot of an example I did just to test things out.

Screenshot from 2013-08-22 09:09:47

MySQL Collation Matters

When creating databases and tables with MySQL there seems to be one setting that gets overlooked, collation and character set.  For most smaller sites, and maybe those purely in English, this may not be such a huge deal but for a larger application site and one that will be holding characters from various languages this is essential.

UTF-8 is the collation and character set we want and the MySQL command to create a database with these settings is CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Bringing this back to Django, when Django creates new tables within a database it will use whatever the default character set and collation is for that database.  This is why it is important to get this right at the beginning with the command above.  If you do not, however, there are ways to make future tables created have the right encoding.  Simply add these lines at the bottom of your database connection settings in your settings.py file:

I put mine right below the PORT variable in my default database connection.  This will ensure that all new tables created will have the correct collation and character set.  But what about those tables already created that need their settings changed to accommodate these special characters?  You can find out what collation the columns are using by entering this command for MySQL: SHOW FULL COLUMNS FROM my_table.  One of the columns shown is the Collation column.  Make sure this is some utf8 encoding and you’re good.  If it isn’t simply use the command ALTER TABLE my_table MODIFY column_name varchar(30) CHARACTER SET utf8; to change the column to the appropriate character set.  Make sure that the varchar(30) in the example is set appropriately to your column whether it be a text field, varchar(255), etc. make sure it is the same column it was before just with the new character set.  Run that command for every column that needs changing.  Run the SHOW FULL COLUMNS line again and make sure everything is right and that’s it!

Hope this helps someone, I know it has come up a lot in the project we are working on now and has become kind of a headache because we didn’t start the tables out correctly.