0

I'm trying to update my data from a pandas dataframe:

with engine.begin() as conn:
    for d in range(0, len(df)):
        query = """ UPDATE table AS p SET name='%s' WHERE p.id='%s'
                """ % (df['name'][d], df['id'][d])
conn.execute(query)

The problem is one of the names is some like D'Artagnan, and it is having this error: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax 

error at or near "D"
LINE 3: ... name='D'Artagnan', ...

[SQL:  UPDATE table AS p SET name='D'Artagnan'
    WHERE p.id='4342']
(Background on this error at: http://sqlalche.me/e/f405)

The problem is I really need this data in this way in my database. Does someone have any idea?

CBury
  • 411
  • 2
  • 6
  • 9
  • 1
    Possible duplicate of [Insert text with single quotes in PostgreSQL](https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql) – Robert May 23 '19 at 22:31
  • 2
    This is one of the many reasons that you should use parameterized queries instead of string concatenation/interpolation. – Blorgbeard May 23 '19 at 22:32
  • Could you talk a little more about parameterized queries? – CBury May 23 '19 at 23:15
  • I'm not 100% sure on exactly how to format parameterized queries with your specific database engine, but it would look something like `conn.execute("update table as p set name=:name where p.id=:id", name = df['name'][d], id = df['id'][d]})` - so you use placeholders in the query string and pass the parameter values along with that string to the database engine, rather than replacing them in Python. This prevents "SQL injection" attacks, and also means you don't have to worry about escaping quotes etc. – Blorgbeard May 24 '19 at 17:08
  • 1
    I think this question's first answer also demonstrates the idea: https://stackoverflow.com/questions/19314342/python-sqlalchemy-pass-parameters-in-connection-execute – Blorgbeard May 24 '19 at 17:09

0 Answers0