1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
===================
``_mssql`` examples
===================
Example scripts using ``_mssql`` module.
Quickstart usage of various features
====================================
::
from pymssql import _mssql
conn = _mssql.connect(server='SQL01', user='user', password='password', \
database='mydatabase')
conn.execute_non_query('CREATE TABLE persons(id INT, name VARCHAR(100))')
conn.execute_non_query("INSERT INTO persons VALUES(1, 'John Doe')")
conn.execute_non_query("INSERT INTO persons VALUES(2, 'Jane Doe')")
::
# how to fetch rows from a table
conn.execute_query('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in conn:
print "ID=%d, Name=%s" % (row['id'], row['name'])
.. versionadded:: 2.1.0
Iterating over query results by iterating over the connection object
just like it's already possible with ``pymssql`` connections is new in 2.1.0.
::
# examples of other query functions
numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees")
numemployees = conn.execute_scalar("SELECT COUNT(*) FROM employees WHERE name LIKE 'J%'") # note that '%' is not a special character here
employeedata = conn.execute_row("SELECT * FROM employees WHERE id=%d", 13)
::
# how to fetch rows from a stored procedure
conn.execute_query('sp_spaceused') # sp_spaceused without arguments returns 2 result sets
res1 = [ row for row in conn ] # 1st result
res2 = [ row for row in conn ] # 2nd result
::
# how to get an output parameter from a stored procedure
sqlcmd = """
DECLARE @res INT
EXEC usp_mystoredproc @res OUT
SELECT @res
"""
res = conn.execute_scalar(sqlcmd)
::
# how to get more output parameters from a stored procedure
sqlcmd = """
DECLARE @res1 INT, @res2 TEXT, @res3 DATETIME
EXEC usp_getEmpData %d, %s, @res1 OUT, @res2 OUT, @res3 OUT
SELECT @res1, @res2, @res3
"""
res = conn.execute_row(sqlcmd, (13, 'John Doe'))
::
# examples of queries with parameters
conn.execute_query('SELECT * FROM empl WHERE id=%d', 13)
conn.execute_query('SELECT * FROM empl WHERE name=%s', 'John Doe')
conn.execute_query('SELECT * FROM empl WHERE id IN %s', ((5, 6),))
conn.execute_query('SELECT * FROM empl WHERE name LIKE %s', 'J%')
conn.execute_query('SELECT * FROM empl WHERE name=%(name)s AND city=%(city)s', \
{ 'name': 'John Doe', 'city': 'Nowhere' } )
conn.execute_query('SELECT * FROM cust WHERE salesrep=%s AND id IN %s', \
('John Doe', (1, 2, 3)))
conn.execute_query('SELECT * FROM empl WHERE id IN %s', (tuple(xrange(4)),))
conn.execute_query('SELECT * FROM empl WHERE id IN %s', \
(tuple([3, 5, 7, 11]),))
::
conn.close()
Please note the usage of iterators and ability to access results by column
name. Also please note that parameters to connect method have different names
than in ``pymssql`` module.
An example of exception handling
================================
.. code-block:: python
from pymssql import _mssql
conn = _mssql.connect(server='SQL01', user='user', password='password',
database='mydatabase')
try:
conn.execute_non_query('CREATE TABLE t1(id INT, name VARCHAR(50))')
except _mssql.MssqlDatabaseException as e:
if e.number == 2714 and e.severity == 16:
# table already existed, so quieten the error
else:
raise # re-raise real error
finally:
conn.close()
Custom message handlers
=======================
.. versionadded:: 2.1.1
You can provide your own message handler callback function that will be invoked
by the stack with informative messages sent by the server. Set it on a per
``_mssql`` :class:`connection <_mssql.MSSQLConnection>` basis by using the
:meth:`_mssql.MSSQLConnection.set_msghandler` method:
.. code-block:: python
from pymssql import _mssql
def my_msg_handler(msgstate, severity, srvname, procname, line, msgtext):
"""
Our custom handler -- It simply prints a string to stdout assembled from
the pieces of information sent by the server.
"""
print("my_msg_handler: msgstate = %d, severity = %d, procname = '%s', "
"line = %d, msgtext = '%s'" % (msgstate, severity, procname,
line, msgtext))
cnx = _mssql.connect(server='SQL01', user='user', password='password')
try:
cnx.set_msghandler(my_msg_handler) # Install our custom handler
cnx.execute_non_query("USE mydatabase") # It gets called at this point
finally:
cnx.close()
Something similar to this would be printed to the standard output::
my_msg_handler: msgstate = x, severity = y, procname = '', line = 1, msgtext = 'Changed database context to 'mydatabase'.'
.. todo:: Add an example of invoking a Stored Procedure using ``_mssql``.
|
