Mysqldb INSERT INTO syntax error in Python

Question

I have an excel file which I am trying to put into mysql. There are 32 fields which I want to extract using the python plugin xlrd and put into mysql. The data is laid out like so:

Data Layout

My methodology is to iterate over the rows and columns, adding fieldnames as I go. The code adds the field name but then there is an error when adding the data. (I created the table 'OilProvedReservesHistory' with an index as the only field):

SQL Layout

def get_data_from_sheet(sheet_index, table, start_row, end_row, end_col):

for row in range(start_row - 1, end_row - 1):
    for col in range(end_col):
        cell = sheet.cell(row,col)
        if col == 0:        
            fieldname = cleanup_field(cell.value)
            if fieldname != ("Blank Cell"):
                sql = "ALTER TABLE OilProvedReservesHistory ADD %s VARCHAR(20)" % fieldname
                c.execute(sql)                  
        else:
            data = cleanup_data(cell)
            if data != ("Blank Cell"):
                postToMySQL(data,fieldname,table)


def postToMySQL(data,fieldname,table):
#Generate sql query string                
sql = "INSERT INTO " + table + " ("+ fieldname + ") VALUES %s"
c.execute(sql, data)
# Executes: ("""INSERT INTO OilProvedReservesHistory (US) VALUES 36.533""")

table = create_table_from_sheet_name()

sheet_index = 0
start_row = 5
end_row = 68
end_col = 32

get_data_from_sheet(sheet_index, table, start_row, end_row, end_col)

The error I get is:

_mysql_exceptions.ProgrammingError: (1064, "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 ''36.533'' at line 1")

Can you help me get this working? Thanks a lot!

JK. · Accepted Answer · 2012-05-07 15:01:21Z

1

My guess is that you simply forgot the parenthesis for your VALUES clause.

INSERT INTO OilProvedReservesHistory (US) VALUES (36.533)

Try changing your code to:

sql = "INSERT INTO " + table + " ("+ fieldname + ") VALUES (%s)"

answered May 7, 2012 at 15:01

JK.

5,1261 gold badge29 silver badges26 bronze badges

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

1 Comment

eamon1234 Over a year ago

Ha, yeah you're right! Before I posted this I had been trying to create all the fieldnames first and then post the data values, however I think the error was that I was trying to fill a row with just one value. Thanks!

Collectives™ on Stack Overflow

Mysqldb INSERT INTO syntax error in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related