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

Answer 1

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.

Answer 2

This should work (tested on my machine) :

print user.columns.name.type.length

Answer 3

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)

Answer 4

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))```

友情链接