[Tutor] make a sqlite3 database from an ordinary text file

[Dennis Lee Bieber]

On Mon, 20 Jun 2022 18:32:20 +0100, Alan Gauld via Tutor <tutor at python.org>
declaimed the following:

>On 20/06/2022 17:18, Dennis Lee Bieber wrote:
>
>> 	The SQLite3 adapter appears to inherently block using parameterized
>> queries to modify the database schema. If you REALLY must generate the
>> schema using data from a file, you have to do all of the that in Python,
>
>Ah! I didn't know that. Precisely because I never use Python
>to create a database!
>

	I'm not ambitious enough to try testing with Firebird or SQL-Server to
see if they object.

	I suspect SQLite3 probably objects to any DDL statement that is
parameterized -- I think it goes through the "prepare" (compile the SQL
statement)/"execute" (run the compiled statement with parameters). Schema
entities (database, table, fields...) probably have to be KNOWN during the
prepare/compile phase.

	One of the older MySQL adapters did not use "prepared statements" --
and internally it used Python string interpolation (with %s placeholders)
to plug in data values (and .executemany() might have been a loop issuing
statements for each set of data values). On that adapter, the OP's attempt
to define the schema from data values probably worked. As I recall, MySQL
in the 3.x series did not support prepared statements even from C. The
latest versions do support prepared statements (and other complex features)
and newer adapters may take advantage of such.

>That's true, although I might not say NEVER, since often you write
>one-off scripts for database work that nobody else ever uses. But the
>official mechanism works well enough for most real cases that there's
>rarely any reason not to use it.
>
	My throw-away efforts still have me looking at the input data file to
determine field names and data types (though SQLite3 doesn't really care --
if a field is defined INT and the data is a string that can be interpreted
as INT it converts, otherwise it will happily store it as text).

>And creating tables and columns dynamically is frought with problems
>too. It's like trying to create variables in code from user input - how
>does the rest of the code refer to those values? For variables we can
>use a dictionary as a partial solution but for a database it gets very
>messy with the need to look at the meta data before performing any action.

	And that leads back to my prior post about "menus". If the application
needs the user to specify fields for matching in a WHERE clause, say, I'd
present a menu of field names obtained from the schema, and the user would
select by menu position, not by entering the name. That way I know the
field name(s) can not contain garbage, and it is safe to use Python string
interpolation to build the SQL statement, while using parameterization for
the user provided values to be matched.



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