Handling Single Quote in JSON using Python & Snowflake-CodePudding

I am trying to insert the dictionary which is retrieved from a rest API into Snowflake column of variant datatype using the below python script

from sqlalchemy import create_engine
import urllib
import requests
import json


engine = create_engine(
'snowflake://{user}:{password}@{account_identifier}/'.format(
    user='UserName',
    password='pwd',
    account_identifier='account_Identifier'
 )
)

jval={"title":"value", "address":[{ "Road":"st xavier's","landmark":"D' Pauls"}]}


try:
    connection = engine.connect()
    results = connection.execute(
        "INSERT INTO db_name.schema_name.sqlalchemy(jval) (select PARSE_JSON('"   json.dumps(jval)   "'))").fetchone()
    print(results[0])
finally:
    connection.close()
    engine.dispose()

But ending up with the following error

File "C:\Users\PycharmProjects\snowflake\venv\lib\site-packages\snowflake\connector\errors.py", line 207, in default_errorhandler
raise error_class(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error:
syntax error line 1 at position 133 unexpected 's'.
syntax error line 1 at position 134 unexpected '", "'.
[SQL: INSERT INTO db_name.schema_name.sqlalchemy(jval)  (select PARSE_JSON('{"title": "value", "address": [{"Road": "st xavier's", "landmark": "D' Pauls"}]}'))]
(Background on this error at: https://sqlalche.me/e/14/f405)

I take it this is causing due to the single quote (') present in the string. how to handle this error? the same error comes even when using snowflake.connector library as well.

CodePudding user response：

Use parameter binding so that the quoting is handled automatically.

from sqlalchemy import text 
...
results = connection.execute(
        text("INSERT INTO db_name.schema_name.sqlalchemy(jval) (select PARSE_JSON(:some_json))"), 
        {'some_json': jval}
).fetchone()