pymssql bulk insert error (python)

Question

Trying to bulk insert a CSV file using pymssql here's the code:

conn = pymssql.connect(host='server', user='user', password='secret', database='My_Dev')
cur = conn.cursor()
load = 'BULK INSERT TempStaging FROM \'/home/dross/python/scripts/var/csv/' + f + '.csv\' WITH (FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = \'\\n\') GO")'
cur.execute(load)

When executing get following error:

Traceback (most recent call last):
 File "./uploadResults.py", line 46, in <module>
 cur.execute(sweepload)
 File "pymssql.pyx", line 447, in pymssql.Cursor.execute (pymssql.c:7092)
 File "_mssql.pyx", line 1009, in _mssql.MSSQLConnection.execute_query (_mssql.c:11585)
 File "_mssql.pyx", line 1040, in _mssql.MSSQLConnection.execute_query (_mssql.c:11459)
 File "_mssql.pyx", line 1160, in _mssql.MSSQLConnection.format_and_run_query (_mssql.c:12652)
 File "_mssql.pyx", line 203, in _mssql.ensure_bytes (_mssql.c:2733)
AttributeError: 'tuple' object has no attribute 'encode'

Line 46 is cur.execute line

• Wayne Werner: Use triple quotes with SQL. ',',ROWTERMINATOR isn't escaped
• dross: @Wayne Werner:Using the following: load = 'BULK INSERT TempStaging FROM \'/home/dross/python/scripts/var/csv/' + filename + '.csv\' WITH (FIRSTROW = 1,FIELDTERMINATOR = \',\',ROWTERMINATOR = \'\\n\')' Is this equivalent to what you meant ?(I'm python beginner)

Answer 1

Note that .format() could allow sql injection, but if you control the filename then it's not that bad (not sure if a parameter would work here).

Also, you should use triple-quoted strings when dealing with SQL, your life will be so much better. Like this:

load = '''BULK INSERT TempStaging FROM /home/dross/python/scripts/var/csv/{}.csv WITH (FIRSTROW=1, FIELDTERMINATOR=',', ROWTERMINATOR='\n')'''.format(filename)

Being triple quoted, you can also break it up to make it easier to read:

load = '''
 BULK INSERT TempStaging
 FROM /home/dross/python/scripts/var/csv/{}.csv
 WITH (
 FIRSTROW=1
 , FIELDTERMINATOR=','
 , ROWTERMINATOR='\n'
 )
'''.format(filename)

Answer 2

You should defined the string as below:

load = "BULK INSERT TempStaging FROM /home/dross/python/scripts/var/csv/" + f + ".csv WITH ( FIRSTROW=1 , FIELDTERMINATOR=',' , ROWTERMINATOR='\\n')"