Web2py can't connect to mssql via pypyodbc - but possible to connect from idle

[achristoffersen]

(x-post from stackoverflow: http://stackoverflow.com/questions/31371462/web2py-cant-connect-to-mssql-via-pypyodbc-but-possible-to-connect-from-idle)

I can connect via 'naked' pypyodbc, but not via the web2py DAL.

I installed pypyodbc as per these instructions: https://code.google.com/p/pypyodbc/wiki/Linux_ODBC_in_3_steps

In my web2py model I have:

import pypyodbc # ps. Anthony informs me there is no need for this.
px = DAL('mssql4://username:password@url,portnumber/databasename')

In my controller I then have:

def index():
    return dict(message=(px.executesql('SELECT top 1 * FROM table;')))

Which gives me a ticket with the following traceback:

Traceback (most recent call last):
  File "/home/andreas/web2py_project/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/andreas/web2py_project/web2py/applications/welcome/models/db.py", line 95, in <module>
    px = DAL('mssql4://UID:password@URL,port/database')
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", line 174, in __call__
    obj = super(MetaDAL, cls).__call__(*args, **kwargs)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", line 459, 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 "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", line 437, in __init__
    self._adapter = ADAPTERS[self._dbname](**kwargs)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/base.py", line 57, in __call__
    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 106, in __init__
    if do_connect: self.find_driver(adapter_args,uri)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/base.py", line 188, in find_driver
    raise RuntimeError("no driver available %s" % str(self.drivers))
RuntimeError: no driver available ('pyodbc',)

Which I find I weird, since I try to use pypyodbc, and not pyodbc. (I tried to import pypyodbc as pyodbc btw, same result).

If I open a python prompt I can do this:

import pypyodbc
cnxn = pypyodbc.connect('Driver=FreeTDS; Server=url; port= portnumber; uid=username; pwd=password;database=database')
cursor = cnxn.cursor()
cursor.execute("select top 1 * from a_table where a_date > getdate() order by newid()")
rows = cursor.fetchall()
for row in rows:
     print row

Which then outputs the desired single random row.

So either web2py DAL is broken (not likely) or I am doing something wrong?

p.s. Web2py version:

2.11.2-stable+timestamp.2015.05.30.16.33.24
(Running on Rocket 1.2.6, Python 2.7.6)

[Niphlod]

web2py doesn't use freetds by default as a driver, but SQL Server... so if you can connect with freetds with pypyodbc it's not said that the same works within web2py (unless you use the same driver args).

That being said, the error "no driver available" smells. How did you install web2py ?

[Jose]

Hi

Please see here [1]

José


[1] https://groups.google.com/forum/#!searchin/web2py/Help$20with$20MSSQL$20Connection/web2py/fFgJMgYHbBQ/ZLji6jjkYzEJ

[achristoffersen]

Thanks.

I have web2py installed in a virtual environment. (basically just unzipped in to the venv). `which python` confirms that `python web2py.py` is using the venv python.

The error message changes when I out-comment the import pypyodbc statement and just call the DAL directly

px = DAL('mssql4://user:pass@url,port/dabase')

It now says:

Traceback (most recent call last):
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/base.py", line 437, in __init__
    self._adapter = ADAPTERS[self._dbname](**kwargs)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/base.py", line 57, in __call__
    obj = super(AdapterMeta, cls).__call__(*args, **kwargs

)
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 160, in __init__
    if do_connect: self.reconnect()
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/connection.py", line 105, in reconnect
    self.connection = f()
  File "/home/andreas/web2py_project/web2py/gluon/packages/dal/pydal/adapters/mssql.py", line 158, in connector
    return self.driver.connect(cnxn, **driver_args)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "/home/andreas/web2py_project/venv/local/lib/python2.7/site-packages/pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
Error: (u'IM002', u'[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified')

Hope this makes sense?

[achristoffersen]

Hi Jose,

Thanks

Okay - So if I run python web2py -M

I get this:
Version 2.11.2-stable+timestamp.2015.05.30.16.33.24
Database drivers available: sqlite3, imaplib, pyodbc, pymysql, pg8000

I guess pyodbc here is really pypyodbc?

A

Also: Thanks for the pointers. Is your recommendation to use DNS instead, like the example?

(here is important indentation)
[My_SQLServer]
    host = my_host
    port = 1433
    tds version = 8.0


in model
db = DAL("mssql://dsn=MY_NAME;UID=

user;PWD=password", pool_size=20)

?

[Niphlod]

On Monday, July 13, 2015 at 10:00:08 PM UTC+2, achristoffersen wrote:

Hi Jose,

Thanks

Okay - So if I run python web2py -M

I get this:
Version 2.11.2-stable+timestamp.2015.05.30.16.33.24
Database drivers available: sqlite3, imaplib, pyodbc, pymysql, pg8000

I guess pyodbc here is really pypyodbc?

yep, if pyodbc is not found system-wide, pypyodbc kicks in as a drop-in replacement

 

[achristoffersen]

Hi again Jose

Since my error mesage now says something like:

Data source name not found, and no default driver specified')

I decided to try the DNS route.
I have tried to edit both /usr/share/freetds/freetds.conf and /etc/freetds/freetds.conf. In both cases I added a DNS. That changed nothing. I still get

Data source name not found, and no default driver specified')

Sincerely

On Monday, July 13, 2015 at 10:00:08 PM UTC+2, achristoffersen wrote:

[achristoffersen]

Thanks again Niphlod

I don't think using SQL server is an option in linux-land?  All the examples I have seen uses FreeTDS.

On Monday, July 13, 2015 at 6:34:30 PM UTC+2, Niphlod wrote:

[Rowdy]

On 13/07/2015 16:02, achristoffersen wrote:
> (x-post from stackoverflow:
> http://stackoverflow.com/questions/31371462/web2py-cant-connect-to-mssql-via-pypyodbc-but-possible-to-connect-from-idle)
>
> I can connect via 'naked' pypyodbc, but not via the web2py DAL.
>

For a non-web2py application, I have had success using pytds [1]. On
Linux too!

pytds describes itself as "Python DBAPI driver for MSSQL using pure
Python TDS (Tabular Data Stream) protocol implementation".

It does not seem to be supported by web2py at the moment, but given that
it has no external dependencies (dependency on bitarray is optional to
improve performance) it might make a good addition to web2py database
support at some future date.

Rowdy

[1] https://pypi.python.org/pypi/python-tds

[Niphlod]

freetds with (py)pyodbc MUST have freetds as a "proxy" to connect from a linux host. The trick is understanding what parameters needs to be passed to py(pyodbc) to connect.

As mssql is usually used from windows host, the default "connectionstring-builder" uses parameters that are valuable when connecting from windows, but it's just a matter to pass either as a DSN or to pass a proper argument to driver_args to enable linux hosts to be able to connect. Given that usually those are in tandem with unixodbc + freetds setup on the server, a little tinkering is required.

I **think** that if you pass mssql4://dsn it works .

A working DSN **may be**

DRIVER=FreeTDS;SERVER=hostname;PORT=1433;DATABASE=dbname;UID=user;PWD=password;TDS_Version=8.0;

just to start.

BTW, passing 

DAL(mssql4://user:password@hostname/dbname, driver_args = {'DRIVER' : 'FreeTDS', 'TDS_Version' : '8.0'})

should result in the same exact underlying connection.