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-05 10:28:54Z

1

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)

edited Jul 5, 2019 at 10:28

answered Jul 5, 2019 at 9:30

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cheesus Over a year ago

Thanks this works! Sadly INSERT INTO seems to create a new row for every value. 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? :)

Collectives™ on Stack Overflow

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

Edit:

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related