f you are not familiar with mysql stored procedures or want to review it as a refresher, you can follow the MySQL stored procedures tutorial .

We will create two stored procedures for the demonstration in this tutorial. The first stored procedure gets all books with authors information from books and authors tables:

DELIMITER $$ USE python_mysql$$ CREATE PROCEDURE find_all() BEGIN SELECT title, isbn, CONCAT(first_name,' ',last_name) AS author FROM books INNER JOIN book_author ON book_author.book_id =books.id INNER JOIN AUTHORS ON book_author.author_id = authors.id; END$$ DELIMITER ;

The find_all() stored procedure has a SELECT statement with JOIN clauses that retrieve title, isbn and author’s full name from books and authors tables. When we execute the find_all() stored procedure, it returns a result as follows:

CALL find_all();
Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling ...

The second stored procedure named find_by_isbn() that is used to find a book by its ISBN as follows:

DELIMITER $$ CREATE PROCEDURE find_by_isbn(IN p_isbn VARCHAR(13),OUT p_title VARCHAR(255)) BEGIN SELECT title INTO p_title FROM books WHERE isbn = p_isbn; END$$ DELIMITER ;

The find_by_isbn() accepts two parameters: the first parameter is isbn (IN parameter) and second is title (OUT parameter). When you pass the isbn to the stored procedure, you will get the title of the book, for example:

CALL find_by_isbn('1235927658929',@title); SELECT @title; Calling stored procedures from Python

To call a stored procedure in Python, you follow the steps below:

Connect to MySQL database by creating a new MySQLConnection object. Instantiate a new MySQLCursor object from the MySQLConnection object by calling the cursor() method. Call callproc() method of the MySQLCursor object. You pass the stored procedure’s name as the first argument of the callproc() method. If the stored procedure requires parameters, you need to pass a list as the second argument to the callproc() method. In case the stored procedure returns a result set, you can invoke the stored_results() method of the MySQLCursor object to get a list iterator and iterate this result set by using the fetchall() method. Close the cursor and database connection as always.

The following example demonstrates how to call the find_all() stored procedure in Python and output the result set.

from mysql.connector import MySQLConnection, Error from python_mysql_dbconfig import read_db_config def call_find_all_sp(): try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.callproc('find_all') # print out the result for result in cursor.stored_results(): print(result.fetchall()) except Error as e: print(e) finally: cursor.close() conn.close() if __name__ == '__main__': call_find_all_sp()

The following example shows you how to call the find_by_isbn() stored procedure.

from mysql.connector import MySQLConnection, Error from python_mysql_dbconfig import read_db_config def call_find_by_isbn(): try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() args = ['1236400967773', 0] result_args = cursor.callproc('find_by_isbn', args) print(result_args[1]) except Error as e: print(e) finally: cursor.close() conn.close() if __name__ == '__main__': call_find_by_isbn()

The find_by_isbn() stored procedure requires two parameters therefore we have to pass a list ( args ) that contains two elements: the first one is isbn (1236400967773) and the second is 0. The second element of the args list (0) is just a placeholder to hold the p_title parameter.

The callproc() method returns a list ( result_args ) that contains two elements: the second element (result_args[1]) holds the value of the p_title parameter.

In this tutorial, we have shown you how to call stored procedures in Python by using callproc() method of the MySQLCursor object.

本文开发(python)相关术语:python基础教程 python多线程 web开发工程师 软件开发工程师 软件开发流程

主题: MySQLSQLPythonUTDUAUTAUISB
分页:12
转载请注明
本文标题:Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling ...
本站链接:http://www.codesec.net/view/480151.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 开发(python) | 评论(0) | 阅读(42)