Блог

По какой-то странной причине я не могу получить результаты от вызова callproc в тестовом приложении Python. Хранимая процедура в MqSQL 5.2.47 выглядит следующим образом:

 CREATE PROCEDURE `mytestdb`.`getperson` (IN personid INT)
BEGIN
   select person.person_id,
          person.person_fname,
          person.person_mi,
          person.person_lname,
          person.persongender_id,
          person.personjob_id
     from person
    where person.person_id = personid;
END
  

Теперь, используя PyCharm с Python 3.3, я, похоже, ничего не могу извлечь при вызове этой хранимой процедуры. Этот код дает мне желаемые результаты:

 import mysql.connector

cnx = mysql.connector.connect(user='root', host='127.0.0.1', database='mytestdb')
cnx._open_connection()
cursor = cnx.cursor()

cursor.execute("select * from person where person.person_id = 1")
people = cursor.fetchall()

for person in people:
    print(person)

cnx.close()
  

Но этот код с любым курсором.fetchall() или cursor.fetchone()…

 import mysql.connector

cnx = mysql.connector.connect(user='root', host='127.0.0.1', database='mytestdb')
cnx._open_connection()
cursor = cnx.cursor()

cursor.callproc("getperson", [1])
people = cursor.fetchall()

for person in people:
    print(person)

cnx.close()
  

… возвращает «mysql.connector.errors.Ошибка интерфейса: нет результирующего набора для извлечения.» Существует дополнительное странное поведение при использовании метода cursor.execute(), подобного этому…

 import mysql.connector

cnx = mysql.connector.connect(user='root', host='127.0.0.1', database='mytestdb')
cnx._open_connection()
cursor = cnx.cursor()

cursor.execute("call getperson(1)")
people = cursor.fetchall()

for person in people:
    print(person)

cnx.close()
  

… потому что это приводит к «mysql.connector.errors.Ошибка интерфейса: Используйте cmd_query_iter для инструкций с несколькими запросами, за которыми «следует»mysql.connector.errors.Ошибка интерфейса: используйте multi = True при выполнении нескольких инструкций » несмотря на то, что я возвращаю только один результат запроса, а не несколько наборов результатов. Обрабатывает ли соединитель MySQL Python вызов execute для хранимой процедуры как двойной запрос? Как я могу просто вызвать хранимую процедуру и получить свои результаты обратно? Я действительно не хочу динамический SQL в своем коде. Заранее спасибо за любой совет!

Вы пробовали выбирать один из наборов результатов?

 for result in cursor.stored_results():
    people = result.fetchall()
  

Возможно, он выделяет для нескольких наборов результатов, даже если у вас есть только один SELECT stmt. Я знаю, что хранимые процедуры MySQLi в PHP делают это, чтобы разрешить возврат переменных INOUT и OUT (которых, опять же, у вас нет, но, возможно, они все равно выделяются).

Полный код, который я использую (который работает), является:

 import mysql.connector

cnx = mysql.connector.connect(user='me',password='pw',host='localhost',database='mydb')
cnx._open_connection()
cursor = cnx.cursor()

cursor.callproc("getperson",[1])

for result in cursor.stored_results():
    people=result.fetchall()

for person in people:
    print person

cnx.close()
  

Получение результата хранимой процедуры после вызова cursor.callproc зависит от этих факторов:

• присваивается ли результат вызова процедуры параметру INOUT или OUT
• состоит ли результат из одной строки или результирующего набора (или результирующих наборов)
• пакет python, используемый для выполнения вызова

В спецификации DBAPI об этом говорится cursor.callproc :

Вызовите хранимую процедуру базы данных с заданным именем. Последовательность параметров должна содержать одну запись для каждого аргумента, ожидаемого процедурой. Результат вызова возвращается как измененная копия входной последовательности. Входные параметры оставлены нетронутыми, выходные параметры заменены, возможно, новыми значениями.

Процедура также может предоставлять результирующий набор в качестве выходных данных. Затем это должно быть доступно через стандарт .методы выборки *().

На практике использование возвращаемого значения cursor.callproc может работать только в том случае, если процедура возвращает одну строку с количеством столбцов, соответствующим количеству параметров INOUT и OUT, поэтому существуют некоторые различия в способе обработки результатов.

Вот как эти случаи обрабатываются основными пакетами MySQL Python connector — MySQL Connector, mysqlclient (MySQLdb) и PyMySQL.

Результат в одной строке, возвращаемый с помощью параметров INOUT или OUT

• Соединитель MySQL возвращает измененную копию входной последовательности в качестве возвращаемого значения cursor.callproc ; значение является кортежем.

   params = [in_param, out_param1, out_param2]
  in_, out1, out2 = cursor.callproc("test_proc", params) 
    

• mysqlclient и PyMySQL требуют запроса выходных параметров к базе данных, а затем извлечения результатов с помощью курсора; значение представляет собой набор кортежей. Имена параметров, которые будут запрошены, имеют вид '@_{procedure_name}_{params.index(param)}'

   cursor.callproc("test_proc", params)
  cursor.execute("""SELECT @_test_proc_0, @_test_proc_1""")
  result = cursor.fetchall()
    

Одна или несколько строк в одном результирующем наборе, параметры INOUT или OUT не определены

• Соединитель MySQL предоставляет результат с помощью метода stored_results cursor.stored_results курсора (не является частью спецификации DBAPI)

   cursor.callproc("test_proc", params)
  results = [r.fetchall() for r in cursor.stored_results()]
    

• mysqlclient и PyMySQL предоставляют результат с помощью методов выборки курсора *

   cursor.callproc("test_proc", params)
  results = cursor.fetchall()
    

Несколько наборов результатов, не определены параметры INOUT или OUT

• Соединитель MySQL предоставляет результат с помощью stored_results метода курсора

   cursor.callproc("test_proc", params)
  results = [r.fetchall() for r in cursor.stored_results()]
    

• mysqlclient и PyMySQL требуют, чтобы каждый результирующий набор извлекался с помощью курсора при вызове cursor.nextset для перехода к следующему результирующему набору. Обратите внимание, что в результате вызова процедуры может быть возвращен дополнительный пустой результирующий набор (это также произошло бы в предыдущих примерах, если бы результирующий набор был получен через cursor.nextset вместо того, чтобы вызываться cursor.fetchall только один раз).

   cursor.callproc("test_proc", params)
  results = [cursor.fetchall()]
  while cursor.nextset():
      results.append(cursor.fetchall())
    

Информация о версии

 $ mysql --version
mysql  Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

$ pip list | grep -i mysql
mysql-connector-python 8.0.18 
mysqlclient            1.4.6  
PyMySQL                0.9.3  
  

Почему бы не попробовать это таким образом

 cursor.callproc("getperson", ['1'])