[Tutor] Excel to SQL DB using Python

[Peter Otten]

On 25/02/2022 13:00, Hannah Jones wrote:
> Hello,
>
> I need help with some code if possible please.
>
> I am trying to convert an excel file to a list and then iterate over the list and add to an SQL database.
>
> I am having trouble adding the data to the database.
>
> I have already established the connection - all good.
>
> My code is:
>
> Locations = []
> filename = input("Enter Filename Containing your Locations:  ")
>
> with open(filename, "r") as csv_file:
>      csv_reader = csv.reader(csv_file, delimiter=',')
>      #skips the header from the excel file
>      next(csv_reader)
>      for lines in csv_reader:
>          Locations.append(lines)
> # SQL Statements
> sqlDELETE = "DELETE FROM dbo.GeocodingLocations"
> cursor.execute(sqlDELETE)
> sqlINSERTBase = "INSERT INTO dbo.GeocodingLocations (SageCode, Name, AddressLine1, AddressLine2, AddressLine3, AddressLine4, City, CountryName) VALUES "
> sqlVALUES = ""
>
> # Iteration to add to Studio DB
> 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]
>      sqlVALUES = sqlVALUES + "( '" + SageCode + "', '" + Name + "', '" + AddressLine1 + "', '" + AddressLine2 + "', '" + AddressLine3 + "', '" + AddressLine4 + "', '" + City + "', '" + CountryName +  "' ), '"
>      sqlINSERT = sqlINSERTBase + sqlVALUES
>      sqlINSERT = sqlINSERT
>      cursor.execute(sqlINSERT)
>
>
> cursor.commit()
> con.close()
>
>
> however I keep receiving this error; pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'S'. (102) (SQLExecDirectW)")
>
> I know my error is in this line;
>
>
>
> sqlVALUES = sqlVALUES + "( '" + SageCode + "', '" + Name + "', '" + AddressLine1 + "', '" + AddressLine2 + "', '" + AddressLine3 + "', '" + AddressLine4 + "', '" + City + "', '" + CountryName +  "' ), '"
>
>
> but I am not sure where exactly, any help is highly appreciated.

You are constructing the insertion statement manually, like in the
example below:

first, second = "Hannah",  "Jones"
sql = "insert into people (first, second) values ('" + first + "', '" +
  + second + "');"

cursor.execute(sql)

This is error prone and even dangerous when the name is entered/provided
by an untrusted user/source. A classic example:

https://xkcd.com/327/

You can avoid this problem dubbed "sql injection" by using a fixed query:

sql = "insert into people (first, second) values (?, ?);"
cursor.execute(sql, (first, second))

In the example I use the question mark as a placeholder; you can find
the actual placeholder in the paramstyle attribute. See

https://www.python.org/dev/peps/pep-0249/#paramstyle

for the details.