pyodbc Connects MSSQL to Execute SQL Statements

Keywords: Python SQL Stored Procedure Database Django

Use django to connect to SQL Server, and pyodbc to connect to multiple databases.

pyodbc, the SQL that runs the query, is very simple:

import pyodbc

connection = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=;DATABASE=DB_name;UID=User_Name;PWD=PassWord')
curs = connection.execute('select * from table')

Basic usage of pyodbc:

  1. First import: from pyodbc import connect;

  2. Generate connection instances: Conn = connect(DBCONNECTSTR);

  3. Generate cursor: cur = Conn.cursor();

  4. Execute cursor commands: cur.execute(), cur.commit(), cur.rollback(), etc.

  5. Close the cursor: cur.close();

  6. Close the connection instance: Conn.close().

The above six steps are necessary.

If it's just a query, you can run the excute command directly by using the instance, and you don't need to create a cursor, then steps 3, 4 and 5 can be omitted.

There are a lot of tutorials on the internet, so I won't say much about them. (DRY=Donot Repeat Yourself)

But many articles on the Internet are misleading, such as Python Connecting to the Database - pyodbc

It says that the callproc() method is used to run stored procedures.

Provide a list of methods for connection and cursor:

>>> dir(Conn)

['__class__', '__delattr__', '__doc__', '__enter__', '__exit__', '__format__', '__getattribute__', '__hash__', '__init__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'add_output_converter', 'autocommit', 'clear_output_converters', 'close', 'commit', 'cursor', 'execute', 'getinfo',  'rollback', 'searchescape', 'timeout']

>>> dir(cur)

['__class__', '__delattr__', '__doc__', '__enter__', '__exit__', '__format__', '__getattribute__', '__hash__', '__init__', '__iter__', '__new__', '__reduce__','__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'arraysize', 'close', 'columns', 'commit', 'connection',  'description', 'execute',  'executemany', 'fetchall', 'fetchmany', 'fetchone', 'foreignKeys', 'getTypeInfo', 'next', 'nextset', 'noscan', 'primaryKeys', 'procedureColumns', 'procedures', 'rollback', 'rowIdColumns', 'rowVerColumns',  'rowcount', 'setinputsizes', 'setoutputsize', 'skip', 'statistics', 'tables']

Execute the stored procedure or use the execute() method, and then attach EXEC as an SQL command to execute.

    #If no data is returned, the stored procedure execution method
    cur.execute('EXEC P_PROCDUREName param1,param2,%d,%s' %(p1_int,p2_str))
    print cur.rowcount  #Number of rows that can be affected by stored procedures
    #If you have a return data set, you need to add set nocount, or you can add it in the stored procedure.
    rows = cur.execute('SET NOCOUNT ON; EXEC P_PROCDUREName param1,param2,%d,%s' %(p1_int,p2_str)).fetchall()
    print cur.rowcount,rows   #Smart as you already know, there must be no rows (- 1). The rows in the back are a list. See how your data looks.
    #Either way, you can commit at the end (in fact, mode 2 already includes commit by default)
except Exception as e:
    print e

Note: If you need to get the returned data (that is, the selected content in the stored procedure), you need to add SET NOCOUNT ON; parameters, otherwise errors may occur: No Results,Previous SQL was not a query

Posted by temidayo on Wed, 09 Oct 2019 08:05:18 -0700