Cannot execute SQL insert command using Python Dictionary as column value

Question

I am utilizing Pyspark in Databricks including inserting parametrized values into a SQL Server table through a SQL command.

Inserting parameters into SQL command (Pyspark):

sql = "INSERT INTO dbo.Validation VALUES ('{}','{}','{}','{}')".format(app,date,anomaly,value)

The SQL Server table has the following columns:

app = [varchar](255)
date [date]
anomaly= [varchar](255)
value = [nvarchar](max)

Dictionary as shown below:

value = {'pt_PT.UTF-8': [88], 'lt_LT.UTF-8': [24], 'fi_FI.UTF-8': [4], 'fr_BE.UTF-8': [4], 'nl_NL.UTF-8': [4]}

Printed SQL command:

INSERT INTO dbo.Validation VALUES ('TestApp','2020-05-06','LanguageAnomaly', '{'pt_PT.UTF-8': [88], 'lt_LT.UTF-8': [24], 'fi_FI.UTF-8': [4], 'fr_BE.UTF-8': [4], 'nl_NL.UTF-8': [4]}')

Error when executing SQL command:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'pt_PT'.

The issue I am encountering seems to be related to inserting the dictionary parameter "value". This maybe related to the apostrophes within the dictionary keys which the code cannot properly parse as a whole string.

How can I resolve this? This seems to work find if the dictionary key is a number value/int without quotes(').

Thanks.

matkurek · Accepted Answer · 2020-05-20 11:38:08Z

1

Your problem is with quotes from dictionary in your SQL insert statement, you need to escape them by doubling them up:

sql = "INSERT INTO dbo.Validation VALUES ('{}','{}','{}','{}')".format(app,date,anomaly,str(value).replace("'", "''"))

answered May 20, 2020 at 11:38

matkurek

8099 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Cannot execute SQL insert command using Python Dictionary as column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related