PyODBC, UnixODBC, FreeTDS – config

We wanted to move our Pylons application from Windows to Linux (hosted on Westhost), and still connect to our MS SQL database out there. It’d be simpler and faster to use a local MySQL also hosted on Westhost, perhaps, but that’ll come later.

SQLAlchemy (that we’re using in Pylons for database access) will work with PyMSSQL, PyODBC, and adodbapi. We’re using PyODBC (because PyMSSQL couldn’t connect with integrated security, and didn’t look into adodbapi.)

UnixODBC is a “Driver Manager.” This must be built first.
Easysoft has an “ODBC-SQL Server Driver” ($$). FreeTDS is an alternative Driver. One of these is built next.
PyODBC is the Python-specific part, and must be built against a driver manager (unixODBC). This is built last.

We’re using SQL Server 2005, so we need TDS Version 8.0.

Problem: Couldn’t get jsonlib installed on Westhost (easy_install didn’t support https), so we switched to simplejson. Not too hard except that we needed to support “ordered dictionaries” so that json output would be in the same order as we put items in a hash {’a':1,’b':2} for example. Doesn’t really matter but it makes life easier when debugging or reading json. We updated to simplejson 1.9.1 along the way, and had to update the python path in .htaccess mod_python set-up, which is now in /etc/httpd/conf/httpd.conf.

Problem: Apache/mod_python couldn’t import pyodbc. The problem was environment variable LD_LIBRARY_PATH which didn’t seem to include /usr/local/lib. You can’t set this from within Python so you must put it in /etc/ld.so.conf — hey, it was already there! Then you do ldconfig. This fixed the problem.

Problem: Couldn’t get “whole thing” (access database from SQL Alchemy) working, so needed to break apart and test each part freetds/unixodbc/pyodbc/sqlAlchemy separately. Fortunately, you can test just freetds, without using /usr/local/etc/freetds.conf by doing tsql -H mydbhost.com -p 1433 -U myuser -P mypasswd, and when you get that working, edit your freetds.conf to put in a server. You should set up a trace file in the [global] section (but be sure to remove it before running any serious traffic!) dump file = stdout will be useful. Add lines like
[myServer]
host = mydbhost.com
port = 1433
tds version = 8.0

Now you can test using tsql -S myServer -U user -P passwd (omit -H -p). This tests what’s in freetds.conf.

Next test with isql which uses unixODBC. You can edit /usr/local/etc/odbcinst.ini to put in
[TDS]
Description = FreeTDS Driver
Driver = /usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsS.so
UsageCount = 1

And in odbc.ini, create a DSN (you don’t actually need to use DSN’s but that’s what I did.)
[MyDSN]
Description=my dsn
Driver=TDS
Database=yourdb
Servername=myServer

Do NOT start any lines in odbc.ini with spaces — cost me a few hours!

Now on to pyodbc. You can test with:
$ python
import pyodbc
cnxn = pyodbc.connect("DSN=MyDSN;UID=user;PWD=pwd")
cursor = cnxn.cursor()
cursor.execute("select 2+2 as [Result]")
for row in cursor:
  print row.Result

And finally to test SQL Alchemy:
$ python
import sqlalchemy as sa
eng = sa.create_engine("mssql://usernm:pwd@/?dsn=MyDSN",echo=True)
res = eng.execute('select 1+1 as foo')
for row in res:
  print 'answer=',row['foo']


You might want to do some of these tests within the Apache/mod_python context The main problems I had along those lines were Python’s path (solved in earlier post in .htaccess or in httpd.conf) and LD_LIBRARY_PATH solved above.

New problem: Doing an update works in isql but fails in sqlalchemy, saying there is a datatype incompatibility (updating int with varchar).

Summary of free software layers we’re using in this project:
pylons (mako, sqlalchemy, simplejson), dojo, python, apache, mod_python, pyodbc, simplejson, unixODBC, freetds. Probably a dozen more I’ll remember later! We’re using Wing IDE Pro ($$) for editing under Windows, and vim for editing on westhost.

