0.3 Release - Part 1

Unfortunately, I did not fully accomplish my goal for release 0.3 - I was able to make progress but in the end I did not turn out the way I had hoped. I tried to get JSON output working as exampled in the BuildAPI existing scripts but could not get it going. However, I did learn a tremendous amount of information which can be seen by browsing through my blog.

In addition, I have created a quick and concise How-to document for BuildAPI in hopes that the next student that possibly takes on BuildAPI will have a head start into the project and a better understanding of its inner workings without wasting too much time figuring it out.
Furthermore, there will also be a second document that provides more information on the controllers, models and template files that will be posted later.

Documentation

Here is a link to the documentation: http://asdesigned.ca/sbr/BuildAPI-How-To-Setup.pdf
It is in PDF form (clickable links included).


Below is my Getting Started documentation in HTML format, although I'd recommend viewing the PDF version instead:

Seneca College

BuildAPI

How to Get Started with BuildAPI

Andrew Singh  12/17/2010

Table of Contents 

Introduction

Purpose

Tasks Summary

I. Install MySQL and Import Database Snapshots

II. Install Python and Python SetupTools (Easy_install)

III. Install Google Python Visualizations Library

IV. Install MySQL-Python (MySQLdb for Python)

V. Install BuildAPI Files

VI. Configure and Run BuildAPI

VII. Optional: Set up sample project files

VIII. Resources

BuildAPI 

“BuildAPI is a Pylons project used by RelEng to surface information collected from two databases updated through our buildbot masters as they run jobs.”

Introduction 

This project consists of generating analytic reports which can be used for a multitude of purposes such as performance and usage tests or discovering rogue buildbot slave machines. Moreover, it will require querying databases for information on the BuildBot jobs, which will then be used to generate the necessary reports. 

Purpose 

 To install, configure and run BuildAPI

Tasks Summary 

1. Install MySQL and import database snapshots 
2. Install Python, Python SetupTools (Easy_install) and Pylons 
3. Install Google Python Visualizations Library 
4. Install MySQL-Python (MySQLdb for Python) 
5. Install BuildAPI Files 
6. Configure and Run BuildAPI 
7. Optional: Set up sample project files 
8. Resources 

I.        Install MySQL and Import Database Snapshots

Install MySQL 

Install Mysql and Mysql Server: 

yum –y install mysql mysql-server 

Enable the service at startup: 

/sbin/chkconfig mysqld on 

Start the MySQL server 

/sbin/service mysqld start 

Set the MySQL root password 

mysqladmin -u root password 'new-password' 

The quotes around the new password are required

Root can be used to access MySQL or a new MySQL user can be created for the project.

For testing purposes, root can be used.

Install Snapshots

Download the database snapshot files, which are in bunzip2 format. Please check https://wiki.mozilla.org/ReleaseEngineering/BuildAPI for the latest snapshot releases, under “Project Requirements”.

If bunzip2 does not exist on the system, install it using: yum install bzip2

Extract the databases

bunzip2 schedulerdb-2010-10-22.sql.bz2 

bunzip2 statusdb-2010-10-22.sql.bz2 

Create the respective databases; Example: statusdb and schedulerdb in MySQL

$ mysql –uroot -ppassword

mysql>  

mysql> create database statusdb; 

mysql> create database schedulerdb; 

mysql> exit 

Import the databases into mysql server 

mysql –uroot -ppassword -hlocalhost statusdb < statusdb.sql

mysql –uroot -ppassword -hlocalhost statusdb < schedulerdb.sql

II.        Install Python and Python SetupTools (Easy_install)

Install Python 

yum install python 

Install Setup Tools 

yum install python-setuptools 

Install Pylons 

yum install pylons or easy_install Pylons

yum install python-pylons 

Virtual Environment 

We can also set up a virtual environment to work in, whereby it allows us to have multiple isolated environments for Python so it is possible to run different applications on the same system, but using different sets of installed Python packages. This is recommended by the Mozilla Release Engineering team. 

Install virtual environment using easy_install and read the readme file to activate 

easy_install virtualenv 

Manually installing virtual environment 

Use wget to get the latest package from http://pypi.python.org/packages/source/v/virtualenv/

Untar the downloaded file, create a directory and activate the environment 

