Insert to MySQL fails with error

Question

I'm using a Python script that I have been using many times before to load CSV data into MySQL tables.

I modified the script for a very simple insert but it fails and I can't see why.

I've gone through the MySQL documentation of the Python connector, compared my syntax and I went through all the related articles on Stackoverflow but I can't find the reason. I've also checked the quotes I'm using as that is a common error.

Perhaps someone can help:

if row[0]:
   s=row[0]
   d=s[s.rfind('/')+1:len(s)-4]

cursor.execute("INSERT INTO `tab` (`did`) VALUES (%s)",(d))

I've checked print(d) and d is populated correctly.

The error I'm getting is

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 '%s)' at line 1

If anyone can spot the (probably very silly) error, please help. Thanks.

Ilja Everilä · Accepted Answer · 2016-05-25 11:41:32Z

2

The problem is that in

cursor.execute("INSERT INTO `tab` (`did`) VALUES (%s)",(d))

the (d) passed as params is a string with parentheses around it, not a tuple.

Here's how a mysql-connector cursor checks its params:

if params is not None:
    if isinstance(params, dict):
        for key, value in self._process_params_dict(params).items():
            stmt = stmt.replace(key, value)
    elif isinstance(params, (list, tuple)):
        psub = _ParamSubstitutor(self._process_params(params))
        stmt = RE_PY_PARAM.sub(psub, stmt)
        if psub.remaining != 0:
            raise errors.ProgrammingError(
                "Not all parameters were used in the SQL statement")

So in your case though params is not None, it is not something accepted as params either and parameter substitution does not take place.

The fix then is simply to pass a tuple to cursor.execute() (a list works too):

cursor.execute("INSERT INTO `tab` (`did`) VALUES (%s)", (d,))

edited May 25, 2016 at 11:41

answered May 25, 2016 at 11:33

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

1 Comment

L4zl0w Over a year ago

This is working, thank you! I had tried similar things before but didn't get it quite right.

Aleksandar Jovanovic · Accepted Answer · 2016-05-25 11:11:40Z

1

I think your string formating is wrong. It should probably be:

cursor.execute("INSERT INTO `tab` (`did`) VALUES (?)",d)

But you should check in the docs for your database library. I'm pretty sure the problem is with the placeholder in the query.

edited May 25, 2016 at 11:11

answered May 25, 2016 at 11:03

Aleksandar Jovanovic

1,1771 gold badge12 silver badges15 bronze badges

1 Comment

Ilja Everilä Over a year ago

That would be an SQL injection point then. cursor.execute(stmt, args) is the correct way. String formatting / concatenating is the way to disaster.

Collectives™ on Stack Overflow

Insert to MySQL fails with error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related