Pyramid+sqlalchemy+MSSQL fails to create the scaffold table

[arikb]

Hello folks,

I've been trying to figure this one out for some time. Here's the stack:

Pyramid 1.3.2
SQLAlchemy 0.7.8
pyodbc-3.0.6
unixODBC 2.2.14
freetds 0.91
Python 2.6.6
CentOS 6.2

Connecting to... an MS SQL Server 2008. Don't ask. No It's not my choice. No I can't switch. I thought I told you not to ask.

I've set up the "sqlalchemy" scaffold and when trying to initialize the database I get the following error:

Column 'name' in table 'models' is of a type that is invalid for use as a key column in an index.

Tracking it down, the SQL generated by SQLAlchemy is:

CREATE TABLE models (
        id INTEGER NOT NULL IDENTITY(1,1),
        name TEXT NULL,
        value INTEGER NULL,
        PRIMARY KEY (id),
        UNIQUE (name)
)

It seems like the SQL Server doesn't like the UNIQUE condition on the [name] column. If I change the TEXT to VARCHAR(100) it works. Researching further, I found that SQL Server implements the UNIQUE condition with an index and wouldn't take a type that has no size limit as an index member because of a limit on the index column size.

My question is: I'm a really new SQLAlchemy user. I thought SQLAlchemy's job is to abstract those differences between the back-end engines away and take care of these things. Is this what I should expect from a multiple engine ORM - that I should expect things to break on different back-ends - or is this an anomaly?

I've used Django's ORM before on postgres, sqlite and SQL Server and I wasn't able to make it break in this way.

-- Arik

[Yuval Adam]

Well, ORMs should - and do - abstract away the important stuff.
But as you can see, there are things that certain DBs just don't support.

Specifically, it's uncommon to have a TEXT field defined as UNIQUE, and MSSQL's limitation on this definitely makes sense.

Bottom line, an ORM is there to help you work with proper DB-backed objects, not to let you change your DB every other day :)

--
You received this message because you are subscribed to the Google Groups "PyWeb-IL" group.
To view this discussion on the web visit https://groups.google.com/d/msg/pyweb-il/-/qlIqYH-13p8J.
To post to this group, send email to pywe...@googlegroups.com.
To unsubscribe from this group, send email to pyweb-il+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/pyweb-il?hl=en.

--

Yuval Adam
http://y3xz.com

[Shai Berger]

Sorry about the delay and waking up an old thread; I just ran into this, and
wanted to comment:

Yuval,

On Wednesday 04 July 2012 12:59:19 Yuval Adam wrote:
> Well, ORMs should - and do - abstract away the important stuff.
> But as you can see, there are things that certain DBs just don't support.
>
> Specifically, it's uncommon to have a TEXT field defined as UNIQUE, and
> MSSQL's limitation on this definitely makes sense.
>

Agreed.

> Bottom line, an ORM is there to help you work with proper DB-backed
> objects, not to let you change your DB every other day :)
>

Disagreed. I'm with Arik on:

> > I thought SQLAlchemy's job is to abstract those differences between the
> > back-end engines away

Now, Arik,

> On Wed, Jul 4, 2012 at 11:52 AM, arikb <arik....@gmail.com> wrote:
> >
> > Pyramid 1.3.2
> > SQLAlchemy 0.7.8
> > pyodbc-3.0.6
> > unixODBC 2.2.14
> > freetds 0.91
> > Python 2.6.6
> > CentOS 6.2
> >
> > Connecting to... an MS SQL Server 2008. Don't ask. No It's not my choice.
> > No I can't switch. I thought I told you not to ask.
> >

You should be aware (and make the powers in charge of the decisions aware)
that most users make other choices, and so you should expect more bugs and
missing features for the combination.

I found myself in a similar situation, and the decision they took was to let
me fix parts of the open-source infrastructure on their dime (that's part of
the beauty of open-source). I've been effectively maintaining the MSSQL backend
of South since then, and I have been (and am) grateful for this opportunity.

Per your specific issue:

> > I've set up the "sqlalchemy" scaffold and when trying to initialize the
> > database I get the following error:
> >
> > Column 'name' in table 'models' is of a type that is invalid for use as a
> > key column in an index.
> >
> > Tracking it down, the SQL generated by SQLAlchemy is:
> >
> > CREATE TABLE models (
> >
> > id INTEGER NOT NULL IDENTITY(1,1),
> > name TEXT NULL,
> > value INTEGER NULL,
> > PRIMARY KEY (id),
> > UNIQUE (name)
> >
> > )
> >

It seems like between Pyramid and SQLAlchemy, someone decided that the proper
datatype for the "name" column is TEXT; this is quite obviously wrong.

> >
> > My question is: I'm a really new SQLAlchemy user. I thought SQLAlchemy's
> > job is to abstract those differences between the back-end engines away
> > and take care of these things. Is this what I should expect from a
> > multiple engine ORM - that I should expect things to break on different
> > back-ends - or is this an anomaly?
> >

This is a bug. When running the same code on different database backends, you
should expect things to work generally -- but less so on the less-used
options; thus, a databse backend that almost nobody uses (with Pyramid, that
is) will have more problems than a "mainstream" one.

HTH,
Shai.