1

I have records from oracle in tuple, I'm trying to execute bulk insert statement in snowflake using python, but I'm facing escape character issue.

Input:

('Sample',1234,"Here is escape' need",9700,'final')

output:

('Sample',1234,'Here is escape'' need',9700,'final')

I have tried using regular expression to capture the string between double quotes and tried with replace function but it is not worked.

Input:

('Sample',1234,"Here is escape' need",9700,'final')

output:

('Sample',1234,'Here is escape'' need',9700,'final')
micharaze
  • 957
  • 8
  • 25
Sriga
  • 1,165
  • 4
  • 11
  • Hi @S3DEV I have tried the above format it not working in the target database, So I'm looking for the output as mentioned. – Sriga Sep 24 '19 at 08:08
  • I have removed my previous comment. Please see this [link](https://stackoverflow.com/a/28906392/6340496) to a similar question. – S3DEV Sep 24 '19 at 08:11

2 Answers2

0

You can split values by coma and iterate over parts searching parts with double quotes and replace them:

inp = "('Sample',1234,\"Here is escape' need\",9700,'final')"
out = inp
for s in inp.strip("()").split(","):
    if s.startswith("\"") and s.endswith("\"") and "'" in s:
        out = out.replace(s, "'" + s[1: -1].replace("'", "\"") + "'")

Result:

('Sample',1234,'Here is escape" need',9700,'final')
Olvin Roght
  • 7,677
  • 2
  • 16
  • 35
0

Why do you need to modify anything? Tuples can be inserted directly via parameter binding: 

t = ('Sample',1234,"Here is escape' need",9700,'final')
cursor.execute("INSERT INTO MY_TABLE VALUES(:1,:2,:3,:4,:5)", [t])
Hans Henrik Eriksen
  • 2,690
  • 5
  • 12
  • Thanks for the suggestion let me try it and get back you in case if I need further assistance. – Sriga Oct 15 '19 at 05:10
  • Hans, Its works only when input has single record, What if I have multiple records with below format: t = ('Sample',1234,"Here is escape' need",9700,'final'),('Sample',1234,"Here is escape' need",9700,'final') I'm facing following error while using parameter binding **TypeError: not enough arguments for format string**. Could you please assist me on this – Sriga Oct 17 '19 at 15:39
  • Did you get a chance to look into this. – Sriga Oct 21 '19 at 07:54