如何在Python中清理sqlite_master的格式问题？

问题背景 #

我的Python应用开发已接近尾声（耗时三年），为调试添加了以下代码：

print("\nSQL - Sqlite3 Information")
print("====================================\n")

print("Sqlite3 Version:", sql.sqlite3.sqlite_version, "\n")
rows = sql.con.execute("SELECT * FROM sqlite_master;").fetchall()
for row in rows:
    print(row, "\n")

执行后，输出结果里出现了异常的制表符（\t）、换行符（\n）以及大量空白：

SQL - Sqlite3 Information
====================================

Sqlite3 Version: 3.11.0 

(u'table', u'History', u'History', 7, u'CREATE TABLE History(Id INTEGER PRIMARY KEY,                 Time FLOAT, MusicId INTEGER, User TEXT, Type TEXT,                 Action TEXT, SourceMaster TEXT, SourceDetail TEXT,                 Target TEXT, Size INT, Count INT, Seconds FLOAT,                 Comments TEXT)') 

(u'index', u'MusicIdIndex', u'History', 15, u'CREATE INDEX MusicIdIndex ON                 History(MusicId)') 

(u'index', u'TimeIndex', u'History', 28, u'CREATE INDEX TimeIndex ON                 History(Time)') 

(u'table', u'Music', u'Music', 1277, u'CREATE TABLE "Music" (\n\t`Id`\tINTEGER,\n\t`OsFileName`\tTEXT,\n\t`OsAccessTime`\tFLOAT,\n\t`OsModificationTime`\tFLOAT,\n\t`OsCreationTime`\tFLOAT,\n\t`OsFileSize`\tINT,\n\t`MetaArtistName`\tTEXT,\n\t`MetaAlbumName`\tTEXT,\n\t`MetaSongName`\tTEXT,\n\t`ReleaseDate`\tFLOAT,\n\t`OriginalDate`\tFLOAT,\n\t`Genre`\tTEXT,\n\t`Seconds`\tINT,\n\t`Duration`\tTEXT,\n\t`PlayCount`\tINT,\n\t`TrackNumber`\tTEXT,\n\t`Rating`\tTEXT,\n\t`UnsynchronizedLyrics`\tBLOB,\n\t`LyricsTimeIndex`\tTEXT,\n\tPRIMARY KEY(Id)\n)') 

(u'index', u'OsFileNameIndex', u'Music', 2, u'CREATE UNIQUE INDEX OsFileNameIndex ON                 Music(OsFileName)\n\n') 

(u'index', u'TypeActionIndex', u'History', 16, u'CREATE INDEX TypeActionIndex ON                 History(Type, Action)') 

请问我在Python中创建Sqlite3表时是不是犯了低级错误？以下是我的建表代码：

""" Open SQL Tables """
global con, cursor, hist_cursor
# con = sqlite3.connect(":memory:")  # Initial tests, not needed anymore
con = sqlite3.connect(FNAME_LIBRARY)

# MUSIC TABLE - 'PlayCount' & 'Rating' not used

# Create the table (key must be INTEGER not just INT !
con.execute("create table IF NOT EXISTS Music(Id INTEGER PRIMARY KEY, \
            OsFileName TEXT, OsAccessTime FLOAT, \
            OsModificationTime FLOAT, OsCreationTime FLOAT, \
            OsFileSize INT, MetaArtistName TEXT, MetaAlbumName TEXT, \
            MetaSongName TEXT, ReleaseDate FLOAT, OriginalDate FLOAT, \
            Genre TEXT, Seconds INT, Duration TEXT, PlayCount INT, \
            TrackNumber TEXT, Rating TEXT, UnsynchronizedLyrics BLOB, \
            LyricsTimeIndex TEXT)")

con.execute("CREATE UNIQUE INDEX IF NOT EXISTS OsFileNameIndex ON \
            Music(OsFileName)")


# HISTORY TABLE
con.execute("create table IF NOT EXISTS History(Id INTEGER PRIMARY KEY, \
            Time FLOAT, MusicId INTEGER, User TEXT, Type TEXT, \
            Action TEXT, SourceMaster TEXT, SourceDetail TEXT, \
            Target TEXT, Size INT, Count INT, Seconds FLOAT, \
            Comments TEXT)")

con.execute("CREATE INDEX IF NOT EXISTS MusicIdIndex ON \
            History(MusicId)")
con.execute("CREATE INDEX IF NOT EXISTS TimeIndex ON \
            History(Time)")
con.execute("CREATE INDEX IF NOT EXISTS TypeActionIndex ON \
            History(Type, Action)")

问题解决 #

调整代码后，输出恢复正常：

SQL - Sqlite3 Information
====================================

Sqlite3 Version: 3.11.0 

(u'table', u'Music', u'Music', 2, u'CREATE TABLE Music(Id INTEGER PRIMARY KEY, OsFileName TEXT, OsAccessTime FLOAT, OsModificationTime FLOAT, OsCreationTime FLOAT, OsFileSize INT, MetaArtistName TEXT, MetaAlbumName TEXT, MetaSongName TEXT, ReleaseDate FLOAT, OriginalDate FLOAT, Genre TEXT, Seconds INT, Duration TEXT, PlayCount INT, TrackNumber TEXT, Rating TEXT, UnsynchronizedLyrics BLOB, LyricsTimeIndex TEXT)') 

(u'index', u'OsFileNameIndex', u'Music', 3, u'CREATE UNIQUE INDEX OsFileNameIndex ON Music(OsFileName)') 

(u'table', u'History', u'History', 4, u'CREATE TABLE History(Id INTEGER PRIMARY KEY, Time FLOAT, MusicId INTEGER, User TEXT, Type TEXT, Action TEXT, SourceMaster TEXT, SourceDetail TEXT, Target TEXT, Size INT, Count INT, Seconds FLOAT, Comments TEXT)') 

(u'index', u'MusicIdIndex', u'History', 5, u'CREATE INDEX MusicIdIndex ON History(MusicId)') 

(u'index', u'TimeIndex', u'History', 6, u'CREATE INDEX TimeIndex ON History(Time)') 

(u'index', u'TypeActionIndex', u'History', 7, u'CREATE INDEX TypeActionIndex ON History(Type, Action)') 

修改后的代码片段 #

# MUSIC TABLE - 'PlayCount' & 'Rating' not used
# 避免sqlite_master中出现\t和\n
# Create the table (key must be INTEGER not just INT !
con.execute("create table IF NOT EXISTS Music(Id INTEGER PRIMARY KEY, " +
            "OsFileName TEXT, OsAccessTime FLOAT, " +
            "OsModificationTime FLOAT, OsCreationTime FLOAT, " +
            "OsFileSize INT, MetaArtistName TEXT, MetaAlbumName TEXT, " +
            "MetaSongName TEXT, ReleaseDate FLOAT, OriginalDate FLOAT, " +
            "Genre TEXT, Seconds INT, Duration TEXT, PlayCount INT, " +
            "TrackNumber TEXT, Rating TEXT, UnsynchronizedLyrics BLOB, " +
            "LyricsTimeIndex TEXT)")

con.execute("CREATE UNIQUE INDEX IF NOT EXISTS OsFileNameIndex ON " +
            "Music(OsFileName)")

内容的提问来源于stack exchange，提问作者WinEunuuchs2Unix