How to get sqlalchemy length of a string column

Question

Consider this simple table definition (using SQLAlchemy-0.5.6)

from sqlalchemy import *

db = create_engine('sqlite:///tutorial.db')

db.echo = False  # Try changing this to True and see what happens

metadata = MetaData(db)

user = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('name', String(40)),
    Column('age', Integer),
    Column('password', String),
)

from sqlalchemy.ext.declarative import declarative_base

class User(declarative_base()):

    __tablename__ = 'user'
    user_id = Column('user_id', Integer, primary_key=True)
    name = Column('name', String(40))

I want to know what is the max length of column name e.g. from user table and from User (declarative class)

print user.name.length
print User.name.length

I have tried (User.name.type.length) but it throws exception

Traceback (most recent call last):
  File "del.py", line 25, in <module>
    print User.name.type.length
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg/sqlalchemy/orm/attributes.py", line 135, in __getattr__
    key)
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'type'

Denis Otkidach · Accepted Answer · 2009-11-22 15:17:33Z

26

User.name.property.columns[0].type.length

Note, that SQLAlchemy supports composite properties, that's why columns is a list. It has single item for simple column properties.

answered Nov 22, 2009 at 15:17

Denis Otkidach

33.5k8 gold badges86 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KomodoDave Over a year ago

I'd like to read more about this but can't find reference to the property attribute anywhere in the official docs. Would someone be kind enough to post a link?

A. Wilson Over a year ago

Here is the type it returns: sqlalchemy.readthedocs.org/en/latest/orm/… but I would like to second @KomodoDave and wonder where the documentation is for the property and columns attributes. My coworkers and I have reservations about using undocumented functionality.

Ben Blank Over a year ago

Looks like the link above has died. The new URL appears to be docs.sqlalchemy.org/en/14/orm/…

Igor Jan 26 at 19:19

Docs link for SQLAlchemy 2.0 docs.sqlalchemy.org/en/20/orm/…

Pierre Bourdon · Accepted Answer · 2009-11-22 05:06:37Z

3

This should work (tested on my machine) :

print user.columns.name.type.length

answered Nov 22, 2009 at 5:06

Pierre Bourdon

10.9k4 gold badges36 silver badges28 bronze badges

2 Comments

Anurag Uniyal Over a year ago

+ thanks it works, but my usecase was bit different , I am basically using declarative_base to define a User class, how can i get length from class, please see modified question

Anurag Uniyal Over a year ago

yes it throws exception 'AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'type'' update Question

AmourK · Accepted Answer · 2022-12-01 23:40:26Z

2

If you have access to the class:

TableClass.column_name.type.length

If you have access to an instance, you access the Class using the __class__ dunder method.

table_instance.__class__.column_name.type.length

So in your case:

# Via Instance
user.__class__.name.type.length
# Via Class
User.name.type.length

My use case is similar to @Gregg Williamson However, I implemented it differently:

def __setattr__(self, attr, value):
    column = self.__class__.type
    if length := getattr(column, "length", 0):
        value = value[:length]
    super().__setattr__(name, value)

edited Dec 1, 2022 at 23:40

answered Dec 1, 2022 at 17:37

AmourK

7321 gold badge8 silver badges20 bronze badges

Comments

Gregg Williamson · Accepted Answer · 2022-11-13 13:53:03Z

0

I was getting errors when fields were too big so I wrote a generic function to trim any string down and account for words with spaces. This will leave words intact and trim a string down to insert for you. I included my orm model for reference.

class ProductIdentifierTypes(Base):
    __tablename__ = 'prod_id_type'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(length=20))
    description = Column(String(length=100))

def trim_for_insert(field_obj, in_str) -> str:

    max_len = field_obj.property.columns[0].type.length
    if len(in_str) <= max_len:
        return in_str
    
    logger.debug(f'Trimming {field_obj} to {max_len} max length.')
    
    trim_str = in_str[:(max_len-1)]
    
    if ' ' in trim_str[:int(max_len*0.9)]:
        return(str.join(' ', trim_str.split(' ')[:-1]))
    
    return trim_str

def foo_bar():
    from models.deals import ProductIdentifierTypes, ProductName
    
    _str = "Foo is a 42 year old big brown dog that all the kids call bar."
    
    print(_str)
    
    print(trim_for_insert(ProductIdentifierTypes.name, _str))
    
    _str = "Full circle from the tomb of the womb to the womb of the tomb we come, an ambiguous, enigmatical incursion into a world of solid matter that is soon to melt from us like the substance of a dream."
    
    print(_str)
    
    print(trim_for_insert(ProductIdentifierTypes.description, _str))```

answered Nov 13, 2022 at 13:53

Gregg Williamson

1

1 Comment

Ftagliacarne Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to get sqlalchemy length of a string column

4 Answers 4

4 Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related