Single backslash required in SQL Server query from Python / SQLAlchemy

I am trying to write a query using a function BULK INSERT

in SQL Server 2014 via a python script to insert a large CSV file into a table in a database. I have the following example code:

import pymssql
import sqlalchemy

engine = create_engine('connection_string_goes_here', encoding="utf-8")
table_name = 'my_table'
source_path = 'C:\path\to\csv\test.csv'
q = ("BULK INSERT " + table_name + " " +
     "FROM '" + source_path + "' " +
     "WITH ( DATAFILETYPE = 'char', " +
     "FIELDTERMINATOR = ',', " +
     "ROWTERMINATOR = '\\n')")
connection = engine.connect()
connection.execute(q)
connection.close()

      

        
        
        
      

    

The error I am getting:

(pymssql.OperationalError) (4861, Cannot bulk load because the file "C:\\path\\to\\csv\\test.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).DB-Lib error message 20018…

I believe this is due to the double backslash issue. Backslashes are literally included in the BULK INSERT statement instead of being translated into a single backslash. I don't see anything in the SQL Server error log.

My setup is using MS SQL Server Express 2014 and Python 2.7.9 running in a separate Windows box.

I tried using raw strings ( r'C:\path\to\csv\test.csv

) and string functions decode

on source_path

, but none of them helped.