pytds support for mssql?

[Derek]

Back in November, niphlod (?sp) said that he'd look into adding pytds support to DAL. I didn't have a chance to look a lot at pytds until recently, I don't see that it's supported yet. I believe the only thing missing from pytds to be able to add support is the DSN connection syntax. If anyone is interested, I could do a pull request and see if we can get that incorporated into pytds. I just reported a few issues with it and those were fixed (related to transactions on sql 2000), so I think it's mature enough to add. If anyone wants to add it to the DAL, I can offer my support as far as testing and workarounds for some issues it may have (its DBAPI 2.0 support is a little wonky).

Why PyTDS? You get MS SQL Server support with 0 binaries required on the host, it's 100% pure python (well, you'd benefit from bitarray, but it's not required). It doesn't use ODBC or anything, just straight TCP. That also means that you could run this with greenlets without reverting to seralized operations. The current pyodbc and pypyodbc still use the platform's odbc drivers which are written in C.

[Niphlod]

+1. It's been sitting on my todolist (and I submitted a patch to make it work without requiring an install) but until DSN/SSPI are kinda "transparent" I didn't want to introduce it in DAL's core. There were also some problems on properly closing connections, but I didn't dig deeper in it. 

[Tim Richardson]

+1 this, sounds great.

[Derek]

I've been working on adding it to the DAL but I ran into some issues with it... the main thing is it doesn't support DSN syntax, but I got past that by modifying the DAL.py file myself (just to see what it would look like once I got past that point). 

Now I am getting an error (which I reported)

File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 3452, in connector return self.driver.connect(server=host,database=db,user=user,password=password,port=1433, tds_version=0x71000000) File "C:\Python27\lib\site-packages\pytds\dbapi.py", line 259, in __init__ self._open() File "C:\Python27\lib\site-packages\pytds\dbapi.py", line 171, in _open raise err AttributeError: '_TdsSession' object has no attribute 'login'

I'm confident that once this issue gets fixed, everything should be good for the DSN implementation.

On that note, I have to question why the DAL insists on passing a DSN. It's merely a footnote in the DBAPI 2.0 specs, and it doesn't seem to be actually required to be compliant.

On Thursday, March 20, 2014 3:33:39 PM UTC-7, Tim Richardson wrote:

+1 this, sounds great.

[Niphlod]

I think it's because there are other adapters that don't override the connect method, and the DSN was the only "universal" working with all of them.

[Niphlod]

BTW: I don't see any dbapi.py in the main repo..... are you on the latest version ?

[Derek]

my bad, wrong error pasted. Here's the same error on the latest version...

Traceback (most recent call last):
  File "C:\Temp\web2py_src\web2py\gluon\restricted.py", line 220, in restricted
    exec ccode in environment
  File "C:/Temp/web2py_src/web2py/applications/test/models/db.py", line 15, in <module>
    db = DAL('mssql://web2py:aaaaaaaaa@asdflasdf/tgtest',pool_size=1)
  File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 7868, in __init__
    raise RuntimeError("Failure to connect, tried %d times:\n%s" % (attempts, tb))
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
  File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 7846, in __init__
    self._adapter = ADAPTERS[self._dbname](**kwargs)
  File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 688, in __call__
    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)
  File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 3455, in __init__
    if do_connect: self.reconnect()
  File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 669, in reconnect
    self.connection = f()
  

File "C:\Temp\web2py_src\web2py\gluon\dal.py", line 3452, in connector

    return self.driver.connect(server=host,database=db,user=user,password=password,port=1433, tds_version=0x70000000,autocommit=False)
  File "C:\Python27\lib\site-packages\pytds\__init__.py", line 1084, in connect
    conn._open()
  File "C:\Python27\lib\site-packages\pytds\__init__.py", line 313, in _open
    raise last_error

AttributeError: '_TdsSession' object has no attribute 'login'

[Derek]

So just an update, it appears there is an issue in tds.py and the author has added a branch sp1d3rx-problem and it does fix it.

Now the only issue with pytds is DSN support.\

To fix the tds.py look around line 2570 or so and comment out this:

#        if not msg['server'] and self.login:

#            msg['server'] = self.server_name

then edit your dal.py....

around line 3453 will be this:

return self.driver.connect(cnxn,**driver_args)

change it to this... 

return self.driver.connect(server=host,database=db,user=user,password=password,port=1433, tds_version=0x70000000,autocommit=False)

(use whichever tds_version matches your version of sql server. in my case, this is sql 2000. you can also just leave that off)

and you have a working pytds with web2py... which means you can run web2py under pypy (you could before, but this should run better) or even use gevent.

the problem with using gevent is that the sql communications, both pyodbc and pypyodbc rely on compiled c libraries and thus the communication with the sql server has to be synchronous. pytds is pure python so it doesn't have that issue.