authored by Wayne Witzel III

SQLalchemy Cleanup Challenge

On October 13, 2011 In python, sqlalchemy Permalink

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)
        )
Read and Post Comments

Using SQLAlchemy Custom Types to Convert Integers to DateTime

On October 12, 2011 In python, sqlalchemy Permalink

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.

Read and Post Comments

paster shell - do people know about it?

On January 21, 2010 In sqlalchemy, pylons Permalink
Today I was having a chat today about Pylons vs. Django and for the most part it was pretty diplomatic. We got to talking about the Admin interface the Django has. Which you don't have to do any extra boiler plate for, it is just there for you. With Pylons you have to use something like FormAlchemy or use Turbogears to get a similar style admin interface for your models and data. Since we were sitting at a computer, I went ahead brought up a quick project and did a little demo of the paster shell. Sure, it involves typing and it isn't as pretty or "fast" as an admin panel, but he didn't even know it existed. One of the common things he mentioned was, "if I want to change the menus that are dynamically defined" or "if a username needs to be changed" .. and the application itself doesn't have a custom admin panel, with Pylons he had to do raw SQL.
$paster shell pylons_config.ini

All objects from demo.lib.base are available
Additional Objects:
   mapper     -  Routes mapper object
   wsgiapp    -  This project's WSGI App instance
   app        -  paste.fixture wrapped around wsgiapp

>>> error_user = meta.Session.query(model.User).filter_by(username='wwitzel 3').one()
>>> # nice thing about this, is you also will get exceptions throw if more than one record exists
>>> error_user.username
u'wwitzel 3'
>>> error_user.username = 'wwitzel3'
>>> meta.Session.commit()
>>> menu_typo = meta.Session.query(model.Menu).filter_by(id=1).one()
>>> menu_typo.value
u'Abuot'
>>> menu_typo.value = 'About'
>>> meta.Session.commit()
So that is a very simple example of how one would use the paster shell to update some bad data in the database while ensuring integrity of your custom model and extension code. After I showed this to my friend he wasn't as concerned about the lack of a web interface for administration within Pylons.
Read and Post Comments

SQLalchemy and JSON w/ Pylons - Best Practices

On August 17, 2009 In sqlalchemy, pylons Permalink
I asked the question I Stackoverflow and maybe it was too generic for the site, since it just got trolled with "Google keyword" by some d-bag. So I deleted it and figured I'd throw it up on my blog a see about getting some feedback from the people who read this pile about. The reason I ask this is mainly because I am preparing to do some updated screencasts for Pylons. I've seen multiple ways referenced in official docs and I have done it a few different ways myself. I am using Pylons and I am curious what the best practices are for this common scenario? I have used something similar to this for auto-magically making the conversion happen.
# The auto-magic version
# I pulled this off a blog, forget the source.
def _sa_to_dict(obj):
    for item in obj.__dict__.items():
        if item[0][0] is '_':
            continue
        if isinstance(item[1], str):
            yield [item[0], item[1].decode()]
        else:
            yield item

def json(obj):
    if isinstance(obj, list):
        return dumps(map(dict, map(_sa_to_dict, obj)))
    else:
        return dumps(dict(_sa_to_dict(obj)))

# here is the controller
@jsonify
def index(self, format='html'):
    templates = Session.query(Template).all()
    if format == 'json':
        return json(templates)
I have also done the version where you use the jsonify decorator and build your dictionary manually, something like this, which is ok if I need to define some custom behavior for my JSON, but as the default behavior seems excessive.
@jsonify
def index(self, format='html'):
    if format == 'json':
        q = Session.query
        templates = [{'id': t.id,
                      'title': t.title,
                      'body': t.body} for t in q(Template)]
        return templates
I've also created an inherited SA class which defines a json method and have used that on all my objects to convert them to JSON. Similar to the the fedora extensions. Maybe I missed some obviously library out there or some obvious helper in the Pylons packages, but I feel like this is a very common task being done a dozen different ways between docs, source, and my own personal projects. Curious what others are doing / using.
Read and Post Comments

Jython 2.5 and snakefight for deploying Pylons w/ SQLAlchemy + Oracle.

