Trong bài viết này, tôi sẽ chỉ cho bạn cách gọi thủ tục lưu sẵn trong cơ sở dữ liệu SQL Server trong ứng dụng Python. Sơ đồ sau đây cho thấy các gói điển hình có thể được sử dụng
Tôi sẽ sử dụng mô-đun pymssql trong ví dụ sau
Môi trường máy chủ SQL
Tôi đang sử dụng phiên bản SQL Server cục bộ với cơ sở dữ liệu có tên là test. Trong cơ sở dữ liệu này, có một bảng có tên [dbo]. [customer] với hai thuộc tính id và customer_name.
T-SQL sau truy vấn bảng và lọc trên cột id
SELECT [id]
,[customer_name]
FROM [test].[dbo].[customer]
where id=101;
Kết quả trả về
Một thủ tục có tên là dbo. getCustomerName được tạo bằng T-SQL sau
-- Create a new stored procedure called 'getCustomerName' in schema 'dbo'
-- Drop the stored procedure if it already exists
IF EXISTS [
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'getCustomerName'
AND ROUTINE_TYPE = N'PROCEDURE'
]
DROP PROCEDURE dbo.getCustomerName
GO
-- Create the stored procedure in the specified schema
CREATE PROCEDURE dbo.getCustomerName
@customerID /*parameter name*/ int /*datatype_for_param1*/
AS
BEGIN
-- body of the stored procedure
SELECT [id]
,[customer_name]
FROM [test].[dbo].[customer]
where id=@customerID;
END
GO grant EXEC on dbo.getCustomerName to zeppelin;
Chạy T-SQL sau để kiểm tra quy trình
EXECUTE dbo.getCustomerName 101;
Nó trả về kết quả giống như câu lệnh SELECT trước đó. Bây giờ chúng ta sẽ sử dụng Python để gọi thủ tục này.
Máy chủ có thể được xác thực bằng thông tin đăng nhập SQL sau
- tên tài khoản. khí cầu
- mật khẩu mở khóa. khí cầu
Cài đặt mô-đun pymssql
Cài đặt gói Python cần thiết bằng lệnh sau
pip install pymssql
Các bản ghi in ra trông giống như ảnh chụp màn hình sau
Sử dụng mô-đun pymssql
Bây giờ, hãy sử dụng mô-đun này để kết nối với SQL Server và gọi thủ tục
import pymssql
import pandas as pd
# Establish the connection
conn = pymssql.connect[server='.',
user='zeppelin', password='zeppelin', database='test']
cursor = conn.cursor[as_dict=True]
# Call procedure
customerId = 101
cursor.callproc['dbo.getCustomerName', [customerId,]]
# Convert the result to DataFrame
rows = []
for row in cursor:
rows.append[row]
df = pd.DataFrame[rows]
print[df]
# Close cursor and connection
cursor.close[]
conn.close[]
Đầu ra
id tên_khách_hàng
0 101 Raymond
Trong đoạn script trên, Con trỏ. hàm callproc được sử dụng trực tiếp để gọi thủ tục. Tham số đầu tiên là tên thủ tục và tham số thứ hai là các tham số [ở định dạng tuple]
Để gọi thủ tục được lưu trữ từ ứng dụng Python, hãy sử dụng ibm_db. hàm callproc . Thủ tục mà bạn gọi có thể bao gồm tham số đầu vào [IN], tham số đầu ra [OUT] và tham số đầu vào và đầu ra [INOUT].
Trước khi bắt đầu
Lấy tài nguyên kết nối bằng cách gọi một trong các chức năng kết nối trong API ibm_db
Thủ tục
Gọi ibm_db. hàm callproc bằng cách chuyển các đối số được liệt kê.
connectionMột tài nguyên kết nối cơ sở dữ liệu hợp lệ được trả về từ ibm_db. kết nối hoặc ibm_db. hàm pconnect . procnameMột tham số tên thủ tục được lưu trữ hợp lệMột bộ tham số khớp với các tham số được khai báo trong thủ tục được lưu trữ.
Thí dụ
Để gọi một thủ tục được lưu trữ với ibm_db. hàm callproc .
import ibm_db
conn = ibm_db.connect["sample", "username", "password"]
if conn:
name = "Peaches"
second_name = "Rickety Ride"
weight = 0
print "Values of bound parameters _before_ CALL:"
print " 1: %s 2: %s 3: %d\n" % [name, second_name, weight]
stmt, name, second_name, weight = ibm_db.callproc[conn, 'match_animal', [name, second_name, weight]]
if stmt is not None:
print "Values of bound parameters _after_ CALL:"
print " 1: %s 2: %s 3: %d\n" % [name, second_name, weight]Phải làm gì tiếp theo
Nếu lệnh gọi thủ tục trả về một hoặc nhiều tập kết quả, bạn có thể bắt đầu tìm nạp các hàng từ tài nguyên câu lệnh#**********************************************************************
# FILENAME : CallSPWithParam.py
#
# DESCRIPTION :
# Simple ODBC example to SELECT data from a table via a
# stored procedure which takes one input parameter.
#
# Illustrates a basic call with one parameter, in the form :
#
# {CALL Find_Record [?]}
#
# ODBC USAGE :
# Drops and recreates a procedure 'pyFind_Record'
#
#
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pyFind_Record'
# Loops asking user to input a PersonID [until zero or
# invalid number entered].
# Executes the procedure using cursor.execute[]
# Calls cursor.fetchall[] to retrieve any row found
# For each row, displays column values
# Closes and deletes cursor and closed connection
#
import pyodbc
# Function to display the contents of a row
def printRec [rec]:
print "\nPersonID : ", rec[0]
print "First Name : ", # Comma on end stops new line being output
if rec[1]!=None: # None appears for empty column
print rec[1][0:10] # Print string from 0 upto 10
else:
print "-" # Print - for empty column
print "Last Name : ",
if rec[2]!=None:
print rec[2][0:10]
else:
print "-"
print "Address : ",
if rec[3]!=None:
print rec[3][0:10]
else:
print "-"
print "City : ",
if rec[4]!=None:
print rec[4][0:10]
else:
print "-"
# Create Stored Procedure 'pyInsert_Record' Statement
sqlCreateSP="CREATE PROCEDURE pyFind_Record [@pPersonID INT] \
AS SELECT PersonID, FirstName, LastName, Address, City \
FROM TestTBL1 WHERE PersonID=@pPersonID"
# Drop Stored Procedure Statement
sqlDropSP="IF EXISTS [SELECT * FROM sys.objects \
WHERE type='P' AND name='pyFind_Record'] \
DROP PROCEDURE pyFind_Record"
# Call Stored Procedure Statement
sqlExecSP="{call pyFind_Record [?]}"
# Connect to data source
conn=pyodbc.connect['DSN=DATASOURCE', autocommit=True]
# Create cursor associated with connection
cursor=conn.cursor[]
print "\nStored Procedure is : pyFind_Record"
# Drop SP if exists
cursor.execute[sqlDropSP]
# Create SP using Create statement
cursor.execute[sqlCreateSP]
# Loop - prompt for PersonID
while 1:
# Get PersonId to look up
userInput=raw_input["\nPerson ID : "]
# Check positive integer entered, quit if
# negative or not a number
try:
id=int[userInput]
except ValueError:
id=0
if userInput != "":
print "\nSorry, NAN"
if id