What's wrong with this syntax of parameterized INSERT query?

Question

I'm trying to import excel data into MS-access database, but it's giving me 'Syntax Error Definition'

Code:

for col_index in range(0,work_sheet.ncols):
    column_value = work_sheet.cell(0,col_index).value
    for row_index in range(1, work_sheet.nrows):
        row_value = work_sheet.cell(row_index,col_index).value # For the same column, fetch the row data repeatedly
        db.Execute("INSERT INTO my_table" [column_value] "VALUES(" & row_value & ")")

Error:

C:\Data\Database>c:\python27\python.exe My_First_DB_Code.py
File "My_First_DB_Code.py", line 32
db.Execute("INSERT INTO my_table" [column_value] "VALUES(" & row_value & ")")

SyntaxError: invalid syntax

Note: I have already imported the column names from the excel sheet and now trying to import the row values, column by column

Answer 1

You need to use Python's string concatenation syntax, not SQL's. Try generating the SQL string separately, then printing it to make sure it's correct before making the DB call:

query_string = "INSERT INTO my_table" + str(column_value) + " VALUES( " + str(row_value) + " )"
print(query_string)
# db.Execute(query_string)
# uncomment when you know the query is correct

The above code assumes that you want to concatenate my_table and column_value with no spaces between them. If, as HansUp comments, you are trying to input into a field name, set query_string like so:

query_string = query_string = "INSERT INTO my_table ([" + str(column_value) + "]) VALUES ('" + str(row_value) + "')"