文件路径中文件夹和文件名的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属性，它们目前显示为实现不完整？

Answer 1

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

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

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)