Python - how to issue SQL insert into statement with ' in value

Question

I am moving data from MySQL to MSSQL - however I have a problem with insert into statement when I have ' in value.

for export i have used code below:

import pymssql
import mysql.connector

conn = pymssql.connect(host='XXX', user='XXX', 
password='XXX', database='XXX')
sqlcursor = conn.cursor()


cnx = mysql.connector.connect(user='root',password='XXX', 
database='XXX')
cursor = cnx.cursor()
sql= "SELECT Max(ID) FROM XXX;"
cursor.execute(sql)
row=cursor.fetchall()

maxID = str(row)
maxID = maxID.replace("[(", "")
maxID = maxID.replace(",)]", "")

AMAX = int(maxID)

LC = 1
while LC <= AMAX:
  LCC = str(LC)
  sql= "SELECT * FROM XX where ID ='"+ LCC +"'"
  cursor.execute(sql)
  result = cursor.fetchall()

  data = str(result)
  data = data.replace("[(","")
  data = data.replace(")]","")
  data = data.replace("None","NULL")
  #print(row)
  si = "insert into [XXX].[dbo].[XXX] select " + data 
  #print(si)
  #sys.exit("stop") 
  try:    
      sqlcursor.execute(si)
      conn.commit()
  except Exception:   
      print("-----------------------")
      print(si)
  LC = LC + 1
print('Import done | total count:', LC)

It is working fine until I have ' in one of my values:

'N', '0000000000', **"test string'S nice company"**

I would like to avoid spiting the data into columns and then checking if there is ' in the data - as my table has about 500 fields.

Is there a smart way of replacing ' with ''?

Answer:

Added SET QUOTED_IDENTIFIER OFF to insert statement:

si = "SET QUOTED_IDENTIFIER OFF insert into [TechAdv].[dbo].[aem_data_copy] 
select " + data

You are opening yourself up to SQL Injection attacks using this technique. — Andrew
– Andrew, Commented Apr 6, 2018 at 12:26

LONG · Accepted Answer · 2018-04-06 11:57:53Z

1

In MSSQL, you can SET QUOTED_IDENTIFIER OFF, then you can use double quotes to escape a singe quote, or use two single quotes to escape one quote.

answered Apr 6, 2018 at 11:57

LONG

4,6302 gold badges21 silver badges41 bronze badges

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

3 Comments

Lemon Over a year ago

well I think it is working when I copy row to SQL, however python is still throwing an exception?

jarlh Over a year ago

two single quotes to escape one quote is the ANSI SQL way.

Lemon Over a year ago

Added SET QUOTED_IDENTIFIER OFF to my insert statement and it is working fine! Thanks

Collectives™ on Stack Overflow

Python - how to issue SQL insert into statement with ' in value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related