Pylons on Westhost

How to get Pylons working on Westhost 3.0 (WH).
This assumes you have SSH (like telnet) access to your Westhost account, but you can do most of this using FTP or the westhost control panel file manager, if you prefer.

We had previously tried and failed to install pylons (prior to Westhost 3.0), so decided to use Virtual Python instead, where you can change whatever you like. It makes ~/bin ~/include ~/lib to replace the system versions, and when you run python, it runs ~/bin/python and uses your lib etc.
Just grab virtual-python.py and run it. (It’s possible you don’t need Virtual Python under WH 3.0, don’t know.)

Now you can get ez_setup.py and do python ez_setup.py which gives you easy_install, which you’ll use for installing most other things. Then do easy_install -U setuptools, easy_install pylons, easy_install sqlalchemy. These all went into ~/wherever (not the system directories.)

Now you can cd ~/www and paster create -t pylons prj
which creates a project called prj (our version of helloworld). cd prj and edit development.ini to put in your domain name (yourdomain.com) instead of 0.0.0.0 and change the port from 5000 to 81. You can serve your new site with: paster serve –reload development.ini
(~/www/prj/prj/public/index.html is the default page).

With your web browser, go to http://yoursite.com:81/ and you should see your new site.

How do we get this served from the usual Apache 2.0 (so we don’t have to run paster) ?

Pylons can run using mod_python, which is a way for Apache to include the Python interpreter.
Westhost 3.0 already supports mod_python. You can verify this by adding these lines to /etc/httpd/conf/httpd.conf: <Location /mpinfo>
SetHandler mod_python
PythonHandler mod_python.testhandler
</Location>
You can apachectl restart to restart your web server (or Restart Account from WH Site Manager).
And then with your web browser, going to http://yoursite.com/mpinfo

See pylonshq.com, Production deployment using mod_python and put wsgi.py into your ~/lib/python2.5/site-packages/mod_python/ folder (I actually put it into /usr/local/python/lib/python2.5/site-packages/mod_python).

cp development.ini production.ini #copy file
edit production.ini #using vi, pico, whatever you know
remove comment character from line: #set debug=false (so now it will set debug=false)
create startup.py containing these two lines:

from paste.deploy import loadapp
app = loadapp("config:/var/www/html/prj/production.ini")

create .htaccess containing:

SetHandler mod_python
PythonHandler mod_python.wsgi
PythonPath "['/var/www/html/prj', stuff explained below] + sys.path"
PythonOption wsgi.application startup::app
PythonOption SCRIPT_NAME /prj


The _stuff explained below_ is the path info that your new python has:
python #run your virtual python
import sys
print sys.path
copy all the path places you see into _stuff_ above, except the first empty one ‘ ‘.

Now you can go to your web browser:
http://www.yourdomain.com/prj/ #and you should see the default page ~/www/prj/prj/public/index.html, this time served by Apache.

It answers on port 80, regardless of the domain/port in your production.ini, which is only used by paster.

JSON Dates and Dojo Grid identifier=primary key for multiple columns

Problem: JSON doesn’t specify a date format.  Dojo wants json dates in the form {_type:’Date’,_value:’2007-12-31T12:34:56.789′}.  Using jsonlib.write’s on_unknown handler, we can test for the datetime type and format it in this way.  On the way back, when items are changed in the browser, we did Date.prototype.json = function() {return dateFormat(this)}, so that dojo.toJson would know how to format dates.  This means that “json” is using two different formats for the two directions (an object on the way to browser and a string on the way back to server.)  The string format for the date itself is the same in the two directions (which took some work since dojo appends the time zone offset but Python’s datetime’s .isoformat function does not, and there are some milliseconds/microseconds issues.)

