1

In a python class for SQLAlchemy you have to specify a maximum character length for a string variable. For example, url = Column(String(200)) I'm interested in storing a large string, but when I try and set mytextfield = Column(String(2**30)) I get the following error:

OperationalError: (OperationalError) (1074, "Column length too big for column 'textfield' (max = 21845); use BLOB or TEXT instead") '\nCREATE TABLE users (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tname VARCHAR(50), \n\tfullname VARCHAR(50), \n\tpassword VARCHAR(12), \n\ttextfield VARCHAR(2147483648), \n\tPRIMARY KEY (id)\n)\n\n' ()

How do I keep the ascii encoding but increase the maximum length of the string in the SQLAlchemy object Base class? If I were to switch to using unicode with mytextfield = Column(UnicodeText(2**30)) am I right in thinking that this will default to utf-8 and not increase storage size for standard English characters? If it were in utf-8 and I retrieved an entry from the SQL database, would the text be the equivalent of text = u'my text here' even if it was originally entered as text = 'my text here'?

Here is my current code:

from sqlalchemy import create_engine
engine = create_engine("mysql://user:password@host/database")

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String, Sequence, UnicodeText

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))
    textfield = Column(String(length=2**31))

    def __init__(self, name, fullname, password, textfield):
        self.name = name
        self.fullname = fullname
        self.password = password
        self.textfield = textfield

    def __repr__(self):
        return "<User('%s','%s', '%s', '%s')>" % (self.name, self.fullname, self.password, self.textfield)


from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

class Address(Base):
     __tablename__ = 'addresses'
     id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
     email_address = Column(String(100), nullable=False)
     user_id = Column(Integer, ForeignKey('users.id'))

     user = relationship("User", backref=backref('addresses', order_by=id))

     def __init__(self, email_address):
         self.email_address = email_address

     def __repr__(self):
         return "<Address('%s')>" % self.email_address



Base.metadata.create_all(engine)

mytext = "Peter Piper picked a peck of pickled peppers. A peck of pickled peppers Peter Piper picked. If Peter Piper picked a peck of pickled peppers, Where's the peck of pickled peppers that Peter Piper picked?"
new_user = User('Pete', 'Peter Piper', 'peppy', mytext)


from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
Session.configure(bind=engine)

session = Session()
session.add(new_user)
session.commit()

1 Answer 1

6

Use Text or UnicodeText instead of String or Unicode.

Text is mapped to CLOB(TEXT) fields, which can store large amount of data String is mapped to VARCHAR field, which is usually limited to 2^31-1

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.