Rudimentary Art of Programming & Development

When performing query(), SQLAlchemy does not execute __init__ of the corresponding ORM objects.

Thus, if you have some logic inside __init__, those won’t get executed.

To have the desired behavior, you need to put such logic inside a function that takes no arguments, then, attach @orm.reconstructor on it.

Reference:

• SQLALchemy Mapper Doc

distinct() method is accessible from Query object. The documentation is kind of obscure, so hopefully this post is helpful for others.

Example:

 @classmethod
 def get_all_category(cls):
 return meta.Session.query(cls.category).distinct().all() 

Reference:

SqlAlchemy 04 Documentation about Query object

The documentations of SqlAlchemy gets better in every version, but I can never remember the steps of creating Engine, Index, and Session.

So, I created 3 minutes tutorial on how to define the ORM class using declaration_base(), build that table, populates, and fetch.

See code here.

I’ve been using SQLObject for a while now, and I’m 60/40 with it.

I am mostly happy of SQLObject because:

• The documentation is easy to read.
• Getting up to speed is ridiculously easy, in less than 5 minutes I have a ‘user’ MySQL table wrapped with class User.
• The generated SQL is efficient, for all my needs.
• The returned ResultSet can be sliced, that’s nice. 

Why I’m not happy with it:

• The invisible ‘id‘ column, that always get created for every new table. I have multiple needs for ORM to just create the table as-is, explicitly (Like how SQLALchemy does).

Why I haven’t switched to SQLAlchemy:

• I have, but not for all model classes.
• My noobiness limits me on what I can do with SQLAlchemy. For example, I haven’t figure out how to add Column object to existing Table object (that was created dynamically).  

That being said, SQLObject is very useful if you want to create MySQL table dynamically. You can achieve this by using meta-programming.

Example:

def create_or_get_dynamic_mysql_table(name):
   try:
      # SQLObject cache all classes that already defined,
      # If 'name' already defined, just get it using findClass()
      dynamic_table_classname = findClass(name)
   except:
      # Create new 'name' class
      # If you see the 2nd param of classobj, 'name' class extends SQLObject class
      dynamic_table_classname = classobj( name, (sqlobject.SQLObject,), {} )

   return dynamic_table_classname

# Then Call the createTable method
table_class = create_or_get_dynamic_mysql_table('awesome')
table_class.createTable(if_not_exists=True)

References:

• SQLObject 0.10 documentation
• SQLAlchemy 0.5 documentation

What is this problem about:

This is actually MySQL error message. It means that there are no MySQL connections anymore.By MySQL default, idling database connections will be closed after 8 hours. Not having MySQL connections means that no SQL operations can be performed. SqlAlchemy is simple reporting the error.

When using SQLAlchemy, how to prevent such problems?

See sample code below:

engine = create_engine(db_path, pool_size = 100, pool_recycle=7200)

 Take a look at the pool_recycle variable. That will configure SqlAlchemy engine to reconnect every x number of seconds. This easy solution makes sure the existence of MySQL connection.

References:

• SqlAlchemy Trac – FAQ
• Mail Archive – MySQL server has gone away 
• GoogleGroup – Has Gone Away 

Among other programming languages, why choose Python?

Let’s go over the basics first, these traits also exist in other programming languages:

• Python is interpreted Programming Language. It is also a high-level programming language. Therefore, just like Ruby or Perl, Python is easy to learn and easy to get up to speed on sizable projects.
• Python is also object-oriented language. But that’s nothing new, most modern programming languages are object-oriented anyway.

Naturally, readers will ask, if Python can only do what MY programming language already can… Why do I have to learn it? Below is my reasoning why… and hopefully readers will convert to Python

1. Python does not have funky sigils or special system_call functions. The only one you have to memorize is: __someDefaultMethods__ By not having too many special characters, programmers spend less time browsing documentation or mailing list on Google.

2. Python tabbed indentation actually contributes to highly readable code. Readable code helps especially when you are juggling many different projects every week, it allows you to remember quickly what you did last time.

3. Python’s power and scalability is proven not hype.

• Google, NASA, United Space Alliance, Industrial Light & Magic, Phillips, Honeywell, EVEOnline, and YouTube (even before bought by GOOG) use Python.
• There are also “less big” examples, which demonstrate Python has low barrier of entry, such as: Jaiku (also bought by GOOG), Renkoo, and projectpipe.com

4. For Back-end Programmers, Database Designer, and System Engineers:

• Look at Twisted[1]. It is one kick ass networking library. It supports many networking protocols that allows building web servers, email, or chat easier.
• Look at SQLAlchemy[2][3], I never seen ORM so powerful and yet so easy to learn.

5. For Application Developers:

• Django is a complete out-of-the-box Web Framework. Their slogan — “For Perfectionists with deadlines”
• TurboGear is also another awesome complete Web Framework solution. But I suggest readers wait until they included SQLAlchemy as their new database stack.

6. For Javascript Developers:

• Look at Mochikit, It is a complete javascript framework that’s very Pythonic. Mochikit is still just Javascript, but have Python’s sense of elegance.

