formating dot commands in sqlite3

Question

My first SQL database im using sqlite3 in idle on linux. Everything is working fine but I want my table to display in an easier to read format in the terminal. been reading about dot commands eg .mode column or .headers on but not sure were to put it in my code, i think this is the closest:

def veiw_outstanding_job():
cursorObj = con.cursor()

cursorObj.execute('.mode column\nSELECT * FROM '+ VesselName + ' WHERE State = "outstanding" ')

rows = cursorObj.fetchall()

for row in rows:

    print(row)
menu()

but i get this error Traceback (most recent call last): File "/usr/lib/python3.8/idlelib/run.py", line 559, in runcode exec(code, self.locals) File "/home/mike/Documents/Maintenance Manager/Maintenance Manager.py", line 238, in <module> menu() File "/home/mike/Documents/Maintenance Manager/Maintenance Manager.py", line 27, in menu veiw_job(con) File "/home/mike/Documents/Maintenance Manager/Maintenance Manager.py", line 54, in veiw_job cursorObj.execute('.mode column\nSELECT * FROM '+ VesselName) sqlite3.OperationalError: near ".": syntax error

Answer 1

https://sqlite.org/cli.html says:

Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But input lines that begin with a dot (".") are intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.

This means if you are NOT using the CLI tool, for example, if you are using a query API, then those dot commands are not recognized.

In other words, a dot command like .mode column is only used when writing queries in the sqlite CLI.

If you want to use Python to do something similar, you would have to print the row yourself in a manner that aligns to columns.

There are a couple of solutions for pretty-printing SQL query results in answers to this question: https://stackoverflow.com/questions/10865483/print-results-in-mysql-format-with-python