How to fetch video from SQL Server database using Python?

Question

I have a SQL Server database with a table media and a column video of type varbinary(max). I want to retrieve video from that column using Python. I consider this question and answer to it, so my code looks like:

import pypyodbc
import base64
from base64 import * 

connection = pypyodbc.connect('Driver=SQL Server;'
                            'Server=SQLEXPRESS;'
                            'Database=use_filestream_db;'
                            'Trusted_Connection=yes;')

cursor = connection.cursor()

SQLCommand = ("SELECT Video FROM media")
cursor.execute(SQLCommand)
data = cursor.fetchone()[0]
data = bytes(data.strip("\n"), 'utf-8')

video_64_decode = base64.decodebytes(data)
video_result = open('landscape.mp4', 'wb')
video_result.write(video_64_decode)
video_result.close()

connection.close()

But I get

TypeError Traceback (most recent call last)
in
16 cursor.execute(SQLCommand)
17 data = cursor.fetchone()[0]
---> 18 data = bytes(data.strip("\n"), 'utf-8')
19
20 video_64_decode = base64.decodebytes(data)

TypeError: a bytes-like object is required, not 'str'

If I write something like

data = bytes(data.strip(bytes("\n", 'utf-8')), 'utf-8')

or just remove this line output video will be broken. How should I fix it?

UPD

After parfait's answer I did that he\she said and got next error:

TypeError                                 Traceback (most recent call last)
D:\programs\Anaconda\lib\base64.py in _input_type_check(s)
    509     try:
--> 510         m = memoryview(s)
    511     except TypeError as err:

TypeError: memoryview: a bytes-like object is required, not '_io.BytesIO'

The above exception was the direct cause of the following exception:

TypeError                                 Traceback (most recent call last)
<ipython-input-5-40b3d6ca2642> in <module>
     14 data = cursor.fetchone()[0]
     15 byte_data = BytesIO(data)
---> 16 video_64_decode = base64.decodebytes(byte_data)
     17 
     18 # USING CONTEXT MANAGER

D:\programs\Anaconda\lib\base64.py in decodebytes(s)
    543 def decodebytes(s):
    544     """Decode a bytestring of base-64 data into a bytes object."""
--> 545     _input_type_check(s)
    546     return binascii.a2b_base64(s)
    547 

D:\programs\Anaconda\lib\base64.py in _input_type_check(s)
    511     except TypeError as err:
    512         msg = "expected bytes-like object, not %s" % s.__class__.__name__
--> 513         raise TypeError(msg) from err
    514     if m.format not in ('c', 'b', 'B'):
    515         msg = ("expected single byte elements, not %r from %s" %

TypeError: expected bytes-like object, not BytesIO

P.S. I tried to post this stacktrace as blockquote but it was recognized as code and returned a error.

UPD 2 I found the solution, see for my answer below.

score 1 · Answer 1 · answered Apr 07 '20 at 16:58

1

Consider BytesIO from the io module to convert the raw SQL data to bytes for decoding:

from io import BytesIO

...

data = cursor.fetchone()[0]
byte_data = BytesIO(data)    
video_64_decode = base64.decodebytes(byte_data)

# USING CONTEXT MANAGER
with open('landscape.mp4', 'wb') as video_result:
   video_result.write(video_64_decode)

answered Apr 07 '20 at 16:58

Parfait

104,375
17
94
125

check UPD please – Nourless Apr 07 '20 at 18:50

score 0 · Accepted Answer · answered Apr 08 '20 at 09:15

It appeared that there is no need for some manipulation over data, it only must be forced to bytes type. So next code have worked

import pypyodbc

connection = pypyodbc.connect('Driver=SQL Server;'
                            'Server=SQLEXPRESS;'
                            'Database=use_filestream_db;'
                            'Trusted_Connection=yes;')

cursor = connection.cursor()

SQLCommand = ("SELECT Video FROM media")
cursor.execute(SQLCommand)
data = cursor.fetchone()[0]
data = bytes(data)


with open('landscape_after_db.mp4', 'wb') as video:
    video.write(data)
connection.close()

How to fetch video from SQL Server database using Python?

2 Answers2