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 tableQuery = '''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?
Finally, I got solution.Below are the steps performed:
- Takes Dataframe and convert it to excel and store it in memory in BytesIO format.
- Store BytesIO object in Database column having varbinary(max)
- 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")