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/mac/Music'

所以我的新输出是：

表track_locations

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

属性目录 '/home/mac/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')