How do I clean up `sqlite_master` format in Python?

Question

My Python application is nearing completion (for three years) and I'm adding debugging information:

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")

The output is strange with tab (\t) and newline (\n) characters plus extraordinary amount of whitespace:

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)') 

Am I making a rookie mistake when creating the Sqlite3 tables in Python?

    """ 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 !
    # See https://stackoverflow.com/a/7337945/6929343 for explanation
    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)")

---

Success Using Variation of Accepted Answer:

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)') 

Snippet of Code used:

    # MUSIC TABLE - 'PlayCount' & 'Rating' not used
    # Avoid \t & \n in sqlite_master. See: https://stackoverflow.com/questions/76427995/
    # how-do-i-clean-up-sqlite-master-format-in-python
    # Create the table (key must be INTEGER not just INT !
    # See https://stackoverflow.com/a/7337945/6929343 for explanation
    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)")

Answer 1

plus extraordinary amount of whitespace

Well, you did send in quite a large amount of whitespace.

    con.execute("create table IF NOT EXISTS History(Id INTEGER PRIMARY KEY, \
                Time FLOAT, ... , Type TEXT, \
                Action TEXT, ... , SourceDetail TEXT, \

There's other ways to phrase that. Here is one, similar to the original but without \ backwhacks.

    con.execute("""
        create table ... KEY,
        Time FLOAT, ... , Type TEXT,
        Action TEXT, ... , SourceDetail TEXT, ...
    """)

Notice that we have \n newlines thrown in there, now.

Here is another way.

    con.execute(
        "create table ... KEY,"
        " Time FLOAT, ... , Type TEXT,"
        " Action TEXT, ... , SourceDetail TEXT, ..."
    )

Fewer \n newlines in that one, and less whitespace.

In general, given some sql command, you can always squish out extra whitespace:

    def squish(s: str) -> str:
        return " ".join(s.split())

    con.execute(squish(sql))

Of course some sql WHERE clauses won't survive that naïve approach, such as sql = "... WHERE text like '%two spaces%'".

---

A very convenient method of removing leading whitespace from a """string""" is to use dedent().

>>> from textwrap import dedent
>>> 
>>> s = """
...    one
...    two
... """
>>> 
>>> print(s, dedent(s))

   one
   two
 
one
two

>>>