Escaping single quote characters to execute sql query using sqlalchemy-CodePudding

I am loading a json data with large number of into snowflake. Some values of the json row contains single quotes and it is returing an error when I am trying to insert json into snowflake using parse_json . I am using sqlalchemy as a connector from python. Here is my code

connection.execute(
                f'insert into json_demo select parse_json( """{json.dumps(data)}""" )')

The json data sample is as follows:

[
  {
    "name": "Milka Luam",
    "ID": "124",
    "UnitAddressLine1": "1262 University Runa",
    "UnitCity": "Jas sti'n"
  },
  {
    "name": "Rahu Liran",
    "ID": "541",
    "UnitAddressLine1": "1262 University Blina",
    "UnitCity": "Rish 21"
  },
  ...
]

The single quote in the unity city Jas sti'n is returning an error. Here is the error:

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error:
syntax error line 1 at position 47 unexpected 'UnitCity'.
syntax error line 1 at position 47 unexpected 'UnitCity'.
parse error line 1 at position 90,841 near '<EOF>'.

I can't manually add an escape character as I am loading a large number of row.

CodePudding user response：

As a solution you can either triple quote "Jas sti'n":

"""Jas sti'n"""

[
  {
    "name": "Milka Luam",
    "ID": "124",
    "UnitAddressLine1": "1262 University Runa",
    "UnitCity": """Jas sti'n"""
  }
]

Or you can place a backslash before the quote to escape it:

"Jas sti\'n"

[
  {
    "name": "Milka Luam",
    "ID": "124",
    "UnitAddressLine1": "1262 University Runa",
    "UnitCity": "Jas sti\'n"
  }
]

Both of these solutions make python read the ' as part of the string.

CodePudding user response：

Using f-strings to interpolate values is error-prone and a security risk. Use SQLAlchemy's text function and bind parameters instead.

# Demonstrate query text
import json

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


json_ = json.dumps({'a': "Jas sti'n"})

stmt = sa.text("""insert into json_demo select parse_json(:json_)""")

stmt = stmt.bindparams(json_=json_)
print(
    stmt.compile(
        dialect=postgresql.dialect(), compile_kwargs={'literal_binds': True}
    )   
)

Output:

insert into json_demo select parse_json('{"a": "Jas sti''n"}')

To execute the statement, you would do

stmt = sa.text("""insert into json_demo select parse_json(:json_)""")
with engine.begin() as conn:
    conn.execute(stmt, {'json_': json_})