[Tutor] Excel to SQL DB using Python

[Hannah Jones]

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.

Thank you
Hannah