Python-сообщество

Можно ли заменить такой ужас:

cursor.execute('CALL my_proc ('+'\''+var1+'\', '+'\''+var2+'\', '+'\''+var3+'\', '+'\''+var4+'\'); COMMIT;')

Для вызова процедур у курсора есть функция callproc. Читай доки по DB API 2 и MySQLdb. :)

Цитата с http://www.python.org/dev/peps/pep-0249/

.callproc(procname)

(This method is optional since not all databases provide stored procedures. )

Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.

The procedure may also provide a result set as output. This must then be made available through the standard fetchXXX() methods.

import sys
import Sybase
db = Sybase.connect(..., auto_commit=True)
db.execute('''
if exists (select name from sysobjects where name = "sp_test_leak")
begin
    drop procedure sp_test_leak
end
''')
db.execute('''
create procedure sp_test_leak
    @arg int
as
    select @arg
''')
for i in range(200):
    for j in range(1000):
        c = db.cursor()
        c.callproc('sp_test_leak', {'@arg': 12345 })
    sys.stdout.write('%3d\r' % i)
    sys.stdout.flush()
sys.stdout.write('\n')

Отредактировано (Фев. 4, 2008 00:14:17)

AlexKiriukha
Конкретно по MySQL примера не нашёл, но есть по Sybase и по идее должно работать и в MySQL.

slivlen
Не факт smile В cx_Oracle н-р совершенно другой стиль передачи аргументов в хранимые процедуры и функции.

DELIMITER $$
DROP PROCEDURE IF EXISTS getCitiesForCountry$$
CREATE PROCEDURE getCitiesForCountry(IN countryName CHAR(52))
BEGIN
	SELECT City.Name, City.District, City.Population FROM Country c
		LEFT JOIN City ON c.Code = City.CountryCode
		WHERE c.Name = countryName;
END$$
DELIMITER ;

import MySQLdb
db = MySQLdb.connect(db = 'world', host = '127.0.0.1', user = 'root', passwd = 'Hm...')
c = db.cursor()
c.callproc('getCitiesForCountry', ('United Kingdom',))
for row in c:
	print 'Name: %s District: %s Population: %s' % (row[0], row[1], row[2])

Compatibility warning: The act of calling a stored procedure
itself creates an empty result set. This appears after any
result sets generated by the procedure. This is non-standard
behavior with respect to the DB-API. Be sure to use nextset()
to advance through all result sets; otherwise you may get
disconnected.

А как получить значения OUT или INOUT аргументов процедуры?

slivlen
А как получить значения OUT или INOUT аргументов процедуры?

Compatibility warning: PEP-249 specifies that any modified
parameters must be returned. This is currently impossible
as they are only available by storing them in a server
variable and then retrieved by a query. Since stored
procedures return zero or more result sets, there is no
reliable way to get at OUT or INOUT parameters via callproc.
The server variables are named @_procname_n, where procname
is the parameter above and n is the position of the parameter
(from zero). Once all result sets generated by the procedure
have been fetched, you can issue a SELECT @_procname_0, …
query using .execute() to get any OUT or INOUT values

DELIMITER $$
DROP PROCEDURE IF EXISTS p$$
CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END$$
DELIMITER ;

import MySQLdb
db = MySQLdb.connect(db = 'world', host = '127.0.0.1', user = 'root', passwd = 'password')
c = db.cursor()
c.callproc('p', ('', 12))
c.execute('SELECT @_p_0, @_p_1')
for row in c:
	print 'Version: %s Increment: %s' % (row[0], row[1])