Ticket #5062: pymssql.2.py

File pymssql.2.py, 9.9 kB (added by gregoire@audacy.fr, 11 months ago)
pymssql.py with conversion to latin-1 as unicode error workaround

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'
30	import _mssql
31	import types
32	import string
33	import time
34	import datetime
35
36	### module constants
37
38	# compliant with DB SIG 2.0
39	apilevel = '2.0'
40
41	# module may be shared, but not connections
42	threadsafety = 1
43
44	# this module use extended python format codes
45	paramstyle = 'pyformat'
46
47	#export column type names from _mssql
48	class 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
59	STRING = DBAPITypeObject(_mssql.STRING)
60	BINARY = DBAPITypeObject(_mssql.BINARY)
61	NUMBER = DBAPITypeObject(_mssql.NUMBER)
62	DATETIME = DBAPITypeObject(_mssql.DATETIME)
63	DECIMAL = DBAPITypeObject(_mssql.DECIMAL)
64
65	### exception hierarchy
66
67	class Warning(StandardError):
68	pass
69
70	class Error(StandardError):
71	pass
72
73	class InterfaceError(Error):
74	pass
75
76	class DatabaseError(Error):
77	pass
78
79	class DataError(DatabaseError):
80	pass
81
82	class OperationalError(DatabaseError):
83	pass
84
85	class IntegrityError(DatabaseError):
86	pass
87
88	class InternalError(DatabaseError):
89	pass
90
91	class ProgrammingError(DatabaseError):
92	pass
93
94	class NotSupportedError(DatabaseError):
95	pass
96
97	def unicode_string(s, encoding='ascii'):
98	if type(s) == types.StringType:
99	return unicode(s, encoding)
100	else:
101	return s
102
103	def unicode_tuple_encode(t, encoding='ascii'):
104	return tuple([unicode_string(i, encoding) for i in t])
105
106	### cursor object
107
108	class 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
218	def _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
249	def _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
263	class 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
333	def 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)

Download in other formats:

Original Format

Code

Ticket #5062: pymssql.2.py

Download in other formats: