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.