UPDATE / 13 March 2009: snakefight 0.3 now has a --include-jar option, prefer that to using my hack. After reading P. Jenvey's blog post about Deploying Pylons Apps to Java Servlet Containers I immediately downloaded the Jython 2.5 beta and installed snakefight to give it a try. One of our services where I work is a Pylons based application. It is deployed using paster and Apache ProxyPass. Our main application is written in Java and is deployed as a war under Jetty. So if I can get my Pylons application built as a war and deployed that way, it would greatly simplify our deployment process. [sourcecode language="bash"] $ sudo /opt/jython25/bin/easy_install snakefight $ /opt/jython25/bin/jython setup.py develop $ /opt/jython25/bin/jython setup.py bdist_war --paster-config dev_r2.ini ... output of success and stuff ... $ cp dist/project-0.6.8dev.war /opt/jetty/webapps [/sourcecode] Now I visit my local server and hit the project context. I get some database errors, kind of expected them. So for the time being, I'll be running this directly using Jython to speed up the debugging process. A quick googling of my DB issues turns up zxoracle for SQLalchemy which uses Jython zxJDBC. I install that in to sqlalchemy/databases as zxoracle.py and give it another go. Changing the oracle:// lines in my .ini file to now read zxoracle:// Now it can't find the 3rd party Oracle libraries (ojdbc.jar). [sourcecode] $ cd ./dist $ jar xf project-0.6.8dev.war $ cd WEB-INF/lib $ ls # no ojdbc.jar as expected ... $ cd ~/project $ export CLASSPATH=/opt/jython25/jython.jar:/usr/lib/jvm/java/jre/lib/ext/ojdbc.jar $ /opt/jython25/bin/jython /opt/jython25/bin/paster serve --reload dev_r2.ini [/sourcecode] Now it is looking a little better and it able to find the jar, but still a DB issue, now with SQLalchemy library. Not having a ton of time to investigate, I decide to try rolling back my SQAlachemy version for Jython. Turns out rolling back to 0.5.0 fixed the issue. I'll be investigating why it was breaking with 0.5.2 soon (tm). So now I rerun it, and get a new error. [sourcecode lang="bash"] AttributeError: 'ZXOracleDialect' object has no attribute 'optimize_limits' [/sourcecode] I decide I am just going to go in to the zxoracle.py and add optimize_limits = False to the ZXOracleDialect. No idea what this breaks or harms, but I do it anyway and rerun the application. Success! Every thing is working now. No liking the idea of having to manually insert the Oracle jar in to the WEB-INF/lib and not really wanting to much around with environment variables, I also implemented a quick and dirty include-java-libs for snakefight, the diff for command.py is below. This allows me to pass in a : separated list of jars to include in the WEB-INF/lib. EDIT: The diff I posted isn't needed since I put it on my hg repo. You can grab it from here. So now I am back to building my war. Just as before. [sourcecode lang="bash"] $ /opt/jython25/bin/jython setup.py bdist_war --paste-config dev_r2.ini --include-java-libs /opt/jython25/extlibs/ojdbc.jar running bdist_war creating build/bdist.java1.6.0_12 creating build/bdist.java1.6.0_12/war creating build/bdist.java1.6.0_12/war/WEB-INF creating build/bdist.java1.6.0_12/war/WEB-INF/lib-python running easy_install project adding eggs (to WEB-INF/lib-python) adding jars (to WEB-INF/lib) adding WEB-INF/lib/jython.jar adding Paste ini file (to dev_r2.ini) adding Paste app loader (to WEB-INF/lib-python/____loadapp.py) generating deployment descriptor adding deployment descriptor (WEB-INF/web.xml) created dist/project-0.6.8dev-py2.5.war $ cp dist/project-0.6.8dev-py2.5.war /opt/jetty/webapps $ sudo /sbin/service jetty restart [/sourcecode] And presto! I am in business. My pylons application is deployed under Jetty and all the selenium functional tests are passing. I am sure there is probably a easier, neater, or cleaner way to do all this, but this was my first iteration through and also my first time ever deploying a WAR to a java servlet container so all in all I am happy with the results. Performance seems about the same as when running the application with paster serve, but Jetty does use a little more memory than before (expected I guess).
Read and Post Comments
Next Page ยป