SQL query to insert image file into MSSQL database using API: Python

Question

In query sheet of MSSQL Server 2017 when we execute this command

insert into Register1 (logo) SELECT BulkColumn FROM Openrowset( Bulk 'C:\\Users\Vitriv-Desktop\Desktop\Capture.JPG', Single_Blob) as img

Result: (1 row affected)

From python shell: After establishing with database correctly

import pypyodbc

cnxn = pypyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                    'Server=DESKTOP-C6RS3DO;'
                    'Database=demo2016;'
                    'uid=sa;pwd=sa')
print('connected <br/>')
cursor = cnxn.cursor()

SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk 'C:\\Users\PC\Desktop\Capture.JPG', Single_Blob) as img"
cursor.execute(SQLInsertImg)

cnxn.commit()
print("Query Inserted Successfully...!")

Result will be:

>>> SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk 'C:\\Users\PC\Desktop\Capture.JPG', Single_Blob) as img"
>>> cursor.execute(SQLInsertImg)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1626, in execute
    self.execdirect(query_string)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1652, in execdirect
    check_success(self, ret)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1007, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 975, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot bulk load because the file "C:\\Users\\PC\\Desktop\\Capture.JPG" could not be opened. Operating system error code 3(The system cannot find the path specified.).')
>>>

Then I modified the above code with

SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk r'C:\Users\PC\Desktop\Capture.JPG', Single_Blob) as img"

Results in:

>>> cursor.execute(SQLInsertImg)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1626, in execute
    self.execdirect(query_string)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1652, in execdirect
    check_success(self, ret)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1007, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 975, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot bulk load because the file "C:\\Users\\PC\\Desktop\\Capture.JPG" could not be opened. Operating system error code 3(The system cannot find the path specified.).')

How do we resolve the above issue i.e, works with MSSQL but not with python shell.

Tried with

SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk r"C:\Users\PC\Desktop\Capture.JPG", Single_Blob) as img"

Result that I got:

>>> SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk r"C:\Users\PC\Desktop\Capture.JPG ", Single_Blob) as img"
  File "<stdin>", line 1
    SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk r"C:\Users\PC\Desktop\Capture.JPG ", Single_Blob) as img"
                                                                                           ^
SyntaxError: invalid syntax
>>> cursor.execute(SQLInsertImg)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1626, in execute
    self.execdirect(query_string)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1652, in execdirect
    check_success(self, ret)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 1007, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\pypyodbc.py", line 975, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'C:'.")
>>>

SQLInsertImg = insert into Register1 (logo) SELECT BulkColumn FROM OPENROWSET (BULK 'C:\\Capture.JPG', SINGLE_NCLOB) as img

>>> import pypyodbc
>>>
>>> cnxn = pypyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
...                  'Server=DESKTOP-C6RS3DO;'
...                   'Database=demo2016;'
...                   'uid=sa;pwd=sa')
>>> print('connected <br/>')
connected <br/>
>>> cursor = cnxn.cursor()
>>> SQLInsertImg = insert into Register1 (logo) SELECT BulkColumn FROM OPENROWSET (BULK 'C:\\Capture', SINGLE_NCLOB) as img
  File "<stdin>", line 1
    SQLInsertImg = insert into Register1 (logo) SELECT BulkColumn FROM OPENROWSET (BULK 'C:\\Capture', SINGLE_NCLOB) as img
                             ^
SyntaxError: invalid syntax

@dsgdfg updated with double quotes could you see the results — Nɪsʜᴀɴᴛʜ ॐ, Dec 21 '17 at 06:36
okay, [Check this](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c3ba4af7-bbb4-4471-815b-10b8a5403d8c/operating-system-error-code-3the-system-cannot-find-the-path-specified?forum=transactsql) , so **You cannot access directories other than the server.** — dsgdfg, Dec 21 '17 at 06:48
@dsgdfg No its in the >>> SQLInsertImg = insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk 'C:\\Capture', Single_Blob) as img File "", line 1 SQLInsertImg = insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk 'C:\\Capture', Single_Blob) as img ^ SyntaxError: invalid syntax — Nɪsʜᴀɴᴛʜ ॐ, Dec 21 '17 at 07:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161693/discussion-between-dsgdfg-and-nishanth). — dsgdfg, Dec 21 '17 at 09:33

SQL query to insert image file into MSSQL database using API: Python

0 Answers0