11 million installations can't be wrong

MySQL Journal

Subscribe to MySQL Journal: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get MySQL Journal: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


MySQL Journal Authors: Jnan Dash, Dynatrace Blog, Stackify Blog, Sematext Blog, OnPage Blog

Related Topics: MySQL Journal

MySQL Journal: Article

How to spyce up your data

Part 3 in a series, we explore how to use spyce with MySQL

(LinuxWorld) — In Part 2 in this series, we discovered that the habits one forms from using PHP do not necessarily carry over well to spyce, despite the similarity between the two approaches to server-side scripting. One important difference is in the way spyce compiles your files and how that affects the behavior of your programs at run-time. There are two types of include files: static and dynamic. Your main program will be able to use any variables or functions declared publicly in a statically included file, but it will not see those same variables and functions if that file is dynamically included.

PHP programmers often use dynamic includes to set a Web site's theme variables. If the user picks the blue theme, for example, the PHP program can simply include the blue_theme_vars.php file, which sets all of the font, color and other style variables that the rest of the PHP program can use. You then use these variables as part of your cascading style sheet definition or within the PHP code itself.

Because spyce doesn't let you set variables from a dynamically included file, the obvious alternative is to store theme values in a database table. In the following example, we'll look up the theme_id number from a user's record (the user id is arbitrarily set to 2 in this case). Then we'll pull the theme values from the appropriate table and insert them into the cascading style sheet definition.

file index.spy

[[\
import MySQLdb

db = MySQLdb.connect(db='test', host='localhost', user='joe-user', passwd='super-secret-password')

user_id = 2

c=db.cursor(MySQLdb.cursors.DictCursor) c.execute("select theme_id from users where user_id=%s",user_id) user=c.fetchone() user_theme=user['theme_id'] c.close()

c=db.cursor(MySQLdb.cursors.DictCursor) c.execute("select * from themes where theme_id=%s",user_theme) theme=c.fetchone() c.close()

print """<html><head><title>This Site Title</title> <!-- css definitions for this page --> <STYLE TYPE=text/css><!-- BODY { font-style : """, theme['default_font_style'], """; font-family : """, theme['default_font'], """ } --> </head> <body>"""

print "Put your page content here."

print "</body></html>" ]]

The above code should be fairly straight-forward for any Python user with the possible exception of the approach I used with MySQL. The cursor-handling technique I'm about to describe is roughly the Python equivalent of the PHP MySQL command mysql_fetch_array(). Strictly speaking, it is a MySQLdb and Python issue that has nothing to do with spyce in particular. But MySQL is one of the most popular databases for Web applications, so this method of using cursors is one you'll want to tuck away in the gray matter for future use if you're going to use the MySQLdb interface to access MySQL from your spyce programs.

You would normally create a MySQLdb cursor with the simpler statement c=db.cursor(), which passes no arguments. When you fetch a row of data, the data will come back in an array (real Python programmers call arrays "tuples" as part of an effective strategy to ensure that non-programmers won't know what they're talking about: job security). This technique can make a program difficult to maintain, especially if your program performs SQL operations such as SELECT *, which returns all the columns in the table. When you fetch the row into the array, the column values will appear in the same order as the table structure. If the third column in your table is city, then you get your value by indexing into the array starting at zero. If you fetch the row into an array called stuff with the command stuff=c.fetchone(), then the value for city is stuff[2].

That's a fine way to work if your table structure never changes. But if you insert a new column into your table structure such that city becomes the fourth column, your program will retrieve the wrong value until you adjust all the array offsets in your code.

One way to make your program easier to maintain is to declare your cursors the same way they appear in the sample program, c=db.cursor(MySQLdb.cursors.DictCursor). Now, when you fetch the row into an array with stuff=c.fetchone(), you will get what Python calls a dictionary. A Python dictionary lets you grab the city value by its column name, such as stuff['city']. The expression stuff['city'] not only makes your program more readable, your program will continue to work even if you change the structure of the table and the city column moves.

You may decide at some point in your program that you want to access the entire array of values after you have already retrieved it as a dictionary. We'll look at one reason why you'd want to do that in a moment. The method is simple. Just pull the values out of the stuff dictionary and put it into a new array with the command newarray=stuff.values(). This is all made possible by the fact that a Python dictionary object includes the methods values() and keys(). In this case, stuff.values() retrieves the data, and stuff.keys() retrieves the array of column names.

Does that mean you can now get to the city value with the offset you would have used if you had declared a regular cursor newarray[2]? Surprisingly, the answer is no. For a reason that eludes the author, when you retrieve the data from a SQL query as a dictionary, Python orders the data however it likes. This is true even if you try to order the data by structuring your query differently. For example, suppose you start with a regular cursor with a command like c=db.cursor() and then execute the following c.execute("select uid, name, address from users"). When you fetch the data with r=fetchone(), you'll put the values in r in the expected order (uid, name, address). But if you started this process with c=db.cursor(MySQLdb.cursors.DictCursor) to fetch a dictionary, it will return that dictionary data in an order based on some algorithm that's probably tied to the price of tea in China and sunspot activity. In other words, for all intents and purposes, you'll get the data back in random order.

