Exporting and Importing An Individual MySQL Table

In moving databases from development to production it is sometimes necessary to export individual tables so that they can be imported into another database.

Exporting the Table
To export the table run the following command from the command line:
“mysqldump -p – –user=username dbname tableName > tableName.sql”

This will export the tableName to the file tableName.sql.
[NOTE: there should be no space between the two dashes, but I have to write it that way so that it display properly].

Importing the Table
To import the table run the following command from the command line:
mysql -u username -p -D dbname < tableName.sql

The path to the tableName.sql needs to be prepended with the absolute path to that file. At this point the table will be imported into the DB and you are ready to go!

I ran into this issue when attempting to add new tables to my database. I am unable to run the “LOAD DATA INFILE” command, that I had previously used to populate tables, because Webfaction does not give the permission to run the command. Therefore the simplest solution was to export a table from the MySQL database on my personal machine and then import it to the database on the Webfaction server, using the export/import commands seen above.

Hope this helps someone out with exporting individual tables and as always if any clarification is needed or I missed something feel free to let me know.

–Steve

Django – Going from development to production

I recently took my 4th year Systems Design Engineering project, which is a Django web application, from the development environment into a production environment and had quite a bit of difficulty, that could have been easily avoided if I had a tutorial to follow!

1. Hosting & Domain:
   The first thing is to either build a web host or purchase hosting from a company. I purchased hosting form Webfaction as they had a one click deployment for a Django application.  A domain name should also be purchased, I bought mine from GoDaddy for 10$.
2. Getting Setup on Webfaction:
   Webfaction takes a little while to setup your account~2-3 hours, when it is complete you will receive an e-mail and you can then login to their control panel. There are some resources that Webfaction offer to help you get setup, the first is a screencast and the other is a text guide.
3. Setting up the Database
   Once you are in the control panel area there are a few things you’ll want to do. Firstly you will need to add a database (if you have not done so), this can be done by clicking add new databse from the ‘Databases’ drop down menu.
4. Adding your domain name
   Under domains/websites select the domain link. Click on the add new button, and then enter your domain, for this tutorial we will assume it is ‘mysite.com’. When you add the domain be sure to enter in the subdomains field ‘www’ .
5. Setting up the application to serve the static media
   The next thing you will want to do is to add Static/CGI/PHP application to your website to serve the static media files (this is highly recommended). To do so go to the applications link under the domains/websites tab. Next click the add new button, name the application ‘media’ and select the app type as ‘Static/CGI/PHP’ and finally click the create button. Now you have to add the application to your domain, to do so click on the ‘websites’ link, and click the edit button for your website. Under subdomains select any that you would like to include, and then under site-apps ensure that you select the media app and set the url/path to ‘/media’. Click update, and you should be taken to the page as seen here:
   

   After site applications have been configured

6. Changes to your settings.py file:
   There are a few changes that need to be made to the settings.py file:
   DEBUG = False MEDIA_ROOT = ‘/home/username/webapps/media/
   MEDIA_URL= ‘/media/’

   Replace ‘username’ with the username that you selected for your webfaction account, now you are ready to upload your site. You will also need to change your database settings such that they match the new database created by webfaction.

7. Uploading your web applicaion
   To upload your web application, you will need to upload your ‘mysite’ application to the webfaction server. To do so you will need to download putty (an ssh tool) so that you can ssh into the host, the connection info as well as the username and password are provided by webfaction. Once you are logged in navigate to ‘/home/username/webapps/django/’ and replace the ‘myproject’ folder with your web application folder that you used for development. Once it has been uploaded you will need to tell apache where to locate the project, so naviagte to ‘/home/username/webapps/django/apache2/conf/’  and edit the httpd.conf file. You will need to change th PythonPath and SetEnvPythonPath “[‘/home/username/webapps/django’, ‘/home/username/webapps/django/lib/python2.5′] + sys.path”
   SetEnv DJANGO_SETTINGS_MODULE  sitename.settings

   To setup your static media you will need to and another few lines of code to the same httpd.conf file:

   <Location “/media”>
   SetHandler None
   </Location>

   Save the file, and thats all you need to do in the apache folder. One last thing you will need to do is put your existing media into the media directory so that your static files are properly served by apache:
   /home/username/webapps/media

8. Setting up the Database :
   The first thing you will need to do is to get a dump of your current sql database, I personally used mysql, and got a dump with the following line of code:   mysqldump -u [username] -p [password] [databasename] > [backupfile.sql] . Now you need to navigate to your project on the webfaction host, /home/username/webapps/django/mysite/ and enter the db shell by entering: python2.5 manage.py dbshell . You should see a mysql command line promp where you can upload your mysql dump: mysql – u user_name -p your_password database_name < file_name.sql. Now  you have your database loaded up and you are ready to go.
9. Firing it Up:
   Everything is ready to go, the last step is to fire up your apache server, to so navigate to /home/username/webapps/django/apache2/ and enter the command bin/stop to stop it in case it was running, and then enter bin/start to start it up.
10. Pointing the domain name to the webfaction name servers:
    There actualy is one last thing to do, and that is on the domain side of things. You will have to go to the site where you purchased your domain name, and then change the nameservers to those at webfaction, which are as follows:
    ns1.webfaction.com
    ns2.webfaction.com
    ns3.webfaction.com
    ns4.webfaction.com
    Now you can navigate to http://mysite.com and it should come up, congrats your site is live!

There you have it, from development to production. Also remember if you make any changes to the settings.py file you will have to stop and then restart the apache server.  Hopefully this quick tutorial will help you get up and going, and if you have any other questions feel free to ask me!

–Steve

4th Year Project: Scraping Pages!

The first step to my project was to get the main information that I was going to display on my web page. As mentioned in the previous post, the two pages I want information from are the schedule of classes and the undergraduate calendar. I used Ruby to accomplish this, if you want more info on scraping with Ruby drop me a comment.

The information I needed to pull from the schedule of classes was the basics for each class including the department, the class name and number, the enrollment cap and total, what day/time it was schedule for and the instructor.  A quick inspection of the page demonstrated that it was very poorly design, no information had tagged IDs or anything useful in that way. After a little digging and with the help of this command:

ie.show_tables

I was able to ascertain that the schedule of classes page had 2 tables, with the second table containing ALL of the desired information on the page; now if this isn’t bad web design I don’t know what is!

Alright I was getting somewhere, the information is in a table, now it was just a matter of finding the right logic to catch the information I wanted. Again after a little investigation I found that the department name would appear in the first column of a given row, and after I caught the department name there was a concrete pattern to where the rest of the information I wanted was placed. The logic I ended up using (and excuse the lack of indenting, wordpress is being a pain) was:

size.times do

if(classArray[count][0] == dept)

Write desired information to an excel form (for the time being)

end

count = count+1 #Increase count so that it checks the next row

end

So this basically iterates through each department page and pulls down all of the information I need! I apply very similar logic to pull down all of the information from the Undergraduate calendar. As things stand I have all of the current information available to students in an excel sheet, this is only temporary, I will shortly be putting it in to a MySQL database so that I could start putting it with the Django framework!

As far as next steps, well that would be to start learning Python/Django and getting a development environment up to play with, should be fun!

–Steve