$ tar zxfv filename.tar.gz 

$ virtualenv.py ~/venv/sandbox 

$ source ~/venv/sandbox/bin/activate 

Once in the sandbox, install packages as necessary, example: 

(sandbox)$ easy_install Pylons==0.9.6.1 

If you will be working within a virtual environment, it is important that you install the necessary packages below in the same environment as well.

III.        Install Google Python Visualizations Library

Download the library from http://google-visualization-python.googlecode.com/ and extract it.

To install the library: 

cd into the extracted directory and run the command:

python ./setup.py install 

Test the library by running python ./setup.py test to make sure it is installed correctly

IV.        Install MySQL-Python (MySQLdb for Python)

Install Dependencies 

This package requires the python-devel dependency or it will not install properly.

yum install python-devel 

Install Package 

Once that is complete, we can install MySQLdb.  

You can stop the mysql server before installing MySQL-Python and restart it once complete.

yum install mysql-python 

In the event that it fails, you can also try a manual installation: 

Download the source file from http://download.sourceforge.net/sourceforge/mysql-python/

Extract the downloaded file and change to its directory 

Run the commands: 

python setup.py build 

python setup.py install 

Optional: run the test to see if it is installed properly: python setup.py test

V.        Install BuildAPI Files

The easiest way to install BuildAPI is to use Python’s setup tools: 

easy_install buildapi 

This will also install SQLAlchemy which is required; otherwise it can be installed using easy_install SQLAlchemy

For manual installation, you can download the source files from http://hg.mozilla.org/build/buildapi and extract it to a directory called buildapi

This will create a directory structure similar to the following: 

------buildapi/ 

 -------MANIFEST.in

 -------README.txt

         -------docs

 -------ez_setup.py

 -------buildapi         (This is a nested directory)

 -------buildapi.egg-info

 -------setup.cfg

 -------setup.py          (used for application setup)

 -------test.ini    (used for testing, i.e. when you run python setup.py test)

Run the command: 

python setup.py install 

VI.        Configure and Run BuildAPI

Next, BuildAPI must be configured to host content on your system, either through localhost or on the internet. Moreover, it uses the Paster server for hosting and requires creating and editing a configuration file. 

We will create our own configuration file using the command: 

paster make-config buildapi config.ini 

This command produces a different configuration file with sensible options for production use

Now we can edit the created config.ini configuration file and make changes as below.

[server:main] 

use = egg:Paste#http 

host = 127.0.0.1 

port = 5000 

# SQLAlchemy database URL 

sqlalchemy.scheduler_db.url = mysql://root:root@localhost/schedulerdb 

sqlalchemy.status_db.url = mysql://root:root@localhost/statusdb 

sqlalchemy.pool_recycle = 3600 

Note: host can be set to any value such as iraq.proximity.on.ca to make it accessible on the internet provided configurations such as firewalls allow it.  The sqlalchemy database url and username:password fields as they may need editing accordingly if you have used different names or different databases.

Run BuildAPI 

To run, stop or reload BuildAPI, make sure you are in the buildapi directory (first level, where the config.ini file resides) and use the following commands

paster serve --start --daemon config.ini 

paster serve --stop --daemon config.ini 

paster serve --reload --daemon config.ini 

Whenever a change is made to the configuration file, or major changes made to controller files, the paster server must be reloaded.

Check the current directory (buildapi), there should be two files that were created:

paster.pid – contains the PID number for the Paster process

paster.log – All messages are contained in this file. Extremely useful file for troubleshooting!

Test it out! 

Depending on your config.ini configuration settings, direct the browser to  

http://localhost:5000                 or           http://domain:5000

And that’s it! BuildAPI should now be live on your system! 

VII.        Optional: Set up sample project files

