Ticket #5062: pymssql.py

File pymssql.py, 9.4 kB (added by mamcx <http://www.elmalabarista.com>, 11 months 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'
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
98	### cursor object
99
100	class 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
208	def _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
237	def _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
251	class 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
321	def 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)

Download in other formats:

Original Format

Code

Ticket #5062: pymssql.py

Download in other formats: