在 SQLAlchemy 中使用自定义类型

from sqlalchemy import String
from sqlalchemy.types import TypeDecorator

class PidanCodeType(TypeDecorator):
    impl = String(255)

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        return "皮蛋编程_" + value

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        return value.replace("皮蛋编程_", "")

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)

# 注册自定义类型到 sqlalchemy 中
from sqlalchemy import event

@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_record):
    dbapi_connection.create_collation("pidancode", lambda s1, s2: cmp(s1, s2, locale="zh_CN"))

Base.metadata.create_all(engine)

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(PidanCodeType)

session = Session()

# 添加一条记录，使用字符串 "pidancode.com" 作为范例
user = User(name="pidancode.com")
session.add(user)

# 查询记录并输出 name 字段的值
user = session.query(User).filter_by(name="pidancode.com").first()
print(user.name)

session.commit()