Ticket #5062: pymssql.3.py

File pymssql.3.py, 9.9 KB (added by gregoire@…, 17 years ago)

pymssql.py with conversion to latin-1 as unicode error workaround and fixes indentation error

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
97def unicode_string(s, encoding='ascii'):
98 if type(s) == types.StringType:
99 return unicode(s, encoding)
100 else:
101 return s
102
103def unicode_tuple_encode(t, encoding='ascii'):
104 return tuple([unicode_string(i, encoding) for i in t])
105
106### cursor object
107
108class pymssqlCursor:
109
110 def __init__(self, src):
111 self.__source = src
112 self.description = None
113 self.rowcount = -1
114 self.arraysize = 1
115 self._result = []
116 self.__fetchpos = 0
117 self.__resultpos = 0
118
119 def close(self):
120 self.__source = None
121 self.description = None
122 self.result = []
123 self.rowcount = -1
124
125 def execute(self, operation, params = None):
126 # "The parameters may also be specified as list of
127 # tuples to e.g. insert multiple rows in a single
128 # operation, but this kind of usage is depreciated:
129 if params and type(params) == types.ListType and \
130 type(params[0]) == types.TupleType:
131 self.executemany(operation, params)
132 else:
133 # not a list of tuples
134 self.executemany(operation, (params,))
135
136 def executemany(self, operation, param_seq):
137 self.description = None
138 self.rowcount = -1
139 self.__fetchpos = 0
140 self.__resultpos = 0
141
142 # first try to execute all queries
143 totrows = 0
144
145 #Respect GO terminator
146 for sql in operation.split('\nGO'):
147 if sql=='':
148 continue
149 try:
150 for params in param_seq:
151 if params != None:
152 sql = _quoteparams(sql, params)
153
154 #print sql
155 ret = self.__source.query(sql)
156 if ret == 1:
157 self._result = self.__source.fetch_array()
158 for res in range(len(self._result[self.__resultpos][2])):
159 self._result[self.__resultpos][2][res] = unicode_tuple_encode(self._result[self.__resultpos][2][res], 'latin-1')
160 totrows = totrows + self._result[self.__resultpos][1]
161 else:
162 self._result = None
163 raise DatabaseError, "error: %s" % self.__source.errmsg()
164 except Exception,e:
165 if self.__source.errmsg() == None:
166 raise e
167 else:
168 raise DatabaseError, "internal error: %s" % self.__source.errmsg()
169
170 # then initialize result raw count and description
171 if len(self._result[self.__resultpos][0]) > 0:
172 self.description = map(lambda (colname,coltype): (colname, coltype, None, None, None, None, None),self._result[self.__resultpos][0])
173 self.rowcount = totrows
174 else:
175 self.description = None
176 self.rowcount = self._result[self.__resultpos][1]
177
178 def nextset(self):
179 if self._result ==None:
180 return 0
181
182 resultlen =len(self._result)
183 if resultlen>1 and self.__resultpos+1<resultlen:
184 self.__resultpos = self.__resultpos + 1
185 return 1
186 else:
187 return 0
188
189 def fetchone(self):
190 ret = self.fetchmany(1)
191 if ret: return ret[0]
192 else: return None
193
194 def fetchall(self):
195 return self._result[self.__resultpos][2][self.__fetchpos:]
196
197 def fetchmany(self, size = None, keep = 1):
198 if size == None:
199 size = self.arraysize
200 if keep == 1:
201 self.arraysize = size
202 res = self._result
203 if res[self.__resultpos][1]==self.__fetchpos:
204 return []
205 reslen = len(res[self.__resultpos][2][self.__fetchpos:])
206 if reslen < size:
207 size = res[self.__resultpos][1]
208 ret = res[self.__resultpos][2][self.__fetchpos:self.__fetchpos+size]
209 self.__fetchpos = self.__fetchpos + size
210 return ret
211
212 def setinputsizes(self, sizes):
213 pass
214
215 def setoutputsize(self, size, col = 0):
216 pass
217
218def _quote(x):
219 if type(x) == types.StringType:
220 x = "'" + string.replace(str(x), "'", "''") + "'"
221 elif type(x) == types.UnicodeType:
222 x = "'" + string.replace(str(x.encode('latin-1')), "'", "''") + "'"
223 elif type(x) in (types.IntType, types.LongType, types.FloatType):
224 pass
225 elif x is None:
226 x = 'NULL'
227 # datetime quoting (thanks Jan Finell <jfinell@regionline.fi>)
228 # described under "Writing International Transact-SQL Statements" in BOL
229 # beware the order: isinstance(x,datetime.date)=True if x is
230 # datetime.datetime ! Also round x.microsecond to milliseconds,
231 # otherwise we get Msg 241, Level 16, State 1: Syntax error
232 elif isinstance(x, datetime.datetime):
233 x = "{ts '%04d-%02d-%02d %02d:%02d:%02d.%s'}" % \
234 (x.year,x.month, x.day,
235 x.hour, x.minute, x.second, x.microsecond / 1000)
236 elif isinstance(x, datetime.date):
237 x = "{d '%04d-%02d-%02d'}" % (x.year, x.month, x.day)
238 # alternative quoting by Luciano Pacheco <lucmult@gmail.com>
239 #elif hasattr(x, 'timetuple'):
240 # x = time.strftime('\'%Y%m%d %H:%M:%S\'', x.timetuple())
241 elif type(x) == types.BooleanType:
242 x = x and 1 or 0
243 else:
244 #print "didn't like " + x + " " + str(type(x))
245 raise InterfaceError, 'do not know how to handle type %s' % type(x)
246
247 return x
248
249def _quoteparams(s, params):
250 if hasattr(params, 'has_key'):
251 x = {}
252 for k, v in params.items():
253 x[k] = _quote(v)
254 params = x
255 else:
256 params = tuple(map(_quote, params))
257 return s % params
258
259
260
261### connection object
262
263class pymssqlCnx:
264
265 def __init__(self, cnx):
266 self.__cnx = cnx
267 self.__autocommit = False
268 try:
269 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
270 self.__cnx.fetch_array()
271 except:
272 raise OperationalError, "invalid connection."
273
274 def close(self):
275 if self.__cnx == None:
276 raise OperationalError, "invalid connection."
277 self.__cnx.close()
278 self.__cnx = None
279
280 def commit(self):
281 if self.__cnx == None:
282 raise OperationalError, "invalid connection."
283
284 if self.__autocommit == True:
285 return
286
287 try:
288 self.__cnx.query("IF @@TRANCOUNT>0 commit tran")
289 self.__cnx.fetch_array()
290 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
291 self.__cnx.fetch_array()
292 except:
293 raise OperationalError, "can't commit."
294
295 def rollback(self):
296 if self.__cnx == None:
297 raise OperationalError, "invalid connection."
298
299 if self.__autocommit == True:
300 return
301
302 try:
303 self.__cnx.query("IF @@TRANCOUNT>0 rollback tran")
304 self.__cnx.fetch_array()
305 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
306 self.__cnx.fetch_array()
307 except:
308 raise OperationalError, "can't rollback."
309
310 def autocommit(self,status):
311 if status:
312 if self.__autocommit == False:
313 self.__cnx.query("IF @@TRANCOUNT>0 rollback tran")
314 self.__cnx.fetch_array()
315 self.__autocommit = True
316 else:
317 if self.__autocommit == True:
318 self.__cnx.query("IF @@TRANCOUNT>0 begin tran")
319 self.__cnx.fetch_array()
320 self.__autocommit = False
321
322 def cursor(self):
323 if self.__cnx == None:
324 raise OperationalError, "invalid connection."
325 try:
326 return pymssqlCursor(self.__cnx)
327 except:
328 raise OperationalError, "invalid connection."
329
330
331
332# connects to a database
333def connect(dsn = None, user = "sa", password = "", host = ".", database = "master"):
334 # first get params from DSN
335 dbhost = ""
336 dbbase = ""
337 dbuser = ""
338 dbpasswd = ""
339 dbopt = ""
340 dbtty = ""
341 try:
342 params = string.split(dsn, ":")
343 dbhost = params[0]
344 dbbase = params[1]
345 dbuser = params[2]
346 dbpasswd = params[3]
347 dbopt = params[4]
348 dbtty = params[5]
349 except:
350 pass
351
352 # override if necessary
353 if user != "":
354 dbuser = user
355 if password != "":
356 dbpasswd = password
357 if database != "":
358 dbbase = database
359 if host != "":
360 dbhost = host
361
362 # empty host is localhost
363 if dbhost == "":
364 dbhost = "."
365 if dbuser == "":
366 dbuser = "sa"
367
368 # open the connection
369 con = _mssql.connect(dbhost, dbuser, dbpasswd)
370 con.select_db(dbbase)
371 return pymssqlCnx(con)
372
Back to Top