String column issue - wrong lenght/trailing spaces

[Werner F. Bruhin]

I have these two columns defined.

sa.Column(u'name', sa.String(length=30, convert_unicode=False)),
sa.Column(u'shortname', sa.String(length=10, convert_unicode=False)),

The name column works as expected but the shortname returns trailing blanks.

dt = session.query(db.Drinktype_Ls).get(1)
print dt
print dt.shortname
print len(dt.shortname)
print dt.name
print len(dt.name)

Drinktype_Ls(centralkey=1, created=datetime.date(2003, 10, 1),
drinktypeid=1, dtype=u'WINE', fk_drinktypexid=1, fk_langid=1,
iconname=u'glassRed ', id=1, name=u'Red Wine', shortname=u'Red ',
sortorder=1, updated=datetime.date(2008, 8, 13))
Red
10
Red Wine
8

I checked the db and I do not see any trailing spaces in that column.
Any idea what might cause this?

Best regards
Werner

[Kipb]

On Aug 14, 5:31 am, "Werner F. Bruhin" <werner.bru...@free.fr> wrote:
> I have these two columns defined.
>     sa.Column(u'name', sa.String(length=30, convert_unicode=False)),
>     sa.Column(u'shortname', sa.String(length=10, convert_unicode=False)),
> The name column works as expected but the shortname returns trailing blanks.

> ...

> I checked the db and I do not see any trailing spaces in that column.  
> Any idea what might cause this?

Perhaps the database has the trailing spaces but you don't see them
due to how you're looking.

In Microsoft SQL, using the SQL Server Management Studio,
create table testa ([pk] int primary key identity, [a] char(16))
insert into testa values ('x')
SELECT [pk], '/' + [a] + '/' as padA, len(a) as lenA from testa

Shows lots of spaces in padA, but len(a) is still 1.
(Can't reproduce this in SQLite, which treats char as varchar.)

As for SQLAlchemy:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mssql://myusername:mypa...@sql.example.com/
mydatabase', echo=True)
metadata = MetaData(bind=engine)
sm = sessionmaker(autoflush=True, bind=engine)
session = scoped_session(sm)
mapper = session.mapper

tabl = Table('testa', metadata,
Column('pk',Integer, primary_key=True),
Column('a',String(16)) )
class Tabl(object):
pass
mapper(Tabl, tabl)

q = session.query( tabl )
for obj in q:
print "#%d: '%s' len=%d" % (obj.pk, obj.a, len(obj.a))

#outputs: #1: 'x ' len=16

So SQLAlchemy says len([a])==16 but SQL directly says len([a]) is 1.

If you can say which database you have and show the columns'
schema definitions *from the database*, that may help too.

[Werner F. Bruhin]

Hi,

Kipb wrote:
> On Aug 14, 5:31 am, "Werner F. Bruhin" <werner.bru...@free.fr> wrote:
>
>> I have these two columns defined.
>> sa.Column(u'name', sa.String(length=30, convert_unicode=False)),
>> sa.Column(u'shortname', sa.String(length=10, convert_unicode=False)),
>> The name column works as expected but the shortname returns trailing blanks.
>> ...
>> I checked the db and I do not see any trailing spaces in that column.
>> Any idea what might cause this?
>>
>
> Perhaps the database has the trailing spaces but you don't see them
> due to how you're looking.
>

I use IBExpert to inspect the database (Firebird SQL) and I doesn't show
the trailing spaces. Just to make sure I wanted to double check and
used FlameRobin and there I do see these spaces in the database. So, it
is an issue on the db side and not SQLAlchemy. No idea how they got in
there in the first place.

Thanks for pushing me to search in the right place.

Werner

P.S.
BTW, both columns are defined as varchar, i.e. varchar(30) for the name
column and varchar(10) for the shortname.