sqlite3 docs: explain SELECT-with-literals trick

[erlend-aasland]

Some examples use the following SQL trick:

# setup code; we only need a "dummy" connection
import sqlite3
cx = sqlite3.connect(":memory:")

# returns one row with one column: ("a",)
row = cx.execute("select 'a' as literal").fetchone()

# do stuff with result
print(row)

Some people may find such examples strange, because:

• we create a connection to an empty database; it is not immediately obvious why we do this
• it may not be immediatly obvious that you can construct a resulting row by using literals in your query

We may consider one or more of the following:

• Add a very short SQL tutorial
• Add an SQL (and/or) SQLite tips and tricks howto
• Explain how SQL queries work (what is a resulting row , etc.)

Originally posted by @erlend-aasland in #96122 (comment)

Linked PRs

• gh-96165: Clarify passing ":memory:" in sqlite3.connect(). #106451
• gh-96165: Clarify omitting the FROM clause in SQLite queries. #106513
• [3.12] gh-96165: Clarify omitting the FROM clause in SQLite queries (GH-106513) #106645
• [3.11] gh-96165: Clarify omitting the FROM clause in SQLite queries (GH-106513) #106646
• [3.12] gh-96165: Clarify passing ":memory:" in sqlite3.connect() (GH-106451) #106647
• [3.11] gh-96165: Clarify passing ":memory:" in sqlite3.connect() (GH-106451) #106648

[erlend-aasland]

cc. @CAM-Gerlach, @ezio-melotti

[ezio-melotti]

• Add an SQL (and/or) SQLite tips and tricks howto

I think this would be useful. Personally I have a basic understanding of DBs and SQL, but there are always a number of things I have to google (especially while jumping from one SQL dialect to the other).

In particular, I would find the following things useful:

• mentioning :memory:, since it could be used to try out things;
• mentioning the sqlite3 console (if there is such a thing);
• the SELECT-with-literals trick;
• how to introspect the DB and see the list of tables, their columns, how many rows they have, their types, etc.
• perhaps a few examples of common queries (CREATE/INSERT/SELECT/UPDATE), and maybe of things like count()/max()/min(), WHERE, ORDER BY, JOIN, etc..

The last two items could even be combined in a relatively compact table like:

how to...	query
see the list of tables	SELECT ...
create a db	CREATE ...
insert values	INSERT ...
count rows	SELECT count(*) ...
find the biggest value	SELECT max(...) ...
order by column	SELECT ... ORDER BY ...
filter results	SELECT ... WHERE ...
explain a query	EXPLAIN QUERY PLAN ...

This will allow the reader to learn how to do some common operations and at the same time figure out the syntax from the examples and recombine them to achieve what they need without having to leave the page. It doesn't need to have any explanation. It could also be considered as a cheat sheet (which I'm not sure where it fits in diataxis).

[CAM-Gerlach]

Hit the wrong button, sorry

[CAM-Gerlach]

Some people may find such examples strange

I am, of course, a complete SQL n00b (much of what little I do know is just from reviewing your PRs on the subject), so take this for what it is, but I certainly did. In particular, what was most strange to me was that we were creating a database but then somehow using SELECT queries to just "magically" make up what we wanted anyway, instead of actually querying the database.

We may consider one or more of the following:

Personally, even though this would be helpful to me, I'm wary of duplicating already (presumably) good and well-developed SQL documentation elsewhere, though I would be a major fan of instead linking to them early and often whenever a new concept comes up...assuming such exists. In particular, I'm sure many good, relatively straightforward SQL/SQLite tutorials exist many other places, so it might seem a better idea to link to such, rather than reinventing the wheel, unless there is something sqlite3-specific involved.

Also, at least for the sqlite3 tutorial, I would generally favor trying to simplify it where possible and avoid relying on "tricks" and magic, per, e.g. the spirit of Get the user started; it should be as obvious as possible how things work and why, without unnecessary explaination not vital for the basic tutorials the tutorial's goal is to teach.

That being said, I like many of the specific concepts @ezio-melotti mentioned in the context of being how-tos or explanations, as they are useful, specific and intersect with sqlite3 enough to potentially justify at least a brief mention with a link to another resource if a suitable one exists, or else a bit more explanation/how-to if it does not. I'm not sure about a SQL console, and how that intersects with the sqlite3 CLI (which is certainly module-specific), and the one I tend to doubt as the "the SELECT-with-literals trick" as if I understand it right, it is more of a SQL trick than anything to do with sqlite3 specifically, and might better be either linked or avoided in the examples instead, if possible.

I also like the table @ezio-melotti suggests, it covers the very basics and gets the user started quickly, but is also highly succient. It should also, preferably, link each item to (ideally) a SQLIte how-to, or other SQLite/etc. material explaining it in more detail, for those interested.

It could also be considered as a cheat sheet (which I'm not sure where it fits in diataxis).

Hmm, well does it serve the user's work or study? Work, clearly, which narrows it down to Reference or How-to (which intuitively is what one would expect). But does it describe practical steps, or theoretical knowledge? I'd say practical steps, mostly, even though it is highly succient, rather than being a formal reference. Therefore, I'd say it would belong to a how-to section, personally. We could ask Daniele, though.

[erlend-aasland]

I agree with CAM; a cheat-sheet is a tutorial HOW-TO (it contains practical steps, and it serves work).

[erlend-aasland]

I also agree there is no need for an SQL/SQLite tutorial here. We also don't want that maintenance burden.

[erlend-aasland]

Big thanks to @felixxm for the docs updates!