在MSSQL Server 2017的查询表中执行此命令时
insert into Register1 (logo) SELECT BulkColumn FROM Openrowset( Bulk 'C:\\Users\Vitriv-Desktop\Desktop\Capture.JPG', Single_Blob) as img
结果:(1 row affected)
来自python shell:在与数据库正确建立之后
^{pr2}$结果为:
>>> 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.).')
>>>
然后我用
SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk r'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.).')
我们如何解决上述问题,即使用MSSQL而不是pythonshell。在
试过了
SQLInsertImg = "insert into Register1 (logo) SELECT BulkColumn FROM Openrowset (Bulk r"C:\Users\PC\Desktop\Capture.JPG", Single_Blob) as img"
我得到的结果:
>>> 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
目前没有回答
相关问题 更多 >
编程相关推荐