Spaces in Column Names (MSSQL)
Kipb
Our MS SQL legacy database has column names with spaces and sometimes
other special characters. I'm able to get sqlalchemy to work with it
by having every column object include quote=True, and by having the
mapper() call include:
,properties= {'NameJammed':TableObj.c['Name Jammed'],
'AnotherCol':TableObj.c['Another (Col)']} #etc
I can use .NameJammed to access the column in my code just fine.
While I can do this, it's unfortunate under the DRY (Don't Repeat
Yourself) philosophy, as we have a long mapper call for each table
with each column name in there essentially twice (besides the initial
definition.)
Mysteries:
1) the key= parameter on the Column() declaration doesn't seem to have
any effect. orm/__init__.py's mapper function says under the
'properties' parameter:
"Note that the columns in the mapped table are automatically converted
into ``ColumnProperty`` instances based on the `key` property of each
``Column`` (although they can be overridden using this dictionary)"
2) Column's quote=True should not be required according to schema.py/
Column/__init__ 'quote' parameter:
"indicates that the Column identifier must be properly escaped and
quoted before being sent to the database. This flag should normally
not be required as dialects can auto-detect conditions where quoting
is required"
Perhaps I need to update mssql.py to override the right methods, which
is pretty intimidating.
Can you confirm that, for your database, column names with spaces are
automatically quoted (no quote=True required) and that Column's key=
works to give a python-identifier name to that column name (no mapper
properties required)?
Any other illumination? I'd love to have a function auto-generate the
"column name without spaces" identifier that I use in my code, too,
for even less Repeating Myself.
Michael Bayer
On Mar 25, 2008, at 9:02 PM, Kipb wrote:
> Can you confirm that, for your database, column names with spaces are
> automatically quoted (no quote=True required) and that Column's key=
> works to give a python-identifier name to that column name (no mapper
> properties required)?
quote=True should not be needed, that is automatic....this stuff
should work for MS-SQL as well though I dont have a test environment
here for MS-SQL Heres a sqlite script illustrating both concepts in
action:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
meta = MetaData(engine)
t1 = Table('some table', meta,
Column('primary col', Integer, key='id', primary_key=True),
Column('the data', String(50), key='data')
)
t1.create()
class Foo(object):
def __eq__(self, f):
return f.id == self.id and f.data == self.data
mapper(Foo, t1)
sess = create_session()
f = Foo()
f.data = "some data"
sess.save(f)
sess.flush()
sess.clear()
assert sess.query(Foo).filter_by(id=f.id).one() == f
> Any other illumination? I'd love to have a function auto-generate the
> "column name without spaces" identifier that I use in my code, too,
> for even less Repeating Myself.
do something like this, using column overrides at the mapper level:
from sqlalchemy.orm import mapper as sa_mapper
def mapper(cls, tbl, **kwargs):
for c in tbl.c:
key = c.name.replace(' ', '_')
kwargs[key] = c
return sa_mapper(cls, tbl, **kwargs)
to do it at the Table level, I'd build a similar function that
decorates Column to manufacture a "key" argument. If you were using
reflection (i.e. autoload=True), thats a little more tricky, it might
require copying the Table to a new one to do it in an "automated"
fashion.
Rick Morrison
quote=True should not be needed, that is automatic....this stuff
should work for MS-SQL as well though I dont have a test environment
here for MS-SQL Heres a sqlite script illustrating both concepts in
action:
Where are the default Dialect quoting rules pertaining to spaces? I'm presuming in sql/compiler.py:IdentifierPreparer, but I don't see the regexp expressions for spaces there.....
As far as I know, the MSSQL Dialect doesn't override the normal quoting rules, but the Dialect interface has changed since I last spent any significant time working with it, so I'm not really all that sure about it.
Rick
Michael Bayer
On Mar 26, 2008, at 1:49 PM, Rick Morrison wrote:
>
> quote=True should not be needed, that is automatic....this stuff
> should work for MS-SQL as well though I dont have a test environment
> here for MS-SQL Heres a sqlite script illustrating both concepts in
> action:
>
>
> Where are the default Dialect quoting rules pertaining to spaces?
> I'm presuming in sql/compiler.py:IdentifierPreparer, but I don't see
> the regexp expressions for spaces there.....
the regexp is compiler.LEGAL_CHARACTERS, and is used within
IdentifierPreparer._requires_quotes.
> As far as I know, the MSSQL Dialect doesn't override the normal
> quoting rules, but the Dialect interface has changed since I last
> spent any significant time working with it, so I'm not really all
> that sure about it.
can you try my test script with an MS-SQL database and see what you
get ?
Rick Morrison
can you try my test script with an MS-SQL database and see what you
get ?
Here's the echo output:
2008-03-26 15:11:41,342 INFO sqlalchemy.engine.base.Engine.0x..d4
CREATE TABLE [some table] (
[primary col] INTEGER NOT NULL IDENTITY(1,1),
[the data] VARCHAR(50),
PRIMARY KEY ([primary col])
)
2008-03-26 15:11:41,343 INFO sqlalchemy.engine.base.Engine.0x..d4 {}
2008-03-26 15:11:42,139 INFO sqlalchemy.engine.base.Engine.0x..d4 COMMIT
2008-03-26 15:11:42,397 INFO sqlalchemy.engine.base.Engine.0x..d4 BEGIN
2008-03-26 15:11:42,408 INFO sqlalchemy.engine.base.Engine.0x..d4 INSERT INTO [some table] ([the data]) VALUES (%(data)s)
2008-03-26 15:11:42,408 INFO sqlalchemy.engine.base.Engine.0x..d4 {'data': 'some data'}
2008-03-26 15:11:42,459 INFO sqlalchemy.engine.base.Engine.0x..d4 COMMIT
2008-03-26 15:11:42,472 INFO sqlalchemy.engine.base.Engine.0x..d4 SELECT TOP 2 [some table].[primary col] AS [some table_primary col], [some table].[the data] AS [some table_the data]
FROM [some table]
WHERE [some table].[primary col] = %(some table_primary col_1)s ORDER BY [some table].[primary col]
2008-03-26 15:11:42,473 INFO sqlalchemy.engine.base.Engine.0x..d4 {'some table_primary col_1': 1}