Need max values in list of tuples, based on position

[Dennis Lee Bieber]

On Fri, 11 Nov 2022 21:20:10 -0500, DFS <nospam at dfs.com> declaimed the
following:

>Yeah, I don't know why cursor.description doesn't work with SQLite; all 
>their columns are basically varchars.
>
	If you read PEP 249 (the general DB-API), everything except column name
and data type code are optional. And the documentation for the sqlite3
module explicitly states that it only provides the column name, not even
type code.
 
>
>The query is literally any SELECT, any type of data, including SELECT *. 
>  The reason it works with SELECT * is the cursor.description against 
>SQLite DOES give the column names:
>
>select * from timezone;
>print(cur.description)
>(
>('TIMEZONE',     None, None, None, None, None, None),
>('TIMEZONEDESC', None, None, None, None, None, None),
>('UTC_OFFSET',   None, None, None, None, None, None)
>)
>
>(I lined up the data)
>
>

	Consider (table definition first)

CREATE TABLE Game (
	ID INTEGER PRIMARY KEY NOT NULL,
	Variant VARCHAR(64) NOT NULL,
	Num_of_Decks INTEGER DEFAULT 1 NOT NULL,
	Cards_in_Deck INTEGER DEFAULT 52 NOT NULL,
	Num_Tableau_Cards INTEGER NOT NULL,
	Num_Reserve_Cards INTEGER  
		GENERATED ALWAYS 
		AS ((Num_of_Decks * Cards_in_Deck) - Num_Tableau_Cards) STORED,
	Foundation_Value INTEGER DEFAULT 1 NOT NULL,
	Tableau_Value INTEGER DEFAULT -1 NOT NULL,
	Reserve_Value INTEGER DEFAULT -2 NOT NULL
);
CREATE UNIQUE INDEX idx_Variant ON Game (Variant);

followed by a session retrieving three columns...

>>> import sqlite3 as db
>>> con = db.connect("c:/users/wulfraed/documents/.localdatabases/solitaire-sqlite/solitaire.db")
>>> cur = con.cursor()
>>> cur.execute("""select max(length(variant)), max(length(cards_in_deck)),
... 		max(length(num_reserve_cards)) from Game""")
<sqlite3.Cursor object at 0x00000246D91E3F80>
>>> widths = cur.fetchall()
>>> widths
[(16, 2, 2)]
>>> 
>>> widths[0]
(16, 2, 2)
>>> pformat = [f'%{w}s' for w in widths[0] ]
>>> pformat
['%16s', '%2s', '%2s']
>>> pformat = "\t".join(pformat)
>>> pformat
'%16s\t%2s\t%2s'
>>> for row in cur.fetchall():
... 	print(pformat % row)
... 	
 Klondike draw-3	52	24
Perpetual Motion	52	52
 Klondike draw-1	52	24
>>> 

	Granted, this will NOT work with "select *" unless one does the select
*/fetchall first, AND extracts the names from the cursor description --
then run a loop to create the select max(length(col)), ... statement (which
is why I state the fetchall step, as unless one creates a second cursor,
the latter select will wipe out any unfetched data from the first).

	It lets SQLite do the work of determining the max width occupied by
each column, rather than some complicated Python loop.



-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed at ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/