首页 / PYTHON / Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling MySQL Stored Procedures in Python
Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling MySQL Stored Procedures in Python
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling MySQL Stored Procedures in Python，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4111字，纯文字阅读大概需要6分钟。
内容图文
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:
| 1 | CALL find_all(); |
The second stored procedure named find_by_isbn() that is used to find a book by its ISBN as follows:
| 1 2 3 4 5 6 7 8 9 | 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:
| 1 2 | 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 thecallproc()method. If the stored procedure requires parameters, you need to pass a list as the second argument to thecallproc()method. In case the stored procedure returns a result set, you can invoke thestored_results()method of the MySQLCursor object to get a list iterator and iterate this result set by using thefetchall()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.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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.
Related Tutorials
Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling MySQL Stored Procedures in Python
标签：
本文系统来源：http://www.cnblogs.com/kungfupanda/p/5937326.html
内容总结
以上是互联网集市为您收集整理的Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling MySQL Stored Procedures in Python全部内容，希望文章能够帮你解决Home / Python MySQL Tutorial / Calling MySQL Stored Procedures in Python Calling MySQL Stored Procedures in Python所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。