Next problem: Dojo grid (ItemFileReadStore, actually) likes to have an ‘identifier’ column, which is used to quickly find a row (’item’).  We have some tables that have multi-column primary keys (PKs).  I had first just been lazy and used the 1st column of the PK, but Dojo was smart enough to see that wasn’t unique when I used real data.  I looked at perhaps a hundred dojo grid data examples, and EVERY ONE had a single-column primary key.  However, you can use an OBJECT for identifier, and if it has a .toString() method then you might be able to have it combine the primary key columns on-the-fly {x:10,y:20,toString:function(){return this.x+’|'+this.y}}.  I’m thinking this will be messy because 1) jsonlib won’t know how to emit a javascript function; 2) didn’t figure out how the object’s toString function references the item’s data columns (does this.pk0 work?); 3) what if we change a PK’s value?  “ItemFileWriteStore does not have support for changing the value of an item’s identifier“.

The plan at the moment is one of: 1) construct our own new one-column primary key that is a concatenation of the actual primary key columns, such as NJ|Trenton.  This won’t be shown on the grid, and for now, there is no editing of PK values anyway.  2) just use a sequence number and fix when we finally have some use for the identifier; 3) switch to the ‘table’ format where we don’t need an identifier.

Pylons, MSSQL, SQL Alchemy

We wanted to have JavaScript pass JSON notation {”such”:”as”,”this”:["example",2],”here”:{}} to Pylons and then have Pylons interpret it as a Python object.  Django includes a serializer but Pylons did not seem to (see note below.)  TurboGears has the @jsonify decorator.  There are several json packages out there we could use.  I typed easy_install jsonlib and it was ready to use.  import jsonlib and then either python_object = jsonlib.read(”"”{”some”:”json”}”"”) or string_output = jsonlib.write(python_obj). 

Note: it seems Pylons also has the @jsonify decorator (you put it in front of your action function) also see decorators/__init__.py, which uses simplejson, but I didn’t find it when I was looking for json and pylons!  I believe the default 0.9.6 Pylons also includes simplejson, so I didn’t need to get jsonlib.

Anyway, jsonlib works fine and it won’t be hard to switch if needed later on.

Next problem: We wanted to connect to a Microsoft SQL Server (MSSQL) using a “Trusted Connection” instead of username/password.  We were using PyMssql.py which worked fine up until then.  We struggled to figure out how to pass the “TrustedConnection=Yes” bit and not the UID=sa etc.  It turns out that pymssql is not capable of that, so we switched to pyodbc which does the trick.  There are yet other alternatives including a commercial one (mxodbc for $140) but this works for now.

Next problem: Some of our tables have integer primary keys, which properly should be auto-incrementing.  Due to issues when moving back and forth between SQL Servers, we made those tables NOT have auto-increment primary keys.  We would do Declare @x int  Select @x = 1+max([ID]) from Table_name and then Insert into Table_name Values (@x, …) which is risky in the case of two simultaneous inserts (2nd attempt may get a duplicate key error.)  In SQL Alchemy we used, in the Table and Column declaration, default=text (”1+(select max([ID]) from Table_name)”) but MS SQL won’t allow a Select in the value of an Insert.  Fortunately, SQL Alchemy (SA) lets you put preexecute_pk_sequences=True up at the top of class MSSQLDialect_pyodbc and that did the trick.  That makes it do the Select 1+Max… first and then use its result value in the Insert.  It seems that SA is doing a round-trip here as opposed to the @x variable approach above which doesn’t require an extra round trip.  This means it’s slower and more likely to have a collision but we’ll switch to true auto-increment someday!

More SQLAlchemy – column names with spaces, schema

A few struggles because we had spaces (and other funny characters) in our column names, and had a ’schema’ (formerly, owner) in ms sql.  Figured out how to automatically create a ‘key’ for each column: make a mapper shim function that removes the bad characters from the column name, making it into a valid Python identifier. From our model python source:

