Ticket #5062: pymssql.py

File pymssql.py, 9.4 KB (added by mamcx <http://www.elmalabarista.com>, 18 years ago)

Complete pymmsql file

Line 
1"""DB-SIG compliant module for communicating with MS SQL servers"""
2#***************************************************************************
3# pymssql.py - description
4#
5# begin : 2003-03-03
6# copyright : (C) 2003-03-03 by Joon-cheol Park
7# email : jooncheol@gmail.com
8# current developer : Andrzej Kukula <akukula@gmail.com>
9# homepage : http://pymssql.sourceforge.net
10#
11#***************************************************************************
12# This library is free software; you can redistribute it and/or
13# modify it under the terms of the GNU Lesser General Public
14# License as published by the Free Software Foundation; either
15# version 2.1 of the License, or (at your option) any later version.
16#
17# This library is distributed in the hope that it will be useful,
18# but WITHOUT ANY WARRANTY; without even the implied warranty of
19# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
20# Lesser General Public License for more details.
21#
22# You should have received a copy of the GNU Lesser General Public
23# License along with this library; if not, write to the Free Software
24# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
25# MA 02110-1301 USA
26#***************************************************************************
27
28__author__ = "Joon-cheol Park <jooncheol@gmail.com>, Andrzej Kukula <akukula@gmail.com>"
29__version__ = '0.8.0'
30import _mssql
31import types
32import string
33import time
34import datetime
35
36### module constants
37
38# compliant with DB SIG 2.0
39apilevel = '2.0'
40
41# module may be shared, but not connections
42threadsafety = 1
43
44# this module use extended python format codes
45paramstyle = 'pyformat'
46
47#export column type names from _mssql
48class DBAPITypeObject:
49 def __init__(self,*values):
50 self.values = values
51 def __cmp__(self,other):
52 if other in self.values:
53 return 0
54 if other < self.values:
55 return 1
56 else:
57 return -1
58
59STRING = DBAPITypeObject(_mssql.STRING)
60BINARY = DBAPITypeObject(_mssql.BINARY)
61NUMBER = DBAPITypeObject(_mssql.NUMBER)
62DATETIME = DBAPITypeObject(_mssql.DATETIME)
63DECIMAL = DBAPITypeObject(_mssql.DECIMAL)
64
65### exception hierarchy
66
67class Warning(StandardError):
68 pass
69
70class Error(StandardError):
71 pass
72
73class InterfaceError(Error):
74 pass
75
76class DatabaseError(Error):
77 pass
78
79class DataError(DatabaseError):
80 pass
81
82class OperationalError(DatabaseError):
83 pass
84
85class IntegrityError(DatabaseError):
86 pass
87
88class InternalError(DatabaseError):
89 pass
90
91class ProgrammingError(DatabaseError):
92 pass
93
94class NotSupportedError(DatabaseError):
95 pass
96
97
98### cursor object
99
100class pymssqlCursor:
101
102 def __init__(self, src):
103 self.__source = src
104 self.description = None
105 self.rowcount = -1
106 self.arraysize = 1
107 self._result = []
108 self.__fetchpos = 0
109 self.__resultpos = 0
110
111 def close(self):
112 self.__source = None
113 self.description = None
114 self.result = []
115 self.rowcount = -1
116
117 def execute(self, operation, params = None):
118 # "The parameters may also be specified as list of
119 # tuples to e.g. insert multiple rows in a single
120 # operation, but this kind of usage is depreciated:
121 if params and type(params) == types.ListType and \
122 type(params[0]) == types.TupleType:
123 self.executemany(operation, params)
124 else:
125 # not a list of tuples
126 self.executemany(operation, (params,))
127
128 def executemany(self, operation, param_seq):
129 self.description = None
130 self.rowcount = -1
131 self.__fetchpos = 0
132 self.__resultpos = 0
133
134 # first try to execute all queries
135 totrows = 0
136
137 #Respect GO terminator
138 for sql in operation.split('\nGO'):
139 if sql=='':
140 continue
141 try:
142 for params in param_seq:
143 if params != None:
144 sql = _quoteparams(sql, params)
145
146 #print sql
147 ret = self.__source.query(sql)
148 if ret == 1:
149 self._result = self.__source.fetch_array()
150 totrows = totrows + self._result[self.__resultpos][1]
151 else:
152 self._result = None
153 raise DatabaseError, "error: %s" % self.__source.errmsg()
154 except Exception,e:
155 if self.__source.errmsg() == None:
156 raise e
157 else:
158 raise DatabaseError, "internal error: %s" % self.__source.errmsg()
159
160 # then initialize result raw count and description
161 if len(self._result[self.__resultpos][0]) > 0:
162 self.description = map(lambda (colname,coltype): (colname, coltype, None, None, None, None, None),self._result[self.__resultpos][0])
163 self.rowcount = totrows
164 else:
165 self.description = None
166 self.rowcount = self._result[self.__resultpos][1]
167
168 def nextset(self):
169 if self._result ==None:
170 return 0
171
172 resultlen =len(self._result)
173 if resultlen>1 and self.__resultpos+1<resultlen:
174 self.__resultpos = self.__resultpos + 1
175 return 1
176 else:
177 return 0
178
179 def fetchone(self):
180 ret = self.fetchmany(1)
181 if ret: return ret[0]
182 else: return None
183
184 def fetchall(self):
185 return self._result[self.__resultpos][2][self.__fetchpos:]
186
187 def fetchmany(self, size = None, keep = 1):
188 if size == None:
189 size = self.arraysize
190 if keep == 1:
191 self.arraysize = size
192 res = self._result
193 if res[self.__resultpos][1]==self.__fetchpos:
194 return []
195 reslen = len(res[self.__resultpos][2][self.__fetchpos:])
196 if reslen < size:
197 size = res[self.__resultpos][1]
198 ret = res[self.__resultpos][2][self.__fetchpos:self.__fetchpos+size]
199 self.__fetchpos = self.__fetchpos + size
200 return ret
201
202 def setinputsizes(self, sizes):
203 pass
204
205 def setoutputsize(self, size, col = 0):
206 pass
207
208def _quote(x):
209 if type(x) == types.StringType or type(x) == types.UnicodeType:
210 x = "'" + string.replace(str(x), "'", "''") + "'"
211 elif type(x) in (types.IntType, types.LongType, types.FloatType):
212 pass
213 elif x is None:
214 x = 'NULL'
215 # datetime quoting (thanks Jan Finell <jfinell@regionline.fi>)
216 # described under "Writing International Transact-SQL Statements" in BOL
217 # beware the order: isinstance(x,datetime.date)=True if x is
218 # datetime.datetime ! Also round x.microsecond to milliseconds,
219 # otherwise we get Msg 241, Level 16, State 1: Syntax error
220 elif isinstance(x, datetime.datetime):
221 x = "{ts '%04d-%02d-%02d %02d:%02d:%02d.%s'}" % \
222 (x.year,x.month, x.day,
223 x.hour, x.minute, x.second, x.microsecond / 1000)
224 elif isinstance(x, datetime.date):
225 x = "{d '%04d-%02d-%02d'}" % (x.year, x.month, x.day)
226 # alternative quoting by Luciano Pacheco <lucmult@gmail.com>
227 #elif hasattr(x, 'timetuple'):
228 # x = time.strftime('\'%Y%m%d %H:%M:%S\'', x.timetuple())
229 elif type(x) == types.BooleanType:
230 x = x and 1 or 0
231 else:
232 #print "didn't like " + x + " " + str(type(x))
233 raise InterfaceError, 'do not know how to handle type %s' % type(x)
234
235 return x
236
237def _quoteparams(s, params):
238 if hasattr(params, 'has_key'):
239 x = {}
240 for k, v in params.items():
241 x[k] = _quote(v)
242 params = x
243 else:
244 params = tuple(map(_quote, params))
245 return s % params
246
247
248
249### connection object
250
251class pymssqlCnx:
252
253 def __init__(self, cnx):
254 self.__cnx = cnx
255 self.__autocommit = False
256 try:
257 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
258 self.__cnx.fetch_array()
259 except:
260 raise OperationalError, "invalid connection."
261
262 def close(self):
263 if self.__cnx == None:
264 raise OperationalError, "invalid connection."
265 self.__cnx.close()
266 self.__cnx = None
267
268 def commit(self):
269 if self.__cnx == None:
270 raise OperationalError, "invalid connection."
271
272 if self.__autocommit == True:
273 return
274
275 try:
276 self.__cnx.query("IF @@TRANCOUNT>0 commit tran")
277 self.__cnx.fetch_array()
278 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
279 self.__cnx.fetch_array()
280 except:
281 raise OperationalError, "can't commit."
282
283 def rollback(self):
284 if self.__cnx == None:
285 raise OperationalError, "invalid connection."
286
287 if self.__autocommit == True:
288 return
289
290 try:
291 self.__cnx.query("IF @@TRANCOUNT>0 rollback tran")
292 self.__cnx.fetch_array()
293 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
294 self.__cnx.fetch_array()
295 except:
296 raise OperationalError, "can't rollback."
297
298 def autocommit(self,status):
299 if status:
300 if self.__autocommit == False:
301 self.__cnx.query("IF @@TRANCOUNT>0 rollback tran")
302 self.__cnx.fetch_array()
303 self.__autocommit = True
304 else:
305 if self.__autocommit == True:
306 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
307 self.__cnx.fetch_array()
308 self.__autocommit = False
309
310 def cursor(self):
311 if self.__cnx == None:
312 raise OperationalError, "invalid connection."
313 try:
314 return pymssqlCursor(self.__cnx)
315 except:
316 raise OperationalError, "invalid connection."
317
318
319
320# connects to a database
321def connect(dsn = None, user = "sa", password = "", host = ".", database = "master"):
322 # first get params from DSN
323 dbhost = ""
324 dbbase = ""
325 dbuser = ""
326 dbpasswd = ""
327 dbopt = ""
328 dbtty = ""
329 try:
330 params = string.split(dsn, ":")
331 dbhost = params[0]
332 dbbase = params[1]
333 dbuser = params[2]
334 dbpasswd = params[3]
335 dbopt = params[4]
336 dbtty = params[5]
337 except:
338 pass
339
340 # override if necessary
341 if user != "":
342 dbuser = user
343 if password != "":
344 dbpasswd = password
345 if database != "":
346 dbbase = database
347 if host != "":
348 dbhost = host
349
350 # empty host is localhost
351 if dbhost == "":
352 dbhost = "."
353 if dbuser == "":
354 dbuser = "sa"
355
356 # open the connection
357 con = _mssql.connect(dbhost, dbuser, dbpasswd)
358 con.select_db(dbbase)
359 return pymssqlCnx(con)
360
Back to Top