python 3.x support. about pymssql

[GoogleCodeExporter]

I haven't actually tested it yet but the rewrite in Cython (1.9.xxx) should 
support Python 3 as that was one of the reasons for the rewrite.

Original comment by [email protected] on 4 Jan 2011 at 8:53

[GoogleCodeExporter]

Original comment by [email protected] on 8 Mar 2011 at 3:32

• Added labels: PyMssql-2.x

[GoogleCodeExporter]

It doesn't support python 3 as is at the current time.  Here's what needs to be 
addressed:

1. Use of StandardError has to be changed to Exception
2. Use of BaseString needs to be changed to (str, unicode)
3. Extra checking for Python 3 and converting the strings from unicode to bytes 
has to be done.
4. An extra step, running 2to3 on py files should be added to setup.py.  The 
changes it makes are all very straight forward.

The question I have is for #3 -- what should the default encoding be to convert 
unicode to bytes?

Original comment by [email protected] on 9 Jan 2012 at 1:03

[GoogleCodeExporter]

The answer to #3 is here: 
https://groups.google.com/forum/?fromgroups#!topic/pymssql/QlDvV9rl4Ow

Original comment by [email protected] on 8 Mar 2012 at 3:10

• Changed state: Accepted

[GoogleCodeExporter]

Here are some changes that I made that got at least basic queries through 
sqlalchemy working for me in Python 3.3:

https://gist.github.com/4436951

These are essentially the same as what David outline in steps 1-3 of comment 3. 
I didn't add the `use_2to3=True` to setup.py as I didn't need it to get basic 
queries going (maybe this is needed for the tests, which I haven't tried yet as 
I don't have a SQL Server available that I can safely test against).

I just noticed that I chose 'ascii' as the encoding for all of my encodes in 
the Cython code. Maybe it should be 'utf-8'? I'm a little unclear whether it's 
possible for the user name and password to be passed in as actual Unicode and 
whether FreeTDS will know how to deal with UTF-8. 'ascii' seems to be a good 
enough choice to get the common cases working, but I could be convinced to 
change it.

Original comment by [email protected] on 3 Jan 2013 at 1:55

[GoogleCodeExporter]

Issue 65 has been merged into this issue.

Original comment by [email protected] on 7 Jan 2013 at 5:28

[GoogleCodeExporter]

Original comment by [email protected] on 13 Mar 2013 at 2:59

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

OK, I had a bunch of time yesterday at the PyCon US 2013 sprints to work on 
this some more and I'm happy to report that I now have a "python3" branch and I 
have the tests passing on that branch for Python 2.6, 2.7, and 3.3!

marca@marca-mac:/tmp/pymssql$ hg branch
python3
marca@marca-mac:/tmp/pymssql$ hg tip
changeset: 300:840a8099e26c
branch: python3
tag: tip
user: Marc Abramowitz [email protected]
date: Mon Mar 18 22:22:30 2013 -0700
files: pymssql.pyx
description:
fetchall: Tweaks for Python 3 compatibility

marca@marca-mac:/tmp/pymssql$ tox
…
py26: commands succeeded
py27: commands succeeded
py33: commands succeeded
congratulations :)

I'd like to thank my employer, SurveyMonkey, for supporting me sprinting.

So it would be great if folks could pull this down from Mercurial and kick the 
tires in Python 3 and Python 2 as well (to make sure nothing was broken by 
these changes) and report on their findings.

Original comment by [email protected] on 19 Mar 2013 at 5:06

[GoogleCodeExporter]

Here's a stack using the python3 branch as of:
changeset:   312:b0372991d14a
branch:      python3
tag:         tip
user:        Marc Abramowitz <[email protected]>
date:        Tue Jun 11 23:15:20 2013 -0700
summary:     Replace exception indexing e[0] with e.args[0]
that I'm hitting:
Traceback (most recent call last):
  File "_mssql.pyx", line 169, in _mssql.ensure_bytes (_mssql.c:2269)
    decoded = s.decode(encoding)
AttributeError: 'str' object has no attribute 'decode'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "pymssql.pyx", line 204, in pymssql.Connection.__init__ (pymssql.c:2607)
    self._conn.execute_non_query('BEGIN TRAN')
  File "_mssql.pyx", line 781, in _mssql.MSSQLConnection.execute_non_query (_mssql.c:8420)
    cpdef execute_non_query(self, query_string, params=None):
  File "_mssql.pyx", line 802, in _mssql.MSSQLConnection.execute_non_query (_mssql.c:8250)
    self.format_and_run_query(query_string, params)
  File "_mssql.pyx", line 982, in _mssql.MSSQLConnection.format_and_run_query (_mssql.c:9712)
    query_string_bytes = ensure_bytes(query_string, self.charset)
  File "_mssql.pyx", line 172, in _mssql.ensure_bytes (_mssql.c:2352)
    return s.encode(encoding)