These are files that were part of the BuildAPI project. They can be used to get a better understanding of BuildAPI and to understand of how it works, along with the blog posts (on http://andrewasdesigned.blogspot.com/).

Additionally, see the https://wiki.mozilla.org/ReleaseEngineering/BuildAPI page for a quick beginner tutorial as well.

File	Description	Download Location
config.ini	Paster Configuration file	http://asdesigned.ca/SBR/config.ini
hello.py	Hello World controller file – a Getting Started controller	http://asdesigned.ca/SBR/hello.py
project.py	Project Controller file	http://asdesigned.ca/SBR/project.py
query.py	Project Query file, which is the BuildAPI query file with Project queries appended	http://asdesigned.ca/SBR/query.py
project.mako	Project template file	http://asdesigned.ca/SBR/project.mako
routing.py	Project routing.py file	http://asdesigned.ca/SBR/routing.py

Directory Structure showing where files should be placed: 

buildapi/ 

+-- buildapi 

¦   +-- config        #routing.py, placed in config

¦   +-- controllers        #project.py, hello.py placed in controllers

¦   +-- model        #query.py, placed in model

¦   +-- templates        #project.mako, placed in templates

Note that other directories and files have been omitted

The Python library called Routes handles mapping URLs to controllers and their methods, or their action as Routes refers to them. By default, Pylons sets up the following routes (found in config/routing.py):

map.connect('/{controller}/{action}') 

Therefore, you can also edit the buildapi/buildapi/config/routing.py file and add the following in manually, instead of using the routing.py provided file:

map.connect('/project', controller='project', action='index') 

This file controls how the controller or project site is accessed. The above means that the site is accessed, for example, by www.domain.com/project which will automatically redirect it to the index as defined in the controller file.

See blog posts on http://andrewasdesigned.blogspot.com/ regarding how the model works.

A Word on Database and queries: 

It is important to understand the database structure and use the resources to figure out which queries and tables would provide the best indication of certain values, such as CPU usage. The queries are also done using SQLAlchemy and it can get complicated with complex queries, therefore, seek out help (See Resources section).

VIII.        Resources

It is extremely important to reach out to the community for help in any area. I cannot stress this enough. There are many resources available, such as IRC, official documentation and discussion groups that you can take advantage of. Some are listed below. 

IRC 

Server:         irc.freenode.net  

channels:         #seneca        #mozilla

Blogs and Wikis: 

http://anamariamoz.wordpress.com/

https://wiki.mozilla.org/ReleaseEngineering/BuildAPI

http://andrewasdesigned.blogspot.com/

Discussion Groups 

http://groups.google.com/group/pylons-discuss (Google Group)

Official Pylons Documentation 

http://pylonshq.com/docs/en/0.9.7/ (Pylons Documentation)

http://pylonsbook.com/ (Free Online Pylons Book and amazing resource)

SBR600 (Seneca) BuildAPI Project Page: 

http://zenit.senecac.on.ca/wiki/index.php/BuildAPI

SBR600: Reflection

Course Reflections

For this to be my final semester, I had to take 7 courses, and so I did. I figured it would be challenging and it definitely was – especially also working part-time plus other responsibilities, but sometimes you 'just gotta do what you gotta do'.

I know I’m probably not the only one as there were others that were in similar positions; and to you all out there who’s graduating: Kudos! Cheers! This chapter is done, complete or whatever your word is, either way, run and tell that, run and tell that, run and tell that, home home home homeboy (if you don’t get the reference, click here and enjoy)


Okay, on to what is important though: My reflections on the course.
The first half was interesting, and I liked the fact that we had a tentative schedule that allowed us divide and devote our time appropriately to the course.

It was good to learn about Fedora’s build and release and I enjoyed creating RPMs and going through the process of creating our own SPEC files, testing build times, signing our RPMS and testing packages on different platforms.

I have to say that was definitely one of my favorite parts of this course. If I had to do this course again (and not that I want to, lol) and choose a project I would have chosen one to rebuild packages or test builds and build times, but no worries, there was only love for BuildAPI...and some frustration. I’ll leave my thoughts on that for last.

As I mentioned in my GIT blog post I’ve used version control clients (SVN) before but never GIT. However, after playing around with it for this course, I actually used it for another course, INT620, which involved creating a website using PERL and TT2. I used GIT to keep track of changes to the website files and it worked out quite well…for the period of time I used it as we eventually switched to using Mercurial. I’m kind of ranting off topic here. Let’s move on to the projects.

It was my fault for missing the class where Chris Tyler explained each project and the respective general expectations. However, we were given a wide choice of projects to choose from that were well thought out and incorporated a little bit of everything; it definitely gains points on my recommendation scale for this course! In regards to that, if there any students who have not taken SBR600 yet, I would highly suggest that you do and also see my “Advice” section later on this post.

The second half of the course focused on developing my chosen project - BuildAPI. I must say there were difficulties along the way and these projects require our own initiative to progress. There were the tasks of learning Python, Pylons, SQLAlchemy queries, MVC model (see previous blog posts) and integrating it all. Although, I may have went too deep into it and should have been more focused on specific goals rather than trying to figure out everything in such a short time.

BuildAPI was especially challenging at the beginning, but I pushed along albeit slowly to gain results and ultimately new knowledge. However, as I worked my way through and progressed, there was a satisfying sense of accomplishment from researching, learning and putting the knowledge to practical use.

Even while working on my INT620 website project using Perl and TT2, I often reflected on how our project can also be done in pylons since it encompassed similar processes as my BuildAPI project: querying databases, displaying/managing data and using a model like the MVC to separate the application code from business.

But I also believe I learned something much greater than learning the ropes on my project. And that is the value of the resources available - the large community of able-bodied and willingly helpful souls. SBR600 opened my eyes to the “community”.


What about my advice for those who plan on taking this course?
-    Do it, take it
-    Choose your project wisely and one that you will thoroughly enjoy
      o    Some projects involve scripting – such as learning Python (which you ultimately do by yourself)
-    Get involved with the community – seek help, and do not be afraid to ask ANY question regardless of how “ridiculous” you may think it sounds
-    Get INVOLVED with the COMMUNITY, success in the course, project and perhaps your career depend on it

Lastly, Cheers and Thanks to: Chris Tyler, Armen Zambrano, Mike Orr, fellow students, IRC users cthomas and aki, blogs, Mozilla Release Engineering, others and the Google group - Pylons-Discuss for all the help!

Oh wait....I have to post 0.3 for the project! I will do that shortly!

BuildAPI: 0.2

Quick note:

Last week I posted a series of blog posts that I did not have time to post during the past week or more. They were all sort of backed up and hence all posted at once.

BuildAPI 0.2

On to the BuildAPI project: Let me just say that I have been doing this project in a step-by-step approach in order to learn what I need to learn, understand what I am working with, and apply it practically.
Also, the BuildAPI wiki page has been updated: http://zenit.senecac.on.ca/wiki/index.php/BuildAPI

To see it running: http://iraq.proximity.on.ca:5000/
Please click on Project 0.2 under BuildAPI Project Test, or http://iraq.proximity.on.ca:5000/project

0.1x - Between 0.1 and 0.2

My 0.1 release included getting BuildAPI set up and taking a peek at the extensively used MVC model.
However, since BuildAPI uses Pylons framework, I took some time to read The Definitive Guide to Pylons book and skipped to the chapters that were necessary. It's a good thing the book is available online, it is such a great resource! It helped me to learn some of the language and concepts. Next was to create very simple controllers for working with model and view templates and that was the beginning of wrapping my head around the MVC model concepts.

0.2 - Summary

For 0.2, I feel very comfortable working with the MVC model and was able to create my own controller, add to the existing BuildAPI universal model script for querying (query.py, orginal seen here), and create my own MAKO template to output my results. (See MVC scripts). In addition, I appended a link to my project to the main BuildAPI index page, which I have set up on http://iraq.proximity.on.ca:5000.

In regards to the model file, I had to read some of the documentation on SQLAlchemy and how queries work in order to pull data from the database, and from that, I now understand how to formulate basic queries using SQLAlchemy. However, I am still having a bit of trouble with complex queries such as using joins and multiple where clauses (See blog post on SQLAlchemy and Basic Queries).

I also stripped special formatting from the output and template as I did not fully understand how it works and how it is integrated with Pylons and BuildAPI - it seemed confusing. By special formatting, I am referring to Javascript/JSON and Json tables and charts.
However, after a bit of fiddling and reading on google visualizations documentation, I get the gist of how it works and I have implemented simple json using google viz api. Although, I still need to seek out help and apply it practically - which will be in 0.3.

MVC – Revisited

From my 0.1 post, I'd like to revisit the MVC model in terms of the BuildAPI project instead of a generic breakdown. I will also reference the MVC scripts which can be seen later in the post.
Here goes

• The model (query.py) knows about the databases and queries, or in other terms, business objects/data
• The template (project.mako) knows about HTML, Javascript, CSS, and the tmpl_context or "c" variables (example: c_output in controller projecy.py script) that are passed to it by the controller.
• The controller knows about both the model and the template. Therefore, it will query or modify the model to obtain data, modifies the values of the "c" variables and then passes it to the template.
• Also, the model can invoke other models, and the controller other controllers etc

After working with it, I can definitely see how each component can be updated separately without affecting the other.

MVC Scripts

The Controller - project.py

import logging

from pylons import request, response, session, tmpl_context as c, url

from pylons.controllers.util import abort, redirect

from pylons.decorators import jsonify

from buildapi.lib.base import BaseController, render

from buildapi.model.query import GetProjectQuery

from buildapi.model.query import GetQueryTest

log = logging.getLogger(__name__)

class ProjectController(BaseController):

    def index(self):

        output = GetProjectQuery()

        results = GetQueryTest()

        c.output = output

        c.results = results

        return render("/project.mako")

        return self.jsonify(output)

        # What the lines below do is to gather the format of the request using request.GET method

        #  which contains the variables in a query string

        # These variables are set in the template through javascript or through the form request

        # This is commented out for reasons (See JSON/Javascript section)

        #if 'format' in request.GET:

        #    format = request.GET.getone('format')

        #else:

        #    format = 'html'

        #if format not in ('html', 'json'):

        #    abort(400, detail='Unsupported format: %s' % format)

        #if format == "html":

            # assign to c_output

            # return template render

        #else:

           # jsonify the data                   

           # return self.jsonify(results)

The Model - query.py (My test queries appended to existing file)

def GetProjectQuery():

    rr = meta.status_db_meta.tables['builders']

    q = select([rr.c.id, rr.c.name])

    q = q.limit(50)

    query_results = q.execute()

    output = []

    for r in query_results:

        this_result = {}

        for key, value in r.items():

            this_result[str(key)] = value

        output.append(this_result)

    return output

def GetQueryTest():

    rr = meta.status_db_meta.tables['builders']

    bb = meta.status_db_meta.tables['builds']

    q = select([rr.c.name, bb.c.starttime, bb.c.endtime])

    #q = q.join(rr, bb.c.builder_id = rr.c.id)

    q = q.where(and_(rr.c.id == bb.c.builder_id))

    q = q.where(and_(rr.c.name.like('%moz%')))

    q = q.limit(50)

    query_results = q.execute()

    results = []

    for r in query_results:

        this_result = {}

        for key, value in r.items():

            this_result[str(key)] = value

        results.append(this_result)

    return results

The Template - project.mako

Only the important parts shown. This creates the table using the results from the query that are passed in as variables.

This is for the first table:

<tr>

% for key in ('Builder ID','Builder Name'):

<td><h3>${key}</h3></td>

% endfor

</tr>

<tbody>

<%

  from pytz import timezone

  from datetime import datetime

  eastern = timezone('US/Eastern')

  now = datetime.now().replace(microsecond=0)

%>

% for key in c.output:

        <tr>

        % for x in ('id', 'name'):

        <td>${key[x]}</td>

        % endfor

</tr>

% endfor

</tbody>

This is for the second table:

% for key in ('Builder Name','Start Time', 'End Time', 'Duration'):

<td><h3>${key}</h3></td>

% endfor

</tr>

% for key in c.results:

        <%

        key['duration'] = key['endtime'] - key['starttime']

        %>

        <tr>

        % for x in ('name', 'starttime', 'endtime', 'duration'):

        <td> ${key[x]} </td>

        % endfor

        </tr>

% endfor

The below is simply using Google's visualization to create the table, it can also be used to create charts. I just don't know how to loop the data into a chart/table as yet properly.

<script type="text/javascript" src="http://www.google.com/jsapi"></script>

<script type="text/javascript">

        google.load('visualization', '1', {packages: ['table']});

</script>

% for key in c.output:

        rows: [{c:[{v: '${key['id']}'}, {v: '${key['name']}'}]}]};