What's a lamb, duh?

Spyce takes advantage of yet another feature of Python called lambdas, which are extremely handy for building data or template-driven Web applications. A "lambda" is Python syntax that lets you treat data like functions and functions like data. Don't worry, I didn't understand that at first, either, and the name doesn't lend much of a clue as to what it does. One can only assume someone chose the name "lambda" as insurance against the possibility of someone looking up the word "tuple" in a standard dictionary and finding out what it means in Python.

The best way to explain how to use a "spyce lambda" is probably by example. In this example, we create a spyce lambda by writing a little spyce program that prints out a table of values and assigning the program to the variable myTable. The important bit of information you need to take away is that the spyce lambda function starts with a double square bracket followed by the keyword spy, which is in turn followed by a list of parameters. Slap a colon on the end and you've got the beginnings of a spyce lambda.

Here's the finished spyce program:

file: mary-had-a-little-lambda.spy

[[\
import MySQLdb
db = MySQLdb.connect(db='test', host='localhost', user='joe-user', passwd='super-secret-password')
myTable = [[spy column, row:
   <table><tr>
   [[for val in column: {]]
      <td><b>
      [[=val]]
      </b></td>
   [[}]]
   </tr><tr>
   [[for val in row: {]]
      <td>
      [[=val]]
      </td>
   [[}]]
   </tr></table>
]]

print "<html><body>"

c=db.cursor(MySQLdb.cursors.DictCursor) c.execute("select head as 'Headline', cat as 'Category', top as 'Topic' from stories") r=c.fetchone()

k=r.keys() v=r.values()

myTable(k,v)

print "</body></html>" ]]

The myTable variable is set to a lambda with two parameters (column and row), after which you'll see a combination of HTML and spyce code. The for val in column and for val in row statements tell you that the parameters are used to pass arrays... er, that is, tuples.

The next section of code prints out the HTML header, makes a connection to the database, fetches data into a dictionary, and then pulls the keys and values of the dictionary into the two tuples k and v. It passes k and v as the parameters column and row to our lambda myTable and calls the lambda as a function.

By the way, you should note another change we added to the way we handle the SQL. This time, the SQL select statement uses the as feature to return the data with keys that are more suitable to a viewable Web page than the raw column names. Instead of the data coming back with key names like head, cat and top, it will instead have the key names Headline, Category and Topic.

Regardless, you might be wondering at this point why we simply didn't create a function called myTable that does the same thing as the spyce lambda. Such a function might look like this:

[[def myTable(spy column, row): [[{]]
   <table><tr>
   [[for val in column: {]]
      <td><b>
      [[=val]]
      </b></td>
   [[}]]
   </tr><tr>
   [[for val in row: {]]
      <td>
      [[=val]]
      </td>
   [[}]]
   </tr></table>
[[}]]

Or if you want to get rid of most of the brackets, you could define the function this way:

[[\
def myTable(column, row):
   print "<table><tr>"
   for val in column:
      print "<td><b>"
      print val
      print "</b></td>"
   print "</tr><tr>"
   for val in row:
      print "<td>"
      print val
      print "</td>"
   print "</tr></table>"
]]

Well, you could have done that. And in many cases, that's exactly what you'll want to do. While the spyce lambda has a high "coolness" factor, it is really just a way to turn HTML into a function, which can sometimes make it easier for an HTML person to avoid having to deal with Python. The basic idea is that an HTML expert can format a page and simply insert placeholders for data. The Python programmer would later replace the placeholders with spyce for statements, spyce variables and blocks of code wherever appropriate. It's not as elegant as XML-based solutions that truly separate presentation from code and data, but with some practice, you can get close. And for some organizations it will be easier than requiring Python programmers to be artists at composing HTML Web pages.

Conclusions

That about wraps up this series on spyce, the Python-based, server-side scripting solution. After having played with spyce for a few weeks now, I have come away with a reasonably positive first impression. As a fan of Python, I find it a pleasure to compose programs and Web pages using spyce. I tested spyce primarily as a CGI solution, so performance on my system has ranged from acceptable to "I shaved three times before the page finished loading." I can't guarantee it will run fast enough for your application, but it performs significantly better in a fastcgi or mod_python configuration.

If you're a Python fan who isn't proficient in Java or PHP, then I strongly recommend it. Spyce could earn its place among these and other alternatives eventually, but it's not as mature yet. So if you're proficient with PHP or Java and happy with your language, but also like Python, I recommend that you keep doing what you're doing. But keep your eye on spyce.

More Stories By Nicholas Petreley

Nicholas Petreley is a computer consultant and author in Asheville, NC.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.