Below are quotes from Eric Raymond on why he likes Python:

– “In Python, I was actually dealing with an exceptionally good design. Most languages have so much friction and awkwardness built into their design that you learn most of their feature set long before your misstep rate drops anywhere near zero. Python was the first general-purpose language I’d ever used that reversed this process.“

– “It (Python) is compact–you can hold its entire feature set (and at least a concept index of its libraries) in your head. C is a famously compact language. Perl is notoriously not;“

– “What I really wanted was code that would analyze the shape and members of the initializer, query the class definitions themselves about their members, and then adjust itself to impedance-match the two sets… …This kind of thing is called metaclass hacking… …Thirty-two lines, counting comments… …Brace yourself: this code only took me about ninety minutes to write—and it worked correctly the first time I ran it.“

– “So the real punchline of the story is this: weeks and months after writing fetchmailconf, I could still read the fetchmailconf code…“

Summary:

Hopefully I provided enough examples on why Python can be your next favorite programming language. Programming in Python is fun indeed.

Resources:

1. http://twistedmatrix.com/trac/wiki/FrequentlyAskedQuestions
2. http://www.sqlalchemy.org/
3. http://en.wikipedia.org/wiki/SQLAlchemy
4. If you are new in Programming, this E-book can help
5. Why use Python in Windows XP?
6. Python Doc – String
7. Python Doc – Common OS path manipulation
8. If you are familiar with apt-get, Python have something similar for its modules: easy_install

What Others Said about Why Python:

• http://www.stanford.edu/~pgbovine/python-teaching.htm
• http://www.ferg.org/projects/python_java_side-by-side.html

The Hand of FuManchu pits SQLAlchemy against Storm and Geniusql under his benchmarks.

In the INSERT benchmark FuManchu(I believe he is involved with CherryPy) claims that SQLAlchemy is making over 400 function calls.

FuManchu show these 2 findings on his benchmarks:

• SQLAlchemy spends a lot of time building the bound parameters.
• He also claims that SQLAlchemy takes 73 Python function calls fetching auto-increment id.

In response, Mike Bayer wrote, in his blog, saying that SQLAlchemy aims to be a complete ORM with rich features. He even corrected the benchmarks here.

What does this benchmarks mean to me? Nothing much really. I like using SQLAlchemy, it has a lot of features I need and some more. IF this benchmark is right, I can always use raw SQL just for the sake of speed, although such code is ugly.

For readers, read the blogs comments as well. They are very interesting.

Below are couple of awesome SQLAlchemy tutorials,

the list will grow as I find more of these:

• http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html
• http://www.splee.co.uk/category/simpleblog/
• Using SQLAlchemy with Pylon
• http://www.thesamet.com/blog/2006/11/17/tutorial-how-to-implement-tagging-with-turbogears-and-sqlalchemy/
• http://atwork.wordpress.com/2006/08/11/switching-to-sqlalchemy/

Another dependency issue happened again to me while re-installing my python development environment.

What did I do to re-install Python?

1. Downloaded python 2.5.1
2. Downloaded easy_install
3. Installed every modules using easy_install (w/o version number, so that I get the most recent stable build)

What went wrong?

1. SQLAlchemy import was messed up. All the sudden I couldn’t import create_session…

What did I do?

>>> import sys
>>> sys.path
['', '/Library/Frameworks/Python.framework/Versions/2.5/lib/

python2.5/site-packages/SQLAlchemy-0.4...egg', ]

Nothing is weird, but this Google Group discussion mentioned that an unrelated project had similar dependency issue to mine. Their solution is to downgrade SQLAlchemy to 0.3.10 (version 0.3.11 is also OK)

Using SQLAlchemy:

easy_install -U “SQLAlchemy==0.3.11″ 

Voila, import problem solved.

2. Cannot run MySQL_python because of missing dot so file, now that’s messed up big time. This is the snippet of the error:

ImportError: dlopen(~/.python-eggs/MySQL_python-1.2.2-py2.5-macosx-10.5-i386.egg-tmp/_mysql.so, 2): Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient_r.15.dylib
Referenced from: ~/.python-eggs/MySQL_python-1.2.2-py2.5-macosx-10.5-i386.egg-tmp/_mysql.so
Reason: image not found

What did I do?

Googling this problem, reveal the cure for it:

sudo mkdir /usr/local/mysql/lib/mysql
sudo cp /usr/local/mysql/lib/libmysqlclient_r.15.dylib /usr/local/mysql/lib/mysql/libmysqlclient_r.15.dylib

Apparently the particular mysql lib file is located in the wrong folder.

Done, now the project runs smoothly the way it was before.

Lesson Learned:

Google Search is very Python friendly, use it extensively.

What does it mean:

During connect process SQLAlchemy is trying to grab database module specific to the dialect (e.g. MySQL or MySQLite)

How to resolve it:

Make sure you installed python module for your specific database. For mine, it’s MySQL thus I installed mysql-python.

Stay tuned for more Python setups or installations…