Adding values from Excel worksheet to MySQL table using Python and the %s operator

Question

Trying to add values from a worksheet to a MySQL Table using a Python loop.

The code is as follows:

for i in range(number_of_columns):
    column_title=worksheet.cell(0,i).value

    for n in range(1,number_of_rows+1):

        value_for_table=worksheet.cell(n,i).value
        print n,i,column_title, value_for_table

        mycursor.execute("INSERT INTO Test (%s) VALUES (%s)", (column_title,value_for_table))

It spits out the following error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Name') VALUES ('Germany')' at line 1

I tried the solutions here unsuccessfully

Edit:

The Columns were originally created with this code:

for i in range(number_of_columns):

    column_title=worksheet.cell(0,i).value
    datatype_of_column=type(worksheet.cell(1,i).value)

    if datatype_of_column==float:
        cell_type="FLOAT(10)"
    elif datatype_of_column==int:
        cell_type="INT(10)"
    elif datatype_of_column==unicode or datatype_of_column==str:
        cell_type="VARCHAR(10)"
    elif datatype_of_column==bool:
        cell_type="BINARY"
    else:
        print ('Datatype at column %s not recognized. Set to float.', i)

    mycursor.execute("ALTER TABLE Test ADD (%s %s)" % (column_title, cell_type))

The table looks like this:

nacho · Accepted Answer · 2019-07-05T10:28:54.627

You can't use placeholders in prepared statements for column names or table names. You should change your query execution (mycursor.execute) to:

query=("INSERT INTO Test (%s)" % column_title) + " VALUES (%s)"
mycursor.execute(query, (value_for_table,))

EDIT

I have modified your script for your requeriments. As you said, your script creates a new row for each value instead of generating one insert statement for each row. Try with this one:

for n in range(1,number_of_rows+1):

    value_for_table=worksheet.cell(n,i).value
    query="INSERT INTO Test ("
    query_values="("
    values=[]
    for i in range(number_of_columns):
        column_title=worksheet.cell(0,i).value
        value_for_table = worksheet.cell(n, i).value
        query=query+column_title if i==0 else query+','+column_title
        query_values=query_values+"%s" if i==0 else query_values+',%s'
        values.append(value_for_table)
    query=query+') VALUES '+query_values+')'
    mycursor.execute(query, values)

Thanks this works! Sadly INSERT INTO seems to create a new row for every value. https://ibb.co/9hDtCcH Tried something like query=("INSERT INTO Test (%s)" % column_title) + " VALUES (%s) WHERE ID=n" without success. You wouldn't happen to know an easy solution to this? :) — cheesus, Jul 05 '19 at 10:06

Adding values from Excel worksheet to MySQL table using Python and the %s operator

Edit:

1 Answers1