Using SQL Server file streaming in Python

Question

I am attempting to use SQL Server 2017 filestream in python. All of the functionality i use goes through sqlalchemy, thus i am attempting to find a way of using this, since i haven't found any implementation within sqlalchemy or other libraries (may have missed something, if so please point me to a working and tested implementation).

I have decided to approach this using the dll, based on https://github.com/VisionMark/django-mssql-filestream/blob/master/sql_filestream/win32_streaming_api.py . However, my call to the OpenSqlFilestream fails and returns -1 instead of file handle. I have no idea what the issue is or how to fix it.

from ctypes import c_char, sizeof, windll
from sqlalchemy import create_engine
from sqlalchemy.orm import session_maker
import msvcrt
import os

msodbcsql = windll.LoadLibrary("C:\Windows\System32\msodbcsql17.dll") 

engine = create_engine("mssql+pyodbc://user:pass@test/test?TrustedConnection=yes+driver=ODBC Driver+17+for+SQL+Server")
maker = session_maker(bind=engine)
session = session_maker()
## first query should begind transaction
path = session.execute("SELECT file_stream.PathName() FROM test_filetable").fetchall()[0][0]
## this returns str like "\\\\test\\*"
context = session.execute("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()").fetchall()[0][0]
## returns bytes

_context = (c_char*len(context)).from_buffer_copy(context)

## This call fails
handle = msodbcsql.OpenSqlFilestream(
        path, # FilestreamPath
        0, # DesiredAccess
        0, # OpenOptions
        _context, # FilestreamTransactionContext
        sizeof(_context), # FilestreamTransactionContextLength
        0 # AllocationSize
    )
## this returns -1 instead of handle

## Never reached, but this should create usable file
desc = msvcrt.open_osfhandle(fsHandle, os.O_RDONLY)
_file = os.fdopen(desc, 'r')

All of the queries work and output (as far as i understand) correct data.

How do i obtain filestream access to a file on SQL Server 2017 from python (3.7)?

Edit: The objects i read go to the size of gigabytes and the process only needs stream access.

Answer 1

My guess is that your issue is related to

1. the fact that a SQLAlchemy Session is much more than just a raw DB API Connection, and/or
2. the transaction context is not appropriate for your invocation of OpenSqlFilestream

For what it's worth, the following works for me with CPython 3.7.2 and pythonnet 2.4.0:

import clr
clr.AddReference("System.Data")
from System.Data import IsolationLevel
from System.Data.SqlClient import SqlCommand, SqlConnection
from System.Data.SqlTypes import SqlFileStream
from System.IO import File, FileAccess, FileOptions

# adapted from c# code at
# https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/filestream-data
connection_string = r"Data Source=(local)\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True"
con = SqlConnection(connection_string)
con.Open()
sql = """\
SELECT Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM employees WHERE EmployeeID = 1"""
cmd = SqlCommand(sql, con)
tran = con.BeginTransaction(IsolationLevel.ReadCommitted)
cmd.Transaction = tran
rdr = cmd.ExecuteReader()
rdr.Read()
path = rdr.GetString(0)
transaction_context = rdr.GetSqlBytes(1).Buffer
rdr.Close()
allocation_size = 0
input_stream = SqlFileStream(path, transaction_context,
        FileAccess.Read, FileOptions.SequentialScan, allocation_size)
output_stream = File.Create(r"C:\Users\Gord\Desktop\photo.bmp")
input_stream.CopyTo(output_stream)
output_stream.Close()
input_stream.Close()
tran.Commit()
con.Close()