% endfor

<script type="text/javascript">

    function drawVisualization() {

      // Create and populate the data table.

    var JSONObject = {

      cols: [{id: 'task', label: 'Builder ID', type: 'string'},

              {id: 'hours', label: 'Builder Name', type: 'string'}],

      rows: [{c:[{v: '${key['id']}'}, {v: '${key['name']}'}]}]};

      var data = new google.visualization.DataTable(JSONObject, 0.5);

      // Create and draw the visualization.

      visualization = new google.visualization.Table(document.getElementById('table'));

      visualization.draw(data, {'allowHtml': true});

    }

    google.setOnLoadCallback(drawVisualization);

</script>

After the above script, this is all that's needed in the HTML file that displays the table:

<div id="table"></div>

Javascript, JSON and Outputting data in special formats

That being said, JSON is just another data format structure, which is used for formatting the tables and charts. However, I am trying to get JSON and javascript to work so I can integrate with existing BuildAPI structure, but honestly, it is quite a lot to learn in a short time.

Whatever it is that I am doing or learning, I like to make sure I understand why I am doing it and how it works.

I've researched that the controller can produce the json on request using several methods, one of which is by passing the format specifier. In addition, this can be done either by passing it from a template using javascript to the controller or by using what is called "RESTful services". In Python documentation for Routes and RESTful services, it shows "Several routes are paired with an identical route containing the format variable. The intention is to allow users to obtain different formats by means of filename suffixes; e.g., "/messages/1.xml".

