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

[Dennis Lee Bieber]

On Mon, 20 Jun 2022 06:29:58 +0530, Manprit Singh
<manpritsinghece at gmail.com> declaimed the following:

>See i know this can be done very easily using numpy and Pandas. But I am
>trying to do it with Core Python only.
>
>cur.execute("create table workdata(? INT, ? INT)", ("Time", "Pieces"))
>
>I just want to insert column names (where these column names are  read
>from text file abcd.txt 1st line)  using a question mark style
>placeholder.  Pls let me know if anything is possible .
>

	You DON'T!

	As stated in my previous post, that is manipulating the database schema
-- which should never rely upon values found in data files due to the
possibility of an SQL injection attack.

https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom

	The purpose of using the parameterized placeholders is that the
interface can "sanitize" (escape/quote) characters in the DATA that have
meaning in SQL.

	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,
BEFORE passing it to .execute().

>>> import sqlite3 as db
>>> con = db.connect("junk.db")
>>> cur = con.cursor()
>>> cur.execute("create table workdata (? INT, ? INT)", ("Time", "Pieces"))
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error
>>> cur.execute("create table workdata (%s INT, %s INT)" % ("Time", "Pieces"))
<sqlite3.Cursor object at 0x000002B642A8D030>
>>> cur.execute("insert into workdata (Time, Pieces) values (?, ?)", (123, "who"))
<sqlite3.Cursor object at 0x000002B642A8D030>
>>>

	Note that the second .execute() is using Python string interpolation to
create the schema definition SQL, not parameterized SQLite3 operations. The
third .execute() shows that parameterized statements work with DATA.  And,
per the warning about SQL injection attacks, you should NEVER use string
interpolation (or other formatting operations) in Python to work with data
for the database.


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