sqlite用新路径替换每个指定的子路径

Question

以下是 sqlite3 中 track_locations 表的原始输出：

表 track_locations

属性位置 '/Users/marcosvelazquez/Documents/Music/loops/Manuel Tur - 123 BPM.mp3'

属性目录 '/Users/marcosvelazquez/Documents/Music/loops'*

我想要一个更改的查询

'/Users/marcosvelazquez/Documents'

<强>路径

'/home/marc/Music'

所以我的新输出将是：

表track_locations

属性位置 '/home/marc/Music/Music/loops/Manuel Tur - 123 BPM.mp3'

属性目录 '/home/marc/Music/Music/loops'*

import sqlite3

con = sqlite3.connect('mixxxdb.sqlite')cur = con.cursor()

for row in cur.execute('''SELECT location, directory FROM track_locations'''):
    rowTuple = row
    newLocation = ''
    newDir = ''
    newTuple = ()

    # Modifies the path for both location and directory
    newLocation = rowTuple[0]
    newLocation = newLocation.split('/Users/marcosvelazquez/Documents') 
    newLocation = '/home/marc/Music' + newLocation[1]
    print(newLocation)

    newDir = rowTuple[1]
    newDir = newDir.split('/Users/marcosvelazquez/Documents')
    newDir = '/home/marc/Music' + newDir[1]
    print(newDir)

    cur.execute('''UPDATE track_locations SET location = newLocation''')
    cur.execute('''UPDATE track_locations SET directory = newDir''')

进行了几次搜索，但没有找到答案。然后我尝试使用 sqlite。这是我的查询：

UPDATE track_locationsSET location = '/home/marc/Music' | ltrim(location,'/Users/marcosvelazquez/Documents')

WHERE location IS NOT NULL;

不幸的是，这也不起作用。

帮助将不胜感激。谢谢！

Answer 1

我认为sqlite函数 replace（）是您正在寻找的东西：

UPDATE track_locations
SET location = replace(location, '/Users/marcosvelazquez/Documents', '/home/marc/Music'),
directory = replace(directory, '/Users/marcosvelazquez/Documents', '/home/marc/Music')