2

The problem is as follows. A matplotlib plot is created by MSSQL2019 sp_execute_external_script(the code is at the end of the post), after that the plot is serialized as the byte sequence and sent to Python script. Then at the stage of graph back conversion the error occures:

runfile('C:/Users/Gamer/Desktop/Проект архив/Анализ данных/test_g_plot.py', wdir='C:/Users/Gamer/Desktop/Проект архив/Анализ данных') Traceback (most recent call last): File "C:\Users\Gamer\Desktop\Проект архив\Анализ данных\test_g_plot.py", line 15, in fig = pickle.loads(tables[i][0]) File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\cbook_init_.py", line 195, in setstate for s, d in self.callbacks.items()} AttributeError: 'CallbackRegistry' object has no attribute 'callbacks'

SQL procedure code:

/****** Object:  StoredProcedure [dbo].[PyPlotMatplotlib]    Script Date: 22.12.2021 22:01:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PyPlotMatplotlib]
AS
BEGIN
    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import matplotlib
import pandas 
import pickle
import matplotlib.pyplot as plt

df = pandas.DataFrame({"name": ["Earth", "Moon", "Mars"], "mass_to_earth": [1, 0.606, 0.107]})
fig_handle = plt.figure()
plt.barh(df.name, df.mass_to_earth)
# plt.hist(df.mass_to_earth)
plt.xlabel("Tipped")
plt.ylabel("Counts")
plt.title("Histogram, Tipped")
#plt.savefig("1.png")

#plt.show()

plot = pandas.DataFrame(data =[pickle.dumps(fig_handle, 3)], columns =["plot"])
plt.clf()

OutputDataSet = plot
plt.clf()
'
WITH RESULT SETS ((plot varbinary(max)))
END

Python code:

# -*- coding: utf-8 -*-

import pyodbc
import pickle
import os
import pydbwork
import matplotlib.pyplot as plt

cnxn = pydbwork.connect_db("SNOUBORT", "TestPlot")
cursor = cnxn.cursor()
cursor.execute("EXECUTE [dbo].[PyPlotMatplotlib]")
tables = cursor.fetchall()
for i in range(0, len(tables)):
    fig = pickle.loads(tables[i][0])

# fig.show()
# fig.savefig('0.png')
# print("The plots are saved in directory: ",os.getcwd())

This way of realisation is used according to the article: click

As a result the plot should be saved like that:enter image description here

Snoubort
  • 21
  • 3
  • 1
    No real idea but you must pickle and I pickle with the exact same version of Matplotlib. – Jody Klymak Dec 23 '21 at 07:28
  • 1
    Thank you for your advice. The script really worked correctly after it was run in an environment with similar versions of python and matplotlib (before that, the script was run in an environment with later versions) – Snoubort Dec 23 '21 at 08:45
  • Glad it helped, despite my phone-induced typos – Jody Klymak Dec 23 '21 at 10:49

1 Answers1

0

The problem was solved by running the script in an environment with python and matplotlib versions similar to those in SQL

SQL version check:

import sys
import matplotlib as plt
print(sys.version)
print(plt.__version__)

3.7.1 (default, Dec 10 2018, 22:54:23) [MSC v.1915 64 bit (AMD64)]

3.0.2

Checking environment versions:

# -*- coding: utf-8 -*-
import sys
import matplotlib as plt
print(sys.version)
print(plt.__version__)

3.7.11 (default, Jul 27 2021, 09:42:29) [MSC v.1916 64 bit (AMD64)]

3.0.2

Thanks to Jody Klymak for the helpful advice and solution to this problem.

Snoubort
  • 21
  • 3