Archive for the ‘SQLAlchemy’ Category

Beep My Stuff enters public Beta

Thursday, April 2nd, 2009

Beep My Stuff

Beep My Stuff


After a rather long period in closed beta I've opened Beep My Stuff to a public beta.

Beep My Stuff is a web site that makes it FREE and easy to create an online library of your Books, Movies, Video Games and Music. Check it out, all feedback is much appreciated.

It's built with Turbogears, SQLAlchemy, Genshi and countless other python modules. It's all sitting on Python, FreeBSD and PostgreSQL.

Just wanted to say thanks to all the pythonista's out there that helped me get this far :)

Posted in Beep My Stuff, FreeBSD, PostgreSQL, Python, SQLAlchemy, Software, TurboGears | 1 Comment »

SQLAlchemy-Migrate upgrade scripts in a transaction

Sunday, July 27th, 2008

SQLAlchemy Migrate is a really good tool for managing database upgrades for SQLAlchemy centric projects. I've been using it for 6 months on New Metal Army and I haven't had a screwed website upgrade yet!

For those that don't know SQLAlchemy-migrate allows you to version control database changes and easily upgrade and downgrade a database. Basically you write a python script with two functions: upgrade and downgrade. You test the script against the database, commit it to the SQLAlchemy-migrate version repository (not to be confused with your source control mechanism). Finally you upgrade your development database.

When it the time comes to deploy the new application you simply ask sqlalchemy-migrate to upgrade your database to the current version. sqlalchemy-migrate reads the current version of your schema from the database (via a custom table it inserts) and proceeds to upgrade your schema by running each upgrade script in turn.

Often you want your upgrades and downgrades to run within a transaction. Not because you expect them to fail but because while writing them you don't wont to leave the database partially upgraded or downgraded if your script fails. To do this I wrote a transaction decorator. Here is a template for an upgrade script:

  1. #!/usr/bin/env python
  2. # encoding: utf-8
  3.  
  4. from datetime import datetime
  5. from sqlalchemy import *
  6. from migrate import *
  7. from migrate.changeset import *
  8.  
  9. metadata = MetaData(migrate_engine)
  10.  
  11. def transaction(f, *args, **kwargs):
  12.     def wrapper(*args, **kwargs):
  13.         connection = migrate_engine.connect()
  14.         transaction = connection.begin()
  15.         try:
  16.             result = f(*args, **kwargs)
  17.             transaction.commit()
  18.             return result
  19.         except:
  20.             transaction.rollback()
  21.             raise
  22.         finally:
  23.             connection.close()
  24.  
  25.     wrapper.__name__ = f.__name__
  26.     wrapper.__dict__ = f.__dict__
  27.     wrapper.__doc__ = f.__doc__
  28.     return wrapper
  29.  
  30. @transaction
  31. def upgrade():
  32.     pass
  33.  
  34. @transaction
  35. def downgrade():
  36.     pass
  37.  

I fill in the upgrade and downgrade functions and I'm done :)

I include the decorator in every script as it's good practice to make your scripts as independent as possible. If you imported it from a module you may improve it in the future and inadvertently break your old downgrade scripts.

I hope this helps you version control your database schema and data.

Posted in NewMetalArmy, PostgreSQL, Python, SQLAlchemy, TurboGears | 8 Comments »

PostgreSQL, SQLAlchemy, Dropping all tables and sequences

Friday, November 23rd, 2007


I've been working in deployment scripts for my current project and sometimes I need to drop all the tables and sequences from the database so I can create everything from scratch. I had been doing a

 
DROP SCHEMA public CASCADE;
CREATE SCHEMA public AUTHORIZATION bob;
GRANT ALL ON SCHEMA public TO bob;
 

This was great but a little destructive and over the top. It removes stored procedures and triggers as well which isn't what I want. So I looked at the SQLAlchemy docs and there is a metadata command drop_all()

 
def drop_tables():
    metadata.drop_tables()
 

Unfortunately this doesn't CASCADE so this isn't going to work in cases where tables have foreign keys (which is most of the time). It also leaves sequences in place.

So I looked at dropping each table in turn with a cascade. So how do you get a list of tables and sequences in the database? I could hard code the names of the tables and sequences but that seemed a little poor.

It turns out that PostgreSQL has a set of views that expose the inner workings of your database and a few queries can give you all the information you need.

 
def get_table_list_from_db():
    """
    return a list of table names from the current
    databases public schema
    """
    sql="select table_name from information_schema.tables"\
        "where table_schema='public'"
    execute = metadata.execute
    return [name for (name, ) in execute(text(sql))]
 
def get_seq_list_from_db():
    """return a list of the sequence names from the current
       databases public schema
    """
    sql="select sequence_name from information_schema.sequences"\
        "where sequence_schema='public'"
    execute = metadata.execute
    return [name for (name, ) in execute(text(sql))]
 
def drop_all_tables_and_sequences():
    execute = metadata.execute
    for table in get_table_list_from_db():
        try:
            execute(text("DROP TABLE %s CASCADE" % table))
        except SQLError, e:
            print e
 
    for seq in get_seq_list_from_db():
        try:
            execute(text("DROP SEQUENCE %s CASCADE" % table))
        except SQLError, e:
            print e
 

The information_schema is full of interesting information. It's actually a 'view' of lower level database tables. You can find all sorts of performance and configuration information in there. Quite handy :) Here are some docs.

Good luck.

Posted in PostgreSQL, Python, SQLAlchemy, Software | 4 Comments »