This is the follow up to my post on connecting Python to SQL Server on Mac. This describes how I set it up on Linux.

Quite a few bits of it are copied from the Mac instructions, so it might be a little repetetive if you’ve already read that. (No new jokes, I’m afraid).

Edit: I’ve updated this having tried the installation a few more times. I’m still not 100% sure, but I appear to have got it working several times in a row.

Summary

• Install unixodbc and unixodbc-dev
• Install the patched version of freetds
• Install tdsodbc
• Install the patched version of freetds again
• Configure unixodbc
• Install python
• Install sqlalchemy and pyodbc

Installing freetds on Linux

I’m basing this on a bare bones install of Debian Lenny (ie starting with the bare minimum you can install).

I initially setup sudo, git, svn, bzr, openssh and emacs22. I won’t go too much into them here. You might use different tools. I did place my /etc and /home under version control with git as soon as possible, which I highly recommend for experimental setups. I regret not putting /usr under version control as well.

At some point you will need the tools to compile source such as python2.6 and pyodbc. You might as well install them now.

sudo aptitude install build-essential

I also recommend installing checkinstall which allows you to install from source as if it were a proper debian package. This allows you to meet dependencies with it and remove it easily at a later date.

 sudo aptitude install checkinstall

Next you’ll need to install unixodbc. You’ll also need the unixodbc dev headers for later (eg compiling pyodbc).

sudo aptitude install unixodbc, unixodbc-dev

Then you need to install the patched version of freetds. I pass two options to configure which make it work better with SQL Server, but might stop it working as well with Sybase and others. I don’t really care about that at the moment, but bear it in mind.

mkdir src
cd src/
wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-patched.tgz
tar zxvf freetds-patched.tgz
cd freetds-0.82.1.dev.20090618/
./configure --enable-msdblib  --with-tdsver=8.0
make
sudo checkinstall
# When asked edit the 'provides' (11) value to be 'freetds-common'
# This allows it to be used as a dependency for tdsodbc

Now install tdsodbc:

sudo aptitude install tdsodbc

Then install tdsodbc again:

cd src/freetds-0.82.1.dev.20090618/
sudo make clean
./configure --enable-msdblib  --with-tdsver=8.0
make
sudo checkinstall
# When asked edit the 'provides' (11) value to be 'freetds-common'

I’m not entirely sure about this, but it appears to work for me. It doesn’t entirely make sense though, so I suspect that there is something better that can be done here.

Now you’re ready to setup up your odbc drivers and data sources.

You’ll need to create two files, tds.driver.template and tds.dsn.template. It doesn’t really matter where you create them, as they are templates to create your odbcinst.ini and odbc.ini files, but it’s worth putting them somewhere safe so that you can refer back to them later.

touch tds.driver.template
touch tds.dsn.template

Edit them and add the following, adjusted for your servername, dbname etc.

