Inserting Names with apostrophe to a Database Table using Python

Question

I am encountering an error when trying to insert data into a database using pyodbc.

The error is as follows:

('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Anthony'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ')\n '. (105)")

The code I am currently using is:

msconn=pyodbc.connect(driver='{Test Server}',
                    server='Test\Test',
                    database='Test',
                    trusted_msconnection='yes')
cursor=msconn.cursor()

for index, row in Terms.iterrows():
    I1 = 'COLUMNIDENTIFIER'
    I2 = row['EID']
    I3 = row['Legal Name']

insert_query = """
    INSERT INTO Test.Table 
    VALUES ('{}','{}','{}')
    """.format(I1,I2,I3)

cursor.execute(insert_query)

cursor.commit()
cursor.close()
msconn.close()

Checking the source file shows that the cause of the error is a name with an apostrophe. (I3)

Is there a way for me to upload the name with the "'"?

Thanks in advance.

Don't use `format` to put values into SQL queries. This is exactly why `execute` takes additional arguments: to fill in parameters in a static query string. If you do things that way, you don't have to worry about converting them to strings, quoting/escaping, or [Little Bobby Tables](https://xkcd.com/327/). — abarnert, Jul 03 '18 at 04:12
If you _did_ need to escape things manually, [the MS SQL Server syntax is to put two single quotes in a row](https://stackoverflow.com/questions/1586560/), something lik `I1.replace("'", "''")`—but again, you don't want to do that. — abarnert, Jul 03 '18 at 04:16

Inserting Names with apostrophe to a Database Table using Python

0 Answers0