[Tutor] Excel to SQL DB using Python

[Dennis Lee Bieber]

On Fri, 25 Feb 2022 12:00:37 +0000, Hannah Jones
<hannah.jones at ebcbrakesuk.com> declaimed the following:

>for x in Locations:
>    SageCode = x[0] ; Name = x[1] ; AddressLine1 = x[2] ; AddressLine2 = x[3] ; AddressLine3 = x[4] ; AddressLine4 = x[5] ; City = x[6] ; CountryName = x[7]

	<ugh>

	for (SageCode, Name, AddressLine1, ..., CountryName) in Locations:


>    sqlVALUES = sqlVALUES + "( '" + SageCode + "', '" + Name + "', '" + AddressLine1 + "', '" + AddressLine2 + "', '" + AddressLine3 + "', '" + AddressLine4 + "', '" + City + "', '" + CountryName +  "' ), '"

	Never do that -- especially if the data comes from an external source!
All it would take is for one data item to have a " in it to turn that into
a mess -- not to mention having a ";SOMESQL in a value executing things you
don't expect..

	Check the documentation for the DB-API adapter you are using to
determine what style of parameter it wants, and parameterize the
SQL/.execute() lines. It is the responsibility of the adapter to examine
each data item and properly escape any dangerous characters.


	{I also ponder why you are reading the entire CSV file into a list
first, only to then iterate over that list to do SQL INSERT statements --
rather than do the insert at the time you read the CSV lines. OR -- once
you parameterize the query, you could use .executemany() to process the
entire list as one transaction.

cursor.commit() is a convenience function that just turns into
connection.commit()

https://github.com/mkleehammer/pyodbc/wiki/Cursor
}


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