Mysql

Django Dynamic ModelForm Using Closure by Eric on February 1, 2020

At my job we are currently undergoing a total rewrite of their old web-based data entry system.  It's written in Django and works pretty-alright we are just going to make it better.  When it was first written the company was small and very few tests were being done.  This caused the initial design to lack foresight into the growth of the project.  Another problem with this current system is that it was an almost direct port of their old Microsoft Access version they had been running before.  The company is international and a web based system is much better and easier for the employees to use but wasn't designed with growth in mind. With a better understanding of how the systemis being used and what the users wanted it to do we have been in the beginning phases of development for about three weeks now.  Parts of the site are ready to go live within a week but the major parts are left. One obstacle that was brought up by one of the software engineers today was the idea of creating ...

Django works wonderfully as a web framework but sufferes from sub-par data migration.  Luckily for all of us one of their developers has created an application called South that fixes this very problem.  South is meant to bring data migrations to Django and make it easy to make schema changes that are quickly applied to your database without much effort on your part.  Django does have a few built in features for dropping tables, creating tables, and will give you the code to enter to create the tables but it doesn't help much when you are working on a production server that you don't want to simply drop and readd tables all the time. To get started just go through the installation tutorial.  Once you've done that you're ready to migrate your data!  This is best done from the very creation of the project so South can help you from the beginning.  If you are starting a new project go here and follow along.  I'm going to focus a bit more on converting an already created project and ma...

Backup and Restore MySQL Databases and Tables by Eric on February 1, 2020

Anytime I need to move data or alter our database I get a little bit nervous that something is going to go wrong and crash the database losing all of our data, dropping tables, causing chaos, and so many phone calls.  Our database automatically backs up every so often but right before any major changes I make a quick local copy of the data.  This came in handy just the other day when a table was inadvertently dropped and I had a backup on hand.  Here's how to do it using console commands.Making a Backup of a Databasemysqldump -u [username] -p --host=[hostname] [database_name] > [output_name].sqlRestoring a Database from a Backup Make sure the database is created before restoring.mysql -u [username] -p --host=[hostname] [database_name] < [output_name].sqlMaking a Backup of a Table in a Databasemysqldump -u [username] -p --host=[hostname] [database_name] [table_name] > [table_name].sqlRestoring a Table from a Backup Make sure the table has been created already and MySQL will know wher...

MySQL Queries for Grouping Data By Year by Eric on February 1, 2020

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 ...

A Visual Guide to SQL Joins by Eric on February 1, 2020

Your SQL database can be huge.  So many tables, so much data, combining it can be hard.  Even once you've sorted out what data you want it can be a real task to visualize what joins you need so that you only grab the data you want.  The more efficient the join the better.  I found this nifty image on SQL joins that I think does a great job at showing how the various joins work and does a great job explaining how to use them. ![](/img/1m55wqo.jpg) I hope this helps someone else as I know I am always having a hard time figuring out which joins to use and when.

Custom Django Admin Filter with Foreign Keys by Eric on February 1, 2020

During our massive redesign of our internal tools I wanted to make the Django admin page more friendly for us, the software engineers and admins.  Currently when they have problems or need things changed the easiest way to do that is through raw SQL and altering the database directly.  This is inconvenient and can lead to bad data.  Django, by design, abstracts that interaction and creates a great user-interface to interact with including filters and search options. One of the strengths of this is you can define your own custom filters that will appear as normal filters on the UI.  Defining them is quite simple and you can check out the tutorial directly from Django(Search for DecadeBornListFilter).  I wanted to take the query a step further and have it look up items based on a characteristic of a parent object stored as a foreign key.  Using the double underscore (__) Django allows you to access data from that foriegn key in queries.  Following the tutorial I was able to quickyl m...

MySQL Collation Matters by Eric on February 1, 2020

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: 'OPTIONS': { 'init_command':'SE...