python - 来自文件路径的文件夹和文件名的 sqlalchemy @hybrid_property 表达式。很容易用纯python编写

Question

我有一个正在使用 sqlalchemy 的数据库，其中涉及存储文件的位置。

我有类似的东西：

class FileLocation(ORMBase): 
    id = Column('id', Integer, primary_key=True)
    filepath = Column('filepath', String)

我想添加与每个文件路径对应的文件夹和文件名的混合表达式。当然，使用常规 python 字符串很容易做到这一点，但我找不到在 sqlalchemy 表达式中进行这种字符串操作的方法。

from sqlalchemy import func

class FileLocation(ORMBase):

    id = Column('id', Integer, primary_key=True)
    filepath = Column('filepath', String)


    @hybrid_property
    def folder(self):
        return os.path.dirname(self.filepath)

    @folder.expression
    def folder(cls):
        # How to  get folder for sql queries???
        last_pathsep_index = # ???
        return func.substr(cls.filepath, 0, last_pathsep_index)

    @hybrid_property
    def filename(self):
        return os.path.basename(self.filepath)

    @filename.expression
    def filename(cls):
        # How to  get filename for sql queries???
        last_pathsep_index = # ???
        return func.substr(cls.filepath, last_pathsep_index+1, func.len(cls.filepath))

我将如何编写@filename.expression和@folder.expression属性，目前显示的实现不完整？

标签： pythondatabasesqliteormsqlalchemy

Answer 1

这是我最终得到的解决方案，我通过@AnthonyCarapetis 的建议链接找到了它。

这最终只是一个直接的 SQL (SQLite) 问题，而不是关于 sqlalchemy 的问题，但我会将 sqlalchemy 代码留在这里，以防它对其他人有所帮助。

import os

import sqlalchemy as sa
from sqlalchemy import case, func

def getpathsep(osname=os.name):
    """use os.name to determine osname"""
    return case([(osname == 'nt', '\\')], else_='/')


def dirname(filepath, osname=os.name):
    """use os.name to determine osname"""
    pathsep = getpathsep(osname)
    replaced = func.replace(filepath, pathsep, '')
    filename = func.rtrim(filepath, replaced)
    return func.substr(filepath, 0, func.length(filename))


def pathsplit(filepath, osname=os.name):
    """use os.name to determine osname"""
    folder = dirname(filepath, osname)
    l = func.length(folder) + 1  # add 1 for (back) slash char
    basename = func.substr(filepath, l + 1)  # First index is 1
    return folder, basename


def basename(filepath, osname=os.name):
    """use os.name to determine osname"""
    return pathsplit(filepath, osname)[1]


class FileLocation(ORMBase):

    id = Column('id', Integer, primary_key=True)
    osname = Column('osname', String)
    filepath = Column('filepath', String)


    @hybrid_property
    def folder(self):
        return os.path.dirname(self.filepath)

    @folder.expression
    def folder(cls):
        return dirname(cls.filepath, cls.osname)

    @hybrid_property
    def filename(self):
        return os.path.basename(self.filepath)

    @filename.expression
    def filename(cls):
        return basename(cls.filepath, cls.osname)