from sqlalchemy.orm import mapper as sa_mapper
import re
colname_omit_re = re.compile("[^A-Za-z0-9_]") #match funny chars to remove
def mapper(cls, tbl, **kwargs):
"Our own mapper that adds Python-identifier-suitable key for multi-word column names"
kprop = kwargs.get('properties',None) # use existing
for c in tbl.c:
  key = colname_omit_re.sub("",c.name) # omit bads
  if key != c.name:
    if not kprop: kprop = {}
    if not kprop.has_key(key): kprop[key] = c # allow override in mapper call
if kprop: kwargs['properties'] = kprop # set, if new
return sa_mapper(cls, tbl, **kwargs)


Each Column() specification (within Table()) had to include parameter schema=’albert’ (if albert was our schema!), and also when we used foreign keys: ForeignKeyConstraint(['Col Name'], ['albert.JoeTableName.Column Name'], name=”explanation of some kind”)

Pylons, SQLAlchemy

Installed SQLAlchemy: easy_install SQLAlchemy

Trying the tutorial at http://docs.pythonweb.org/display/pylonscookbook/Making+a+Pylons+Blog

Did all steps without incident using sqlite and the MyBlog folder name, etc. and the “blog” works fine. Where is the SQL magic happening? In the blog controller (blog.py) in its index function:

     q = model.Session.query(model.Blog)
     c.posts = q.limit(5)
     return render("/blog/index.html")

Now looking at sqlautocode, which needs pymssql.  Found and installed (using .zip for Python 2.5).  Modified autocode so I didn’t have to use command line, see below.  One problem was that the tables in the database were NOT in the default dbo schema, but in “myschema” (another name actually), so had to specify this:

#File autocode.py
import sqlautocode
command = "mssql://sqlusername:sqlpass@sqlserver.dummy.net/sqldatabase -s myschema -t tablewildcard* --noindexes --output out.py"
argv = ("autocode "+command).split(" ")
sqlautocode.main(argv) #File sqlautocode\__init__.py
#def main(myargv):
#    config.configure(myargv)

Tried it without –noindexes, but get “It seems that this dialect does not support indexes!”  Foreign keys seem OK so far, and schema is passed in the output file.

Dojo, Dojox, dojox.grid

Discovered some wonders of grids, charts, etc. using dojo and dojox.grid.  Looks to be cool.

On my Windows system, installed easy_install (you run one python program),
added python25/scripts onto my Windows path (control panel, system, advanced, environment variables),
installed pylons (paste one line into DOS command box). 
Installed helloworld example: paster create -t pylons helloworld
Started pylons server: paster serve –reload development.ini
and accessed a web page at http://localhost:5000/!

Continuing with the instructions in: http://wiki.pylonshq.com/display/pylonsdocs/Getting+Started
We start an application: paster controller hello
However, this gives an error: Command controller not known. Maybe I’m in the wrong subdirectory?
Ok, look at the web page from localhost:5000 above (index.html), which says to first
remove index.html, edit the routes in helloworld/config/routing.py to “point the root path to a hello controller we’ll create”
 map.connect(”, controller=’hello’, action=’index’)
and THEN do paster controller hello
This time, no error.  It created hello.py.

http://localhost:5000/hello now gives the output from this hello.py controller.

Django to Pylons?

We converted the existing database schema for Django, including adding numeric ID columns where we had multiple column keys.  Django is helpful in that you instantly get maintenance pages for the tables, for adding/changing/deleting records.  We’d also like to see joins and ‘maintain’ the data from that view, without having to go find the individual row in its own table. 

There were frustrations with Django, as I’m sure one would have with any new platform.  There is a “book” online, but several of the examples didn’t work and when looking online for help, it was hard to find the answers.  Django doesn’t seem to help much with reporting.  Our reports need multi table joins and summaries,

I was excited to read about SQLAlchemy and how it handles a lot of fancy database ideas we’d want.  Not available in Django, and only sort-of available in Turbogears.  Pylons does support it.  Pylons of course is less mature than the other two so no doubt we’ll run into issues, but now ready to try it out.