Hello.
> otherwise, I'd consider using pyodbc for which the dialect and DBAPI are
production quality. I use pyodbc with FreeTDS on unix platforms in production.
Ok, I can use pyodbc if it is the preferred choice. However I cannot make it
work either. I suspect that I supply bad connection string but am a little lost
in the docs...
The code:
from sqlalchemy.ext.sqlsoup import SqlSoup
if __name__ == '__main__':
conn_string =
'mssql+pyodbc://username:pass\@[email protected]:1433/ZFP_CRM'
db = SqlSoup(conn_string)
x = db.zfp_mlm_spol.first()
fails (see the traceback below). Note the password contains the character '@'.
The preceeding '\' is my attempt to escape it.
What connection string should I use to connect to MSSQL via pyodbc using
freetds?
Thank you,
Ladislav Lenart
THE TRACEBACK:
Traceback (most recent call last):
File
"/home/lada/.eclipse/org.eclipse.platform_3.8_155965261/plugins/org.python.pydev_2.6.0.2012062818/pysrc/pydevd.py",
line 1392, in <module>
debugger.run(setup['file'], None, None)
File
"/home/lada/.eclipse/org.eclipse.platform_3.8_155965261/plugins/org.python.pydev_2.6.0.2012062818/pysrc/pydevd.py",
line 1085, in run
pydev_imports.execfile(file, globals, locals) #execute the script
File "/home/lada/mine/devel/python/ZFP/zfp_connect.py", line 11, in <module>
x = db.zfp_mlm_spol.first()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 807,
in __getattr__
return self.entity(attr)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 804,
in entity
return self.map_to(attr, tablename=attr, schema=schema)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 684,
in map_to
schema=schema or self.schema)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 318, in
__new__
table._init(name, metadata, *args, **kw)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 381, in
_init
self._autoload(metadata, autoload_with, include_columns)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 397, in
_autoload
self, include_columns, exclude_columns
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2424,
in run_callable
conn = self.contextual_connect()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2490,
in contextual_connect
self.pool.connect(),
File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 224, in
connect
return _ConnectionFairy(self).checkout()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 387, in
__init__
rec = self._connection_record = pool._do_get()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 741, in
_do_get
con = self._create_connection()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 188, in
_create_connection
return _ConnectionRecord(self)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 270, in
__init__
self.connection = self.__connect()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 330, in
__connect
connection = self.__pool._creator()
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line
80, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line
281, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver
Manager]Data source name not found, and no default driver specified (0)
(SQLDriverConnectW)') None None
On 6.10.2012 00:47, Michael Bayer wrote:
> what I can do for the moment is this patch, if you want to try it:
>
> diff -r 17cab4ad55d5 lib/sqlalchemy/dialects/mssql/pymssql.py
> --- a/lib/sqlalchemy/dialects/mssql/pymssql.py Thu Oct 04 18:26:55
> 2012 -0400
> +++ b/lib/sqlalchemy/dialects/mssql/pymssql.py Fri Oct 05 18:46:01
> 2012 -0400
> @@ -80,7 +80,7 @@
> def _get_server_version_info(self, connection):
> vers = connection.scalar("select @@version")
> m = re.match(
> - r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
> + r"\s*Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
> if m:
> return tuple(int(x) for x in m.group(1, 2, 3, 4))
> else:
>
>
>
> otherwise, I'd consider using pyodbc for which the dialect and DBAPI are
> production quality. I use pyodbc with FreeTDS on unix platforms in
> production.
>
>
>
>
> On Oct 5, 2012, at 1:40 PM, [email protected] wrote:
>
>> Hello.
>>
>>> I dont have easy access to pymssql here so can you fully define what
>>> "fails" means ? stack trace ?
>>
>> I don't have access to my development environment during the weekend, so I
>> cannot provide you with a stacktrace, but I try to better describe the issue:
>>
>> def _get_server_version_info(self, connection)
>> vers = connection.scalar("select @@version")
>> m = re.match(r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)",
>> vers)
>> ...
>>
>> The above code snippet is from the file pymssql (around line 80). The
>> variable vers is set to None and because of that the following regex fails
>> with error "Expected string or buffer". The None is returned by the call to
>> scalar(). The code snippet (from memory, I don't remember its exact location
>> and form):
>>
>> iter(resultproxy).next()
>>
>> is called to get a next (first) result from the result proxy and it simply
>> returns None as if there were no rows.
>>
>> Ladislav Lenart
>>
>>
>> Od: "Michael Bayer" <[email protected]>
>>> On Oct 5, 2012, at 9:40 AM, Ladislav Lenart wrote:
>>>
>>>> Hello.
>>>>
>>>> I try to access a Microsoft SQL database from Linux (Debian testing):
>>>>
>>>> from sqlalchemy.ext.sqlsoup import SqlSoup
>>>>
>>>> conn_string = 'mssql+pymssql://user:pass@freetds_name'
>>>> db = SqlSoup(conn_string)
>>>> v = db.some_table.first()
>>>> print v
>>>>
>>>> freetds_name is the section name from /etc/freetds/freetds.conf
>>>>
>>>> [freetds_name]
>>>> host = ...
>>>> port = 1433
>>>> tds version = 7.1
>>>> asa database = DB
>>>>
>>>> The above script fails
>>>
>>>
>>> I dont have easy access to pymssql here so can you fully define what
>>> "fails" means ? stack trace ?
>>>
>>>
>>>
>>>
>>>> in pymssql on line 83, because line 81 sets vers to None:
>>>>
>>>> def _get_server_version_info(self, connection):
>>>> vers = connection.scalar("select @@version")
>>>> m = re.match(
>>>> r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
>>>> if m:
>>>> return tuple(int(x) for x in m.group(1, 2, 3, 4))
>>>> else:
>>>> return None
>>>>
>>>> But the following works in tsql:
>>>>
>>>> 1> select @@version
>>>> 2> go
>>>>
>>>> Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)
>>>> Apr 22 2011 19:23:43
>>>> Copyright (c) Microsoft Corporation
>>>> Workgroup Edition (64-bit) on Windows NT 6.1 <X64>
>>>> (Build 7601: Service Pack 1) (Hypervisor)
>>>>
>>>> (1 row affected)
>>>>
>>>>
>>>> Any idea what is wrong?
>>>>
>>>>
>>>> Thank you,
>>>>
>>>> Ladislav Lenart
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to [email protected].
>>>> To unsubscribe from this group, send email to
>>>> [email protected].
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
