SQLalchemy Cleanup Challenge

Yesterday I found myself writing some very interesting SQLalchemy. The problem is I have a date column in PostgreSQL that is stored as epoch time, so it is just an Interger column. I need to group by year,month and grab the total count of status='A' groups for that year,month combination.

Here is what I came up with, can you make it cleaner? Faster? I am curious to see the different variations people come up with.

        pg_date_part_month = sa.func.date_part('month',
                sa.func.to_timestamp(Group.register_time))
        pg_date_part_year = sa.func.date_part('year',
                sa.func.to_timestamp(Group.register_time))

group_month_select = ( 
            db.query(
                sa.sql.label('year', pg_date_part_year),
                sa.sql.label('month', pg_date_part_month),
                sa.sql.label('total', sa.func.count(Group.status))
            )   
            .filter_by(status='A')
            .group_by(pg_date_part_year)
            .group_by(pg_date_part_month)
            .group_by(Group.status)
            .order_by(pg_date_part_year)
            .order_by(pg_date_part_month)
        )

Using SQLAlchemy Custom Types to Convert Integers to DateTime

Today I was working on fetching out some data from an existing PostgreSQL server and generating some BSON output that would later be imported in to MongoDB. One of the problems I ran in to was that I needed to format the timestamps easily for each row of data.

Searching the internet I ran across this blog post by Ralph Bean, which does just that, but at a level that was well beyond what I needed. So taking away some inspiration from Ralph's blog post, I decided to just go with a Custom Type.

from time import mktime
from datetime import datetime

class IntegerDateTime(types.TypeDecorator):
    """Used for working with epoch timestamps.

Converts datetimes into epoch on the way in.
    Converts epoch timestamps to datetimes on the way out.
    """
    impl = types.INTEGER
    def process_bind_param(self, value, dialect):
        return mktime(value.timetuple())
    def process_result_value(self, value, dialect):
        return datetime.fromtimestamp(value)

Then in my reflected table, I just override the column that holds the integer representation of the datetime I want.

group_table = sa.Table('groups', metadata,
    sa.Column('register_time', IntegerDateTime),
    autoload=True,
    include_columns=[
        'group_id',
    'register_time',
    'type'
    ],
)

Now when we query and begin to use our results, register_time will be a DateTime object making it very easy to do any timedelta arithmetic or string formatting.

Working for Geeknet

I joined Geeknet full time this week as a Senior Software Engineer on the SourceForge team. I am really looking forward to the challenge ahead and working with everyone on the team. As part of the SourceForge team I will be helping to make SourceForge better for the current users and working on improvements that will attract new projects to SourceForge. My personal goal is to make SourceForge part of the day to day vocabulary like it used to be. I want see SourceForge back in the top three when people ask "Where is a good place to host my code?" or "If you were creating an OSS project, where would you put?". I believe that this team can do that and I am really excited for the future.

I will be candid here, when I first thought of working for Geeknet on the SourceForge team, I was thinking probably the same thing you are ... isn't that only CVS and SVN? That is just for hosting downloads right?

So as part of my initial curiosity of what people currently think of SourceForge versus what is actually happening on the site I did some googling and read up on anything that mentioned SourceForge that was less than 6-months old. I found that it wasn't so much that SourceForge wasn't keeping pace with the other options out there, but that it wasn't marketing the fact that it was keeping pace with the other options out there.

Here are a list of improvements that I didn't know about until I did some research.

• SourceForge supports Git and Mercurial (and Bazzar).
• You can fork and submit merge requests with Git and Mercurial.
• You don't have to get approval for projects.
• There is a user project space for creating miscellaneous repositories.
• Integrated Tracker / Commit messages. [#TICKET]
• 9 out of 10 Ninjas recommend it.

When you combine that with one of the best mirror networks out there, live IRC support, and a great community; You get a pretty nice resource. And at a price of FREE.99 (like beer), it was all I needed to know that this was where I wanted to work. Contributing to an long-time open source project that paved the path for other project hosting sites while helping to keep that open spirit alive and well.