Create an excel file from BytesIO using python

Question

I am using pandas library to store excel into bytesIO memory. Later, I am storing this bytesIO object into SQL Server as below-

    df = pandas.DataFrame(data1, columns=['col1', 'col2', 'col3'])
    output = BytesIO()
    writer = pandas.ExcelWriter(output,engine='xlsxwriter')
    df.to_excel(writer)
    writer.save()
    output.seek(0)
    workbook = output.read()

    #store into table
    Query = '''
            INSERT INTO [TABLE]([file]) VALUES(?)
            '''
    values = (workbook)
    cursor = conn.cursor()
    cursor.execute(Query, values)
    cursor.close()
    conn.commit()

   #Create excel file.
   Query1 = "select [file] from [TABLE] where [id] = 1"
   result = conn.cursor().execute(Query1).fetchall()
   print(result[0])

Now, I want to pull the BytesIO object back from table and create an excel file and store it locally. How Do I do it?

Answer 1

Finally, I got solution.Below are the steps performed:

1. Takes Dataframe and convert it to excel and store it in memory in BytesIO format.
2. Store BytesIO object in Database column having varbinary(max)
3. Pull the stored BytesIO object and create an excel file locally.

Python Code:

#Get Required data in DataFrame:
df = pandas.DataFrame(data1, columns=['col1', 'col2', 'col3'])

#Convert the data frame to Excel and store it in BytesIO object `workbook`:
output = BytesIO()
writer = pandas.ExcelWriter(output,engine='xlsxwriter')
df.to_excel(writer)
writer.save()
output.seek(0)
workbook = output.read()

#store into Database table
Query = '''
        INSERT INTO [TABLE]([file]) VALUES(?)
        '''
values = (workbook)
cursor = conn.cursor()
cursor.execute(Query, values)
cursor.close()
conn.commit()

#Retrieve the BytesIO object from Database
Query1 = "select [file] from [TABLE] where [id] = 1"
result = conn.cursor().execute(Query1).fetchall()

WriteObj = BytesIO()
WriteObj.write(result[0][0])  
WriteObj.seek(0)  
df = pandas.read_excel(WriteObj)
df.to_excel("outputFile.xlsx")