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