BULK INSERT from sql server as a query in python

Question

I have been struggling with a task as follows:

I have an active connection from my python application with sql server and I would like to perform a bulk insert operation that insert data from a specific file into my database. When I perform this directly from sql server it works fine but with python I always get the message regarding escape character '/' - it is a Windows machine.

I cannot just figure this out how to get this done: I tried: a) to replace '/' with '//' but then sql server crushed b) to create a stored procedure and refer to it in c.execute but it does not work either c) to replace '/' with '' - then sql cannot open a file d) to create a varaible with raw string r'' and pass this as a parameter into sql server code but also does not work

conn = pyodbc.connect(f'DRIVER={driver};SERVER={server};DATABASE={database};UID={user};PWD={password}')

c = conn.cursor()
c.execute('''
   BULK INSERT Namen 
   FROM 'C:\Users\pllmkd\Desktop\test.txt'
   WITH (
       FIELDTERMINATOR = ',')
''')
c.commit()


c.close()
conn.close()

What should be the approach to that problem? I don't want to read a file row by row as I have hundres of thousands row in each file i want to upload.

The error I get:

SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 36-37: truncated \UXXXXXXXX escape

Could you please suggest a solution?

Thank you in advance for any tips

Answer 1

The \ character is used to define escape sequences. You need to either escape \ itself or use a raw string by using the r prefix. From the docs:

Both string and bytes literals may optionally be prefixed with a letter 'r' or 'R'; such strings are called raw strings and treat backslashes as literal characters.

The r prefix should be applied to the entire string, not the path inside the string. Python doesn't parse the contents of the string and doesn't care about the path itself.

You should use

sql=r'''
   BULK INSERT Namen 
   FROM 'C:\Users\pllmkd\Desktop\test.txt'
   WITH (
       FIELDTERMINATOR = ',')
'''
c.execute(sql)

or

sql='''
   BULK INSERT Namen 
   FROM 'C:\\Users\\pllmkd\\Desktop\\test.txt'
   WITH (
       FIELDTERMINATOR = ',')
'''

Make sure the server account can access the file

The path passed to BULK INSERT is treated as a local file on the database server, unless it's a path to a shared folder. The file will be accessed using SQL Server's service account, which means that account needs to be given Read permission at least on that file or folder.

C:\Users\pllmkd\Desktop.txt is a local path, stored in a user's Desktop, where only that user has access. SQL Server would have to run on the same machine, using the pllmkd as the service account. The default SQL Server installation uses a low-privilege account though, not the current user's account.