Python execute stored procedure

Execute PostgreSQL Stored Procedure and Function in Python

In this lesson, you will learn how to execute a PostgreSQL function and Stored procedure in Python. PostgreSQL function can perform different operations; it can be data manipulation or data retrieval. Let’s see how to execute such functions from Python.

Table of contents

Prerequisites

Before executing the following program, please make sure you have the following in place:

• Username and password that you need to connect to PostgreSQL.
• PostgreSQL database Stored procedure or function name which you want to execute.

For this lesson, I have created a function get_production_Deployment in PostgreSQL, which returns a list of employee records who deployed code changes in the production environment.

CREATE OR REPLACE FUNCTION get_production_deployment(appId integer) RETURNS TABLE(empId INTEGER, empName VARCHAR, designation VARCHAR) AS $ BEGIN RETURN QUERY SELECT employee.id, employee.name, employee.designation FROM employee where employee.id = (SELECT empId FROM prod_movement where prod_movement.appId = appId) END; $ LANGUAGE plpgsql;

Steps to call PostgreSQL Function and stored procedure from Python

We are using a psycopg2 module to execute the PostgreSQL function in Python.

How to execute PostgreSQL functions and stored procedure in Python

1. Import psycopg2 Install psycopg2 using pip install psycopg2 and import it in your file.
2. Connect to PostgreSQL from Python Refer to Python PostgreSQL database connection to connect to PostgreSQL database from Python using PSycopg2.
3. Get Cursor Object from Connection Next, use a connection.cursor() method to create a cursor object. This method creates a new psycopg2.extensions.cursor object.
4. Execute the stored procedure or function Execute the stored procedure using the cursor.callproc() . here, you must know the stored procedure name and its IN and OUT parameters. For example, cursor.callproc(‘Function_name’,[IN and OUT parameters,]) IN and OUT parameters must be separated by commas.
5. Fetch results Once the stored procedure executes successfully, we can extract the result using a fetchall().
   Process The result returned by the callproc() . It may be database rows or just an affected row count. Alternatively, it can be anything as per the implementation of the function.
6. Close the cursor object and database connection object use cursor.clsoe() and connection.clsoe() method to close the PostgreSQL connections after your work completes.

Example to execute PostgreSQL Function and stored procedure

Let see the demo now. We already created the stored procedure get_production_Deployment , which accepts the application id as an IN parameter and returning its employee id, employee name, and designation as the OUT parameters.

import psycopg2 try: ps_connection = psycopg2.connect(user="postgres", password="pass@#29", host="127.0.0.1", port="5432", database="postgres_db") cursor = ps_connection.cursor() # call stored procedure cursor.callproc('get_production_Deployment', [72, ]) print("fechting Employee details who pushed changes to the production from function") result = cursor.fetchall() for row in result: print("Id = ", row[0], ) print("Name = ", row[1]) print("Designation = ", row[2]) except (Exception, psycopg2.DatabaseError) as error: print("Error while connecting to PostgreSQL", error) finally: # closing database connection. if ps_connection: cursor.close() ps_connection.close() print("PostgreSQL connection is closed") 

fechting Employee details who pushed changes to the production from function Id = 23 Name = Scot Designation = Application Developer PostgreSQL connection is closed

We can also use Python cursor’s fetchall(), fetchmany(), fetchone() methods depending on the return value from a function or a stored procedure.

Also, cursor.callproc() internally uses execute() method of the cursor object to call a stored procedure. So you can directly execute the following query to call stored procedure instead of using cursor.callproc()

cursor.execute("SELECT * FROM get_production_Deployment( %s); ", (appId, ))

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник

Python Execute MySQL Stored Procedure

In this lesson, you will learn how to execute MySQL stored procedures in Python.

Further Reading:

Table of contents

Prerequisites

Before moving further, make sure you have the following in place.

• Username and password that you need to connect to MySQL
• MySQL stored procedure name which you want to call.

For this lesson, I have created a stored procedure get_laptop under the “Electronics” database. This procedure fetches laptop details from the Laptop table based on the laptop id passed as an IN parameter

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your SQL operations.

If you already know the stored procedure you want to execute, you can skip the following query. Else, open the MySQL console and run the below query to create a MySQL Stored Procedure.

DELIMITER $ USE Electronics$ CREATE * from Laptop where $ DELIMITER ;

Now you know the stored procedure to execute, so let’s move to our example.

Note: We are using the MySQL Connector Python module to execute a MySQL Stored Procedure.

Steps to execute MySQL Stored Procedure in Python

To call MySQL stored procedure from Python, you need to follow these steps: –

How to execute MySQL stored procedure in Python