This ultimately means that you could put a test in the controller to see if it JSON or HTML data:

def view(self, id, format='html'):

     if format == 'json':

         return self.jsonify(data)

     else:

         return ('templates/project.mako')

Then, the output will depend on how the data is called, for example:
http://examplepath/controller/page.html - the format will be html thus displaying an HTML page
or
http://examplepath/controller/page.json - using json format and application/json content type

However, BuildAPI takes the approach of using javascript to set the format and furthermore, this javascript is inserted into another template file (call it B) that is invoked by the main template (call it A), so A invokes values from B. Maybe I am just confusing things and I have it all wrong? PLEASE correct me if I am wrong! (I've sent some emails out to request for help on the matter)

In BuildAPI, I understand that the controllers will check the 'format' of the request by using Pylon's "request.GET" and I understand that for the "Reports" this format (such as charts) is set in the respective mako template files. If it is an html request, it shows the HTML page, if it is a json request then it shows the json data in the form of a table or chart in the html page. However, some controllers (namely recent.py, running.py and pending.py) - where is the format set? I don't see it set anywhere in the controller, model or template file and it is not inheriting it from anywhere, yet the format seems to be set and it uses html + json to create those nice looking tables! How?

Moreover, I also received errors when trying to 'jsonify' output with more than one key/value pairs such as a pylons list that contains:
 {'name': 'mozilla-1.9.1-win32-unittest', 'starttime': '2009-09-27 14:06:30',  'endtime': '2009-09-27 14:27:50'}
It gives me the error:

Error - <type 'exceptions.TypeError'>: datetime.datetime(2009, 9, 27, 14, 27, 50) is not JSON serializable

Aaahh, the frustration!

Although, I've thought about a simpler solution:

1. Dump the data from the query into a python list
2. Then dump that data into json objects and pass it as a c_context variables to the MAKO template
3. n the template, iterate through the results to display it using google visualizations to create table or charts

However, I've read that this may not be as secure as it perhaps uses the javascript eval() function – but I'm not sure.

I will update this in the near future with a clear path to 0.3.

Update: I recently exchanged emails with Armen Zambrano regarding the objectives and will post another blog in the coming days of what I've decided to do.

Getting BuildAPI online + Previous Errors - Fixed

Preamble

I had tried to get BuildAPI set up on iraq.proximity.on.ca server before milestone 0.1 release however, I ran into problems that was posted on this linked blog post. I was receiving errors running the command paster serve --reload --daemon config.ini inside or outside of a python virtual environment.

I went back onto the iraq server to take a second look at it.

Fixing Errors

When I ran the paster command by itself it gave me an error. The paster.log provided a lot of information on what could be done to possibly fix it and mentioned to make sure that PasteScript was installed properly.
I ran the command:

easy_install PasteScript==1.7.3

And what do you know, that got the Paster error resolved. So easy eh?

However, I was still having errors with BuildAPI but that was quickly solved after running the following command  in the BuildAPI directory:

python setup.py develop

And that was it, just two commands to solve all my problems...I feel like kicking myself (lol).


Serving Content
I set Paster server to host on http://iraq.proximity.on.ca:5000 and credits to Chris Tyler for pointing out that I needed to add an IPtables firewall rule to allow incoming connections to port 5000.
This is so much better than working off of my laptop!

SQLAlchemy MySQL error

When setting up the Paster server for my BuildAPI project, I used Sqlalchemy and MySQL to connect to the databases. I noticed that if I accessed a page on the server that connects to a database and then several hours later try to access the page again, I received a Server Error on the page.

Additionally, checking the paster.log file shows:

Error - <class 'sqlalchemy.exc.OperationalError'>: (OperationalError) (2006, 'MySQL server has gone away') 'SELECT builders.id, builders.name \nFROM builders \n LIMIT 50' ()

This error is a result of the database connections being specified to stay open forever and the server closing the connection after a period of time. There is an option called pool_recycle that should be set for MySQL connections, as noted in The Definitive Guide to Pylons book:

pool_recycle:

The length of time to keep connections open before recycling them. If not specified, the connections will stay open forever. This should be specified for MySQL in particular because servers typically close connections after eight hours, resulting in a “MySQL server has gone away” error.

Therefore, for example, in the project configuration I specified the pool_recyle as shown below:

sqlalchemy.your_db1.url = mysql://user:pass@localhost/your_db1

sqlalchemy.your_db2.url = mysql://user:pass@localhost/your_db2

sqlalchemy.pool_recycle = 3600

Pylons/SQLAlchemy Simple Queries

Working with Simple Queries

Recently I've been working using the MVC model (See 0.1 post here) and feel much more comfortable with it. I have to say, it's awesome! Anyway, I am still have some trouble with complex queries and to be able to complete my 0.3 objective I will have to get some help with it, but for now, I understand and can create simple queries just fine.

/// Behind the Scenes information not included - such as creating metadata for the tables or binding it to the engine (as can be seen here and here). In addition, the query.py model contains the queries and also imports data from other model files ///

Here is an example from one of the BuildAPI queries:

ss = meta.scheduler_db_meta.tables['sourcestamps']

q = select([ss.c.branch]).distinct()

q = q.where(not_(ss.c.branch.like("%unittest")))

results = q.execute()

• The database and table information is stored in the ss variable.
• In a more readable format, it would be: table sourcestamps from scheduler_db database
• The q variable is where the query begins. It is first assigned a select statement and reuses the ss variable. 
• The ss.c.branch simply means the branch field/column(.c) in the sourcestamps table. 
• The distinct is added at the end to pull out distinct values. 
• Then, the q variable is reused again and a where clause is added.

If this was to translated to a MySQL query it would look like this:

select distinct(branch) from sourcestamps where branch not like '%unittest';

---

Another simple example is:

rr = meta.status_db_meta.tables['builders']

q = select([rr.c.id, rr.c.name])

q = q.limit(50)

query_results = q.execute()

Here the query would be:

select id, name from builders limit 50;

To put this in context of the MVC model, the results from the query could be assigned to an array variable such as c.results_output that the 'View' template can use to display it. This will be shown in another blog post, perhaps in the 0.2 release.

Using Vim with Python

Getting Vim to work with Python
I'm sure there are better editors out there but I've been using vim for creating/editing python scripts. Also I should mention that since I am accustomed to typing vi on the command line, I have vi aliased to vim (alias vi=vim).
However, using vim with the default configuration is not ideal for python, especially with its 8 character tab spaces! After doing some research, I came across a few changes you can make to vim's configuration to optimize it for use with python:

You will need to edit the /etc/vimrc file (on Fedora), and append the following four lines:

autocmd BufRead,BufNewFile *.py syntax on

autocmd BufRead,BufNewFile *.py set ai

autocmd BufRead *.py set smartindent cinwords=if,elif,else,for,while,with,try,except,finally,def,class

au FileType python setl autoindent tabstop=4 expandtab shiftwidth=4 softtabstop=4

This will automatically enable syntax highlighting, and do automatic indentation for Python code (indicated by the "smartindent cinwords=..."). In addition, once a python file is loaded, tabs will be expanded to spaces and be 4 characters long instead of the 8 character default tab space.
That's it.

Credits to http://wiki.python.org/moin/Vim