python - ascii 与 MYSQL 的 unicode 中的 SQLAlchemy 大字符串

Question

在 SQLAlchemy 的 python 类中，您必须为字符串变量指定最大字符长度。例如，url = Column(String(200)) 我对存储大字符串很感兴趣，但是当我尝试设置 mytextfield = Column(String(2**30)) 我收到以下错误:

OperationalError: (OperationalError) (1074, “列长度对于‘文本字段’来说太大了(最大值 = 21845)；请改用 BLOB 或 TEXT”)'\nCREATE TABLE 用户(\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' ()

如何保留 ascii 编码但增加 SQLAlchemy 对象基类中字符串的最大长度？如果我要切换到使用 unicode 和 mytextfield = Column(UnicodeText(2**30)) 我认为这将默认为 utf-8 并且不会增加标准英文字符的存储大小是正确的？如果它是 utf-8 并且我从 SQL 数据库中检索了一个条目，那么文本是否等同于 text = u'my text here' 即使它最初是作为 text 输入的= '我的文字在这里'?

这是我当前的代码:

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

Answer 1

使用Text或 UnicodeText而不是 String 或 Unicode。

Text映射为CLOB(TEXT)字段，可以存储大量数据String映射到VARCHAR字段，通常限制为2^31-1