Issues trying to insert string to SQL Server
Question: I am trying to insert a string to a SQL DB starting with `0x` but it keeps
failing on the insert. The characters that come after `0x` are random
characters that range from A-Z, a-z and 0-9 with no set length. I tried to get
around it by adding a letter in front of the string and update it afterwards
but it does not work. I am using

SQL statement I am trying to mimic

    
    
    insert into [TestDB].[dbo].[S3_Files] ([Key],[IsLatest],[LastModified],[MarkedForDelete],[VersionID]) values ('pmtg-dox/CCM/Trades/Buy/Seller_Provided_-_Raw_Data/C''Ds_v2/NID3153422.pdf','1','2015-10-11','Yes', '0xih91kjhdaoi23ojsdpf')
    

Python Code

    
    
    import pymssql as mssql
    ...
    cursor.execute("insert into [TestDB].[dbo].[S3_Files] ([Key],[IsLatest],[LastModified],[MarkedForDelete],[VersionID]) values (%s,%s,%s,%s,%s)",(deleteitems['Key'],deleteitems['IsLatest'],deleteitems['LastModified'],MarkedforDelete, deleteitems['VersionId']))
    conn_db.commit()
    

> pymssql.ProgrammingError: (102, "Incorrect syntax near
> 'qb_QWQDrabGr7FTBREfhCLMZLw4ztx'.DB-Lib error message 20018, severity 15:
> General SQL Server error: Check messages from the SQL Server")

Is there a way to make Python, pymssql\mysql force insert the string? Is there
a string manipulation technique that I am not using? I have tried pypyodbc but
no luck.

Edit: My current patch is to alter the string and add a flag to the row so I
remember that the string starts with `0x`


Answer: This is the solution that I came up with.

Since running the insert command with the appropriate values worked, I created
a stored procedure in SQL to handle my request

    
    
    USE [TestDB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[stp_S3Files]
    @Key_ varchar(4000),
    @IsLatest_ varchar(200),
    @Size_ varchar(200),
    @LastModified_ varchar(200),
    @MarkedForDelete_ varchar(200),
    @VersionID_ varchar(200)
    AS
    insert into [TestDB].[dbo].[S3_Files] ([Key],[IsLatest],[Size(Bytes)],[LastModified],[MarkedForDelete],[VersionID]) values (@Key_, @IsLatest_, @Size_, @LastModified_, @MarkedForDelete_, @VersionID_)
    

Then I call it through Python

    
    
    modkey = deleteitems['Key'].replace("'", "''")
    cursor.execute("""exec TestDB.dbo.stp_S3Files
        @Key_               = '%s'
        ,@IsLatest_          =  %s
        ,@Size_              = '%s'
        ,@LastModified_      = '%s' 
        ,@MarkedForDelete_   = '%s'
        ,@VersionID_         = '%s' """ %(modkey, deleteitems['IsLatest'],deleteitems['Size'],deleteitems['LastModified'],MarkedforDelete,deleteitems['VersionId']))
    conn_db.commit()
    

Note: the string replace is to handle path names with ' to escape the
character. I hope this helps someone who has the same issue down the road.

