Django execute stored procedure - different data types, multiple parameters and different columns from model

Question

Here is a list of what works and what doesn't, along with the errors. when trying to send in an decimal/money data type variable. I'm not sure if the issue is with different data types, or that more than 3 arguments are passed in. The first 2 parameters look like numbers, but they are sent in as strings. The database is SQL Server 2017

I noticed the data types of the parameters are set based on the model - does this change anything? param1 - '10', param2 is '-100' and param3: Decimal('100')

# WORKS - there's a charfield/nvarchar datatype, one param only (pyodbc/mssql)
# cursor.execute('EXEC [dbo].[sproc2] @charfield-param={}'.format(stringValue))

# DOESNT WORK
# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1=%s @charfield-param2=%s @money-decimal-param=%d', (charfield-param1, charfield-param2, 100000))
# %d format: a number is required, not str

# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1=%s @charfield-param2=%s @money-decimal-param=%s', (charfield-param1, charfield-param2, 100000))
# ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}'.format(charfield-param1, charfield-param2, 100000))
# ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW)")
# ignored_wrapper_args  
# (False,
# {'connection': <sql_server.pyodbc.base.DatabaseWrapper object at 0x04B21470>,
# 'cursor': <django.db.backends.utils.CursorDebugWrapper object at 0x04CEDD10>})
# params    
# None
# self  
# <django.db.backends.utils.CursorDebugWrapper object at 0x04CEDD10>
# sql   
# ('EXEC [dbo].[sproc1] @charfield-param1=10 @charfield-param2=-100 '
# '@money-decimal-param=100000')

# DOESNT WORK
# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1=%s @charfield-param2=%s @money-decimal-param=%s', (charfield-param1, charfield-param2, 100000))
# # ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}'.format(charfield-param1, charfield-param2, money-decimal-param3))
# ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW)")
# params    
# ()
# self  
# <sql_server.pyodbc.base.CursorWrapper object at 0x03FD46B0>
# sql   
# ('EXEC [dbo].[sproc1] @charfield-param1=10 @charfield-param2=-100 '
# '@money-decimal-param=200000')

# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}'.format(charfield-param1, charfield-param2).format(money-decimal-param3))
# ignored_wrapper_args  
# (False,
# {'connection': <sql_server.pyodbc.base.DatabaseWrapper object at 0x04019470>,
# 'cursor': <django.db.backends.utils.CursorDebugWrapper object at 0x04019790>})
# params    
# None
# self  
# <django.db.backends.utils.CursorDebugWrapper object at 0x04019790>
# sql   
# ('EXEC [dbo].[sproc1] @charfield-param1=10 @charfield-param2=-100 '
# '@money-decimal-param=200000')

cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}', (str(charfield-param1), str(charfield-param2), int(money-decimal-param3)))
# not all arguments converted during string formatting

# cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}', (str(charfield-param1), str(charfield-param2), int(100000)))
# Error: '<' not supported between instances of 'NoneType' and 'int'

Changing the stored proc signature to expect 3 charfields, instead of 2 and a money field, doesn't fix my problem. So, looks like a formatting issue? I'm very new to Python and django, so this might be a really simple one to fix!

cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @charfield-param3={}'.format(str(charfield-param1), str(charfield-param2), charfield-param3))

cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @charfield-param2={}'.format(charfield1, charfield2).format(charfield3))
IndexError: tuple index out of range

Edit: An integer is returned, along with differently named columns from different tables (not necessarily mapped to the mapped table/model).

Error details:

 cursor.execute('EXEC [dbo].[stored_proc] @Param1={} @Param2={} @NewParam3={}'.format(strparam1, strparam2,strparam3))

Environment: Local vars:

ignored_wrapper_args    
(False,
 {'connection': <sql_server.pyodbc.base.DatabaseWrapper object at 0x05E96490>,
  'cursor': <django.db.backends.utils.CursorDebugWrapper object at 0x060D51B0>})
params: None
self    : <django.db.backends.utils.CursorDebugWrapper object at 0x060D51B0>
sql: ('EXEC [dbo].[stored_proc] @Param1=10 '
 '@Param2=-100 @NewParam3=100000')

Request Method: POST
Request URL: http://localhost:8000/app-url/

Django Version: 2.1.1
Python Version: 3.7.0
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'app_name']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']

Traceback:

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute
  83.                 return self.cursor.execute(sql)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\sql_server\pyodbc\base.py" in execute
  546.             return self.cursor.execute(sql, params)