# tds.driver.template
[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so

# tds.dsn.template
[my_dsn]
Description     = Connection to windows virtual machine
Driver          = TDS
Trace           = No
Database        = my_database_name
Server          = MY-SERVER
Port            = 1433
TDS_Version     = 8.0

Note that in the driver template you are naming a driver. You then use that name in the dsn template as the value of the driver. This can be any name you want instead of TDS, but they must match. Alternatively, you can specify the path to the driver in the dsn template.

Now you are ready to install the two templates. You’ll need to run odbcinst for each template. Running odbcinst without any arguments will give you some useful info, but I’ll summarise what you need to know to get it working.

 sudo odbcinst -i -d -f tds.driver.template
 # -i says you want to install
 # -d says you are installing the driver
 # -f says what template you are using

 sudo odbcinst -i -s -l -f tds.dsn.template
 # -i says you want to install
 # -s says you want to install dsn
 # -l says you want to install system dsn
 # -f says what template you are using

These will copy the contents of the templates to /etc/odbcinst.ini and /etc/odbc.ini respectively. They also perform some piece of magic that I haven’t worked out yet (eg. if you duplicate the entry it adds to odbc.ini and change the name slightly, it doesn’t seem to work). However adding a new entry using odbcinst does work.

With the second call to odbcinst, you have a choice between installing system dsn to /etc/odbc.ini using -l or user dsn in ~/.odbc using -h. I recommend system dsn for development, and doing more research for production.

Don’t forget that you might have to add your db-server to your hosts file, depending on your network setup. Just edit /etc/hosts and add the ip address and name of your server underneath the entry for localhost.

Now you should be able to run queries on your database, using isql.

isql my_dsn username password

This should bring up an SQL prompt that allows you to execute queries on the server. Try a couple of queries to test it. If you don’t have a legacy database with data already in it, why the hell are you using SQL Server?

Connecting Python to SQL Server through pyodbc and freetds on Linux

You should now be ready to get python sorted out. If you’ve had any problems so far, let me know and I’ll try to help.

First, you need a python installed. I’ve installed 2.6 from source, which I’ll briefly cover, but you should also be able to use the python2.5 package that’s available.

In order to have all the functionality of python when installing from source, you will need some extra libraries:

sudo aptitude install libncursesw5-dev libreadline5-dev libssl-dev \
libgdbm-dev libbz2-dev libc6-dev libsqlite3-dev libdb-dev tk-dev
# That's all on one line, not two

I’ve used checkinstall to install python as a package. Alternatively you can install it to your home folder by passing --prefix=/home/username to configure.

To install it to your home folder, just download the source from the python website, unzip it, configure it, then use checkinstall.

wget http://www.python.org/ftp/python/2.6.2/Python-2.6.2.tgz
tar zxvf Python-2.6.2.tgz 
cd Python-2.6.2/
./configure
make
sudo checkinstall

I also strongly recommend using virtualenv and virtualenvwrapper. Create a virtualenv as soon as you have installed python and always create your other virtualenvs from that, so that you can easily get back to a clean python if you need to. (I’ll add instructions for that as a seperate post sometime). Install pyodbc into your virtualenv:

    easy_install pyodbc

If you installed the build-essential and unixodbc-dev earlier you should have what you need to compile it.

Now checkout the 0.6 branch of SQLAlchemy. This is currently a branch but should soon move to trunk. It’s fine for development. If you are using this as a guide to setting up your production server, you are insane.

svn checkout http://svn.sqlalchemy.org/sqlalchemy/branches/rel_0_6
# Make sure your virtualenv is activated then:
cd rel_0_6
python setup.py develop

You should now be able to run some basic tests using SQLAlchemy:

>>> import sqlalchemy as sa
>>> uri = "mssql://username:password@my_dsn"
>>> engine = sa.create_engine(uri)
>>> select_query = sa.text("SELECT * FROM MyTable")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[]
>>> insert_query = sa.text(
    "INSERT INTO MyTable (id, text_field) VALUES (:id, :text_field)")
>>> result = engine.execute(insert_query, 
        id="Brian", text_field="Naughty Boy")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[('Brian', 'Naughty Boy')]

That should all work. At this point you should probably try running some other tests. Particularly ones involving non-ascii chars. Then you can watch it crumble and die. (Actually if you’ve installed it correctly, it should work with non-ascii chars).

The UK Pound Sign (£) is a particularly good symbol to use for testing as it has a different byte representation between latin-1 and utf-8.

This is a draft document, and will be updated as and when I discover more. The Mac instructions are very draft as I haven’t had a chance to repeat them (I haven’t worked out how to run OS X Leopard in a virtual machine yet).

I have unfortunately had to work with a legacy SQL Server database, and was reluctant to use Windows to do the development. As such I have been trying to get my Mac development machine and a Linux server to connect to the database. It has been quite an adventure, and this is what I think I have worked out so far.

Installing freetds on Mac

I used MacPorts for installing packages. It’s not perfect but it’s a lot easier than installing from source when there is a lot of dependencies, and quite a bit of complex configuration has often been done for you.

Install freetds with the +mssql and +odbc variants.

sudo port install freetds+mssql+odbc

Also install unixODBC

sudo port install unixODBC

You now have a folder /opt/local/etc/freetds/ which should contain two templates tds.driver.template and tds.dsn.template as well as freetds.conf. You shouldn’t need to edit freetds.conf, although you can choose to specify the global tds version and charset (I have done neither of those and it works for me, so best leave it alone unless you have specific problems).

If you don’t have tds.driver.template or tds.dsn.template, create them.

sudo touch tds.driver.template
sudo touch tds.dsn.template

Edit them and add the following, adjusted for your servername, dbname etc.

# tds.driver.template
[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /opt/local/lib/libtdsodbc.so
Setup           = /opt/local/lib/libtdsS.so

# tds.dsn.template
[my_dsn]
Description     = Connection to windows virtual machine
Driver          = TDS
Trace           = No
Database        = my_database_name
Server          = MY-SERVER
Port            = 1433
TDS_Version     = 8.0

Note that in the driver template you are naming a driver. You then use that name in the dsn template as the value of the driver. This can be any name you want instead of TDS, but they must match. Alternatively, you can specify the path to the driver in the dsn template.

Now you are ready to install the two templates. You’ll need to run odbcinst for each template. Running odbcinst without any arguments will give you some useful info, but I’ll summarise what you need to know to get it working.

 sudo odbcinst -i -d -f tds.driver.template
 # -i says you want to install
 # -d says you are installing the driver
 # -f says what template you are using

 sudo odbcinst -i -s -l -f tds.dsn.template
 # -i says you want to install
 # -s says you want to install dsn
 # -l says you want to install system dsn
 # -f says what template you are using

These will copy the contents of the templates to /Library/ODBC/odbcinst.ini and /Library/ODBC/odbc.ini respectively. They also perform some piece of magic that I haven’t worked out yet (eg. if you duplicate the entry it adds to odbc.ini and change the name slightly, it won’t work). However adding a new entry using odbcinst does work.

With the second call to odbcinst, you have a choice between installing system dsn to /Library/ODBC/odbc.ini using -l or user dsn in ~/.odbc using -h. I recommend system dsn for development, and doing more research for production.

Now you should be able to run queries on your database, using isql.

isql my_dsn username password

This should bring up an SQL prompt that allows you to execute queries on the server. Try a couple of queries to test it. If you don’t have a legacy database with data already in it, why the hell are you using SQL Server?

Connecting Python to SQL Server through freetds on Mac

You should now be ready to get python sorted out. If you’ve had any problems so far, let me know and I’ll try to help.

First, you need a python installed. I’m using python2.6, but 2.5 should work fine, and maybe 2.4. I’m not going to go into installing python in too much depth, but I recommend not using the Mac system python, and also not using MacPorts to install python. I also strongly recommend using virtualenv and virtualenvwrapper. Create a virtualenv as soon as you have installed python and always create your other virtualenvs from that, so that you can easily get back to a clean python if you need to.

Install pyodbc into your virtualenv:

    easy_install pyodbc

Pyodbc needs to be compiled, so you will need a version of GCC. Either install the latest version through MacPorts (this takes a very long time) or install XCode which includes Apple’s own version of GCC. You may also need the python-dev headers and/or the unixodbc-dev headers, depending on how you installed python and unixodbc.

If you have gcc errors and they include the string “-arch i386 -arch ppc” somewhere in them, then python is probably trying to build a universal build but doesn’t have the ppc headers. Edit /lib/python2.x/config/MakeFile and delete every instance of “-arch ppc”. (This is assuming you are running an Intel Mac of course).

Now checkout the 0.6 branch of SQLAlchemy. This is currently a branch but should soon move to trunk. It’s fine for development. If you are using this as a guide to setting up your production server, you are insane.

svn checkout http://svn.sqlalchemy.org/sqlalchemy/branches/rel_0_6
# Make sure your virtualenv is activated then:
cd rel_0_6
python setup.py develop

You should now be able to run some basic tests using SQLAlchemy:

>>> import sqlalchemy as sa
>>> uri = "mssql://username:password@my_dsn"
>>> engine = sa.create_engine(uri)
>>> select_query = sa.text("SELECT * FROM MyTable")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[]
>>> insert_query = sa.text(
    "INSERT INTO MyTable (id, text_field) VALUES (:id, :text_field)")
>>> result = engine.execute(insert_query, 
        id="Brian", text_field="Naughty Boy")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[('Brian', 'Naughty Boy')]

That should all work. At this point you should probably try running some other tests. Particularly ones involving non-ascii chars. Then you can watch it crumble and die.

The UK Pound Sign (£) is a particularly good symbol to use as it has a different byte representation between latin-1 and utf-8.

I’ve been reading about CouchDB on and off for a few months now, and recently decided to properly evaluate it as an option for my new website. This decision weirdly coincided with the release of Google AppEngine which uses their Bigtable system, which is vaguely similar to CouchDB (non-relational, document based, flexible and scalable).

Anyway, I tried to install CouchDB on Leopard, and hit a few minor issues which I thought I’d document for others trying to install it.

I installed from Macports (always a good first stop). There appears to be a missing dependency on SeaMonkey, so you need to do:

sudo port install seamonkey
sudo port install couchdb

When you first start couchdb it complians that: CouchDB needs write permission on the data directory: /opt/local/var/lib/couchdb or CouchDB needs write permission on the log directory: /opt/local/var/log/couchdb.

I tried doing sudo couchdb to get over that, and couchdb seemed to start happily, and there was a response from http://localhost:5984/. However, when I inserted my first document using couchdb-python, python hung completely with no repsonse (I left it for 10 minutes while I made a coffee). In retrospect, sudo couchdb probably wasn’t the correct way to go (particularly as those directories don’t even exist), but I was keen to start playing. The correct thing to do is probably to create a couchdb user that runs the database and do sudo -u couchdb couchdb to start the database. Unfortunately I’m quite lazy, so instead I just did:

sudo mkdir -p /opt/local/var/lib/couchdb
sudo chown ed /opt/local/var/lib/couchdb
sudo mkdir -p /opt/local/var/log/couchdb
sudo chown ed /opt/local/var/log/couchdb

Where ed should be replaced by your username. mkdir -p recursively makes directories (incase you don’t have /opt/local/var/lib/ or /opt/local/var/log) yet.

Anyway, hope this helps someone else, if they have problems.

I’ve been playing around with Google’s new hosted Python framework. I created a very basic wiki called WikiWikiWoo! just to get a feel of the system.

It’s based on Django and Paste and seems to be inspired by bits of web.py and maybe bits of Werkzeug.

The templating is directly Django’s, which I think is a shame as Jinja is very similar but better (vaguely useful error messages for a start).

The model is inspired by Django, but using Google’s Datastore (not an rdb), using GQL, their modified version of SQL. It wasn’t easy to get going with, which is partly the fault of the documentation (it took me ages how to work out how to do their version of a primary key).

Once you get the hang, it’s pretty simple for quick simple data. It would be tough going to work out how to do complex relationships with it, particularly for anyone from a traditional rdb background (like me). There’s no section where they explain how their datastore works and the concepts behind it. I’ve been playing with Couchdb recently and they did a wonderful job of explaining the overall concept before going into how to use it. That kind of conceptual overview for Datastore would probably make a world of difference.

The controllers seem to be inspired by Web.py. They are classes with methods for POST, GET, etc. I like that system from playing around with Web.py. It allows you to very easily use the same URL for displaying an edit form and saving an item. It keeps your code nicely grouped, but I think it would make it a little more difficult to create generic CRUD controllers (maybe having to use meta-class magic).

The routing seems based on a corrected version of Web.py’s. It fixes the annoying list of tuples into a proper list of lists of tuples. It uses basic regexs for routing (first one that matches wins), and a direct reference to a class. This means the routing has to go at the end of the module (which is different to most other frameworks). I like it and it’s exactly how I set up the routing when I was messing around with WSGI.

Sessions and deployment are the killer features. Deployment is just a single command that uploads all your files, and restarts your app. Session handling uses the Google login system, so sessions are completely abstracted away. These are often the hardest bits to get right when using a new framework, so they’ve done impressively well to make them so simple.

Overall, I’d say it could be outstanding for quickly knocking out an app you’ve just thought of. A simple app will scale beautifully if it suddenly becomes popular (no more slashdotting) and they’ve solved the more annoying problems for webapps. I would have some concerns about trying to create a huge complex app using it, but we’ll see. Certainly I’ll use it again if I ever have a good, simple idea. For my bad, complex ideas I’ll continue building my own framework

---

As an update, it appears you can use almost any Python modules in your code as long as you add them into your project folder (either copy the package or if you use a symlink it will copy them automatically). That would mean that everything hinges on their storage. Sessions and deployment are excellent, and everything except for the model you can do yourself. If their storage is easy to use with potential longterm power, then it could big, and a serious choice for hosting major sites.

The only other issue would be one of branding. As the login makes it clear that you are using a Google AppEngine, I don’t see big companies using it. That’s a good thing as it gives us open minded charities a leg up on the over-resourced private sector.

I’ve been messing around with Python web frameworks for a quite a while now. Trying out lots of different ones and seeing which one feels like the best fit. As I’ve got closer and closer to the right one, I’ve also found that i am getting closer and closer to using WSGI.

WSGI seems excellent and could be great for Python. Frameworks tend to make things seem more difficult and mysterious than they really are. What has taken me a long time to learn is that WSGI is really very simple.

At it’s simplest there are three aspects to it:

• You receive a dictionary with useful information in it (environ)
• You receive a function to call that starts the response (start_response)
• You return an iterator (ie a list) of strings (this is your page body)

Anything on top of that is just helpers. Helpers are great, but you don’t need a framework that hides what is really going on, when it’s as simple as that.

You can create a hello world app as simple as:

def app(environ, start_response):
    start_response('200 OK', [('content-type', 'text/html')])
    return ["Hello World"]

Obviously that just returns Hello World with no html. Which is bad. So:

def app(environ, start_response):
    start_response('200 OK', [('content-type', 'text/html')])
    begin_body = "<html><body>"
    body = "Hello World"
    end_body = "</body></html>"
    return [begin_body, body, end_body]

We don’t want to write begin_body and end_body every time, so lets write a function to handle this for us:

def put_in_body(args):
    begin_body = "<html><body>"
    end_body = "</body></html>"
    return [begin_body] + list(args) + [end_body]

Then we can just:

def app(environ, start_response):
    start_response('200 OK', [('content-type', 'text/html')])
    return put_in_body("Hello World")

We’re going to want to be able to do lots of different things, and we don’t want to write them all in the body of one function, so let’s write a dispatcher:

def dispatch(environ):
    # We need to get the path from the environ, and work out where to go.
    path_info = environ.get('PATH_INFO', '')
    if path_info == "/goodbye":
        return goodbye(environ)
    else:
        return hello_world(environ)


def hello_world(environ):
    return ["Hello World"]

def goodbye(environ):
    return ["Goodbye World"]

def app(environ, start_response):
    start_response('200 OK', [('content-type', 'text/html')])
    return put_in_body(dispatch(environ))

Now if you go to /goodbye it will say “Goodbye World”, otherwise it will say “Hello World”.

As you can see it is all very straightforward. It allows you to do things however you want. Adding more and more functionality is just a case of adding functions that returns strings, and telling the dispatcher when to call that function.

In a later post I’ll talk about threading. It turns out that’s easy as well.

If you want to run the code above, just easy_install paste and then add this to the bottom of your file (which I called wsgi_sample.py):

if name == 'main':
    from paste import httpserver
    httpserver.serve(app, host='127.0.0.1', port='8080')

Then you can do python wsgi_sample.py and visit http://localhost:8080 to see it working (don’t forget to visit http://localhost:8080/goodbye before you leave).

Stumbled across an interesting web framework called RIP, by Luke Arno. It seems to be made up of pieces he’s all made himself.

Going through the obligatory quick wiki example, I like the style of it. Straightforward, obvious, no magic (one of his stated aims). It’s only 0.1 at the moment but I’m going to keep an eye on it; maybe steal some bits.

It does use a very frameworky style. It has a command-line tool that generates loads of boilerplate for you (just like TurboGears). Not keen on that as a style. Having played with web.py and Arc recently, I really like the idea of starting with a single blank file and adding in what you need from there, rather than starting with a large number of files. (It’s possibly no coincidnce that both of those have some sort of link back to Paul Graham).