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

Use triple quotes with SQL. ',',ROWTERMINATOR isn't escaped — Wayne Werner
– Wayne Werner, Commented Apr 1, 2016 at 15:39
@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) — dross
– dross, Commented Apr 1, 2016 at 16:04

Wayne Werner · Accepted Answer · 2016-04-01 16:12:06Z

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)

answered Apr 1, 2016 at 16:12

Wayne Werner

52.3k35 gold badges213 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ken · Accepted Answer · 2018-06-25 08:54:15Z

0

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')"

answered Jun 25, 2018 at 8:54

Ken

111 bronze badge

Collectives™ on Stack Overflow

pymssql bulk insert error (python)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related