1. Connect to MySQL from Python Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module
2. Get Cursor Object from Connection Next, use a connection.cursor() method to create a cursor object. This method creates a new MySQLCursor object.
3. Execute the stored procedure Execute the stored procedure using the cursor.callproc() . here, you must know the stored procedure name and its IN and OUT parameters. For example, cursor.callproc(‘get_laptop’,[1,])
4. Fetch results Once the stored procedure executes successfully, we can extract the result using a cursor.stored_results()
5. Close the cursor object and database connection object use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Now, let see the example. In this example, we are going to execute the get_laptop stored procedure using Python.

import mysql.connector from mysql.connector import Error try: connection = mysql.connector.connect(host='localhost', database='Electronics', user='pynative', password='pynative@#29') cursor = connection.cursor() cursor.callproc('get_laptop', [1, ]) # print results print("Printing laptop details") for result in cursor.stored_results(): print(result.fetchall()) except mysql.connector.Error as error: print("Failed to execute stored procedure: <>".format(error)) finally: if (connection.is_connected()): cursor.close() connection.close() print("MySQL connection is closed")

You should get the following output.

Printing laptop details [(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))] MySQL connection is closed

Note: Also, catch any SQL exceptions that may occur during this process.
Use the MySQL Connector module’s Error class, which shows us an error when we failed to execute a stored procedure. Example ER_ACCESS_DENIED_ERROR when username or password is wrong.

Cursor callproc() Method

result_args = cursor.callproc(proc_name, args=())

The callproc() method calls the stored procedure mentioned in the proc_name argument. The args sequence of parameters must contain one entry for each argument that the procedure expects. For example, the procedure can have one or many IN and OUT parameters.

The callproc() returns a modified copy of the input sequence. This method doesn’t change the input parameters. However, It can replace the output and input/output parameters with new values as per the execution result.

The stored procedure returns the output in result sets, and It is automatically fetched and stored as MySQLCursorBuffered instances.

For example, a stored procedure “addition” takes two parameters, adds the values, and returns the sum.

CREATE PROCEDURE add_num(IN num1 INT, IN num2 INT, OUT sum INT) BEGIN SET sum := num1 + num2; END;

The following example shows how to execute this Add procedure in Python.

args = (5, 6, 0) # 0 is to hold value of the OUT parameter sum cursor.callproc('add_num', args)

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник

How to execute SQL Server stored procedure from Python?

In an earlier article we have seen how to execute a simple select query from a python program on a SQL Server Database. Now we will see how to execute a Stored Procedure from python. For the most part, executing a stored procedure is similar to a select statement. You just need to add parameters and its values during the execution. Let’s see with an illustration.

Prerequisites

Please follow my earlier article about connecting SQL Server from python for:

1. Tools & technologies used.
2. Installing Microsoft ODBC Driver for SQL Server On macOS.
3. Installing Pyodbc Module.
4. Getting the database connection details.
5. Connecting to SQL Server database.

Create A Stored Procedure

To start with, let us create a simple stored procedure. I am using SQL Server’s sample database WideWorldImporters for this illustration.This stored procedure fetches the list of customers from the Sales.Customers table. There are two parameters. One parameter is for getting the search term to filter the customer names. The other parameter is the number of records to fetch. This stored procedure returns two columns, CustomerID and CustomerName.

CREATE PROCEDURE [Sales].[Mtb_GetCustomers] @SearchText nvarchar(100), @MaximumRowsToReturn int AS BEGIN SELECT TOP(@MaximumRowsToReturn) c.CustomerID, c.CustomerName FROM Sales.Customers AS c WHERE c.CustomerName LIKE N'%' + @SearchText + N'%' ORDER BY c.CustomerName; END; GO

Python Program

Here is the sample python code to execute the stored procedure and fetch a few rows from a table and print the data. In this the variable storedProc has the stored procedure execution script. In the script you have to replace the parameter value with question mark (?). In the params variable holds the parameter values in an array.

import pyodbc as po # Connection variables server = 'localhost' database = 'WideWorldImporters' username = 'sa' password = 'MyPassword' try: # Connection string cnxn = po.connect('DRIVER=;SERVER=' + server+';DATABASE='+database+';UID='+username+';PWD=' + password) cursor = cnxn.cursor() # Prepare the stored procedure execution script and parameter values storedProc = "Exec [Sales].[Mtb_GetCustomers] @SearchText = ?, @MaximumRowsToReturn = ?" params = ("And", 10) # Execute Stored Procedure With Parameters cursor.execute( storedProc, params ) # Iterate the cursor row = cursor.fetchone() while row: # Print the row print(str(row[0]) + " : " + str(row[1] or '') ) row = cursor.fetchone() # Close the cursor and delete it cursor.close() del cursor # Close the database connection cnxn.close() except Exception as e: print("Error: %s" % e)

Reference

Источник