The above exception (('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@Param2'. (102) (SQLExecDirectW)")) was the direct cause of the following exception:

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\core\handlers\exception.py" in inner
  34.             response = get_response(request)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\core\handlers\base.py" in _get_response
  126.                 response = self.process_exception_by_middleware(e, request)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\core\handlers\base.py" in _get_response
  124.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\views\generic\base.py" in view
  68.             return self.dispatch(request, *args, **kwargs)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\views\generic\base.py" in dispatch
  88.         return handler(request, *args, **kwargs)

File "C:\Github\project-name\app_name\views.py" in post
  90.             results = ModelName.get_stored_proc_data(str(param1), str(param2), decimalParam3)

File "C:\Github\project-name\app_name\models.py" in get_stored_proc_data
  89.             cursor.execute('EXEC [dbo].[stored_proc] @Param1={} @Param2={} @NewParam3={}'.format(param1, param2,decimalParam3))        

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in execute
  100.             return super().execute(sql, params)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in execute
  68.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute_with_wrappers
  77.         return executor(sql, params, many, context)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\utils.py" in __exit__
  89.                 raise dj_exc_value.with_traceback(traceback) from exc_value

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute
  83.                 return self.cursor.execute(sql)

File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\sql_server\pyodbc\base.py" in execute
  546.             return self.cursor.execute(sql, params)

Exception Type: ProgrammingError at /app-url/class/
Exception Value: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@Param2'. (102) (SQLExecDirectW)")

What packages and versions are you using? There are several forks of `django-pyodbc`. What OS are you running Django on? — FlipperPA, Sep 07 '18 at 11:43
I tried doing a install 'django-pyodbc' and it returned - requirements already satisfied - ```pyodbc<4.1,>=3.0.6 from python37-32 packages - (from django-pyodbc) (4.0.23)```. I remember doing other installs, and checked for ```django-pyodbc-azure``` and it did this - Found existing installation: ```(from django-pyodbc-azure) (4.0.23) Django 2.0.2 Uninstalling Django-2.0.2: Successfully uninstalled Django-2.0.2``` — Loser Coder, Sep 07 '18 at 18:12
In my system variables, this is what I have - ```C:\Users\\AppData\Local\Programs\Python\Python37-32\Lib\site-packages\django_pyodbc-2.0.0a1.dist-info``` ```C:\Users\\AppData\Local\Programs\Python\Python37-32\Lib\site-packages\django_pyodbc_azure-2.1.0.0.dist-info```, ```C:\Users\\AppData\Local\Programs\Python\Python37-32\Lib\site-packages\django_pyodbc``` and ```C:\Users\\AppData\Local\Programs\Python\Python37-32\Lib\site-packages\sqlserver_ado``` — Loser Coder, Sep 07 '18 at 18:13
updated the question with more details. I still feel like this is a simple string formatting issue! — Loser Coder, Sep 10 '18 at 18:13

FlipperPA · Accepted Answer · 2018-09-10T18:56:09.933

1

I got some time to test this, and I believe you've found a bug in django-pyodbc-azure. I've found a work around, but it isn't the prettiest.

If you open up a separate connection with pyodbc with the same credentials, you can do this:

import pyodbc

con = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=yourserver.com;PORT=1433;DATABASE=yourdb;UID=yourdb_user;PWD=password;')
cursor = con.cursor()

cursor.execute('EXEC usp_get_user_data @user_name = ?, @user_type = ?', 'flipperpa', 'admin')
rows = cursor.fetchall()
for row in rows:
    print(row)

Good luck!

edited Sep 10 '18 at 18:56

answered Sep 10 '18 at 18:33

FlipperPA

13,607
4
39
71

thank you! I'll try the workaround. Is there an easy way to get the row[0], row[1] etc. to a different model (there are different columnlike names in the result set from the actual Model and the resulting columns don't map to any specific table) – Loser Coder Sep 10 '18 at 18:48
self.model is erroring out for me. This is a function within a model... ```resultSet = [] for row in results: p = self.models(id= row[0], column2=row[1], summary=row[2]) resultSet.append(p) return resultSet``` – Loser Coder Sep 10 '18 at 18:51
When using `pyodbc` instead of Django's connection, you can access the return values as `row.user_name` and `row.user_type`, for example. – FlipperPA Sep 10 '18 at 18:55
nice. Is there a way to convert the columns returned/row as something a Django view can process (aka Model, but one that's not mapped to a table or view) – Loser Coder Sep 10 '18 at 19:08
1

Hmmm, the example I gave runs with a stored procedure I have on SQL Server, but I can't really debug yours. Is your line, `cursor.execute('EXEC [dbo].[stored_proc] @Param1=? @Param2=? @NewParam3=?', strparam1, strparam2, strparam3)`? Also, if `@Param2` is an `INT` in T-SQL, then `strparam` must be an `int` in Python... not a string. – FlipperPA Sep 10 '18 at 19:35
1

Any of the results can be included in a view's context. For example, you could do `context['rows'] = cursor.fetchall()`, and then do `{% for row in rows %}` in the Django template. – FlipperPA Sep 10 '18 at 19:36

Kashif · Answer 2 · 2022-02-26T07:23:10.030

0

you can call store procedure with parameters this way

from django.db import connection
cursor = connection.cursor()
cursor.callproc('test_procedure', [first_type, second_type])

If you fetch data with list of dict then you can use this too for extra information

list_obj = [ dict( zip( [column[0] for column in cursor.description] , record ) ) for record in cursor.fetchall()]

edited Feb 26 '22 at 07:23

answered Feb 26 '22 at 07:09

Kashif

1,364
17
21

Django execute stored procedure - different data types, multiple parameters and different columns from model

2 Answers2

Linked