TypeError: encode() argument 1 must be str, not None

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./plot_fid.py", line 39, in <module>
    main()
  File "./plot_fid.py", line 19, in main
    database='DM_INFO', as_dict=True)
  File "pymssql.pyx", line 574, in pymssql.connect (pymssql.c:8634)
    return Connection(conn, as_dict)
  File "pymssql.pyx", line 206, in pymssql.Connection.__init__ (pymssql.c:2673)
    raise OperationalError('Cannot start transaction: ' + str(e.args[0]))
pymssql.OperationalError: Cannot start transaction: encode() argument 1 must be 
str, not None

Is Cython 0.19.1 required for python3 support? I only have 0.17.2 currently 
installed on my system.

Original comment by [email protected] on 13 Jun 2013 at 8:25

[GoogleCodeExporter]

@aeroevan,

Thank you so much for testing this branch!

I had the tests passing with:

$ .tox/py33/bin/pip freeze | grep -i cython
Cython==0.18

so Cython 0.19.1 shouldn't be required. I suspect that your version of Cython 
is fine (it would be great if you could try again after upgrading Cython but I 
doubt that will help the problem).

More likely is that you are doing something in plot_fid.py that is exercising 
pymssql in a way that the current pymssql tests are not covering.

Are you able to share the code of plot_fid.py? If not, maybe you can isolate it 
to one query/one table and produce a simplified case?

Original comment by [email protected] on 15 Jul 2013 at 9:57

[GoogleCodeExporter]

The code was a quick hack, so it should be short enough.

http://sprunge.us/ifhP?python

The issue might be related to having to get results from an info database to 
determine which table to use (some sort of sharding I guess), but I'm not 
really sure. The same code (s/input/raw_input/) works on python 2.

It also looks like I'm connecting to a SQL Server 2005 host if that helps.

Original comment by [email protected] on 15 Jul 2013 at 10:19

[GoogleCodeExporter]

aeroevan,

Looking briefly at the code, another possibility is that this is happening 
because you did not specify a charset when connecting to the database. In fact, 
I reproduced what looks like the same error by writing a little script to 
connect to the database and omitting the charset parameter. Try adding this to 
your connect call:

charset='UTF-8'

Original comment by [email protected] on 15 Jul 2013 at 10:28

[GoogleCodeExporter]

A complete example with `charset='UTF-8'`:

import pymssql
conn = pymssql.connect(host='localhost', user='(user)', password='(password)',
charset='UTF-8')
cur = conn.cursor(as_dict=True)
cur.execute('SELECT name FROM sys.sysdatabases')
for row in cur.fetchall():
print(row['name'])

Original comment by [email protected] on 15 Jul 2013 at 10:31

[GoogleCodeExporter]

I just committed this change to the "python3" branch:

https://code.google.com/p/pymssql/source/detail?r=0fff3f95e7049ac204a26bda58a36c
9fe40fee1d&name=python3

This will make the default charset "UTF-8" (hopefully a reasonable default) so 
that users will not have to add "charset='UTF-8'" to make their code work with 
this branch.

aeroevan, can you give that a spin?

Original comment by [email protected] on 15 Jul 2013 at 11:49

[GoogleCodeExporter]

Sorry for getting back so late, but that does fix the issue I was hitting in my 
simple case.

Thanks.

Original comment by [email protected] on 17 Jul 2013 at 8:54

[GoogleCodeExporter]

aeroevan,

Awesome. Thanks for reporting back!

Feel free to post again if you have successes or failures using pymssql on 
Python 3. You are in brave new territory! :-)

Original comment by [email protected] on 18 Jul 2013 at 6:44

[GoogleCodeExporter]

Original comment by [email protected] on 7 Aug 2013 at 1:54

• Added labels: needstesting

[GoogleCodeExporter]

Original comment by [email protected] on 7 Aug 2013 at 3:01

• Added labels: python3

[GoogleCodeExporter]

I merged the "python3" branch into the "master" branch on GitHub and tox is 
passing for me on python 2.6, 2.7, and 3.3!

It would be great if other folks can try the "master" branch on GitHub:

https://github.com/pymssql/pymssql

Original comment by [email protected] on 17 Sep 2013 at 2:36

• Changed state: Fixed
• Added labels: PriorityI-Medium
• Removed labels: Priority-Medium