Hello again.
It turned out that I was missing some ODBC-related packages and also needed to
configure freetds to work with unixodbc. I managed to finally do it, though it
was by no means easy for me (trial and error of several tutorials).
The following code works now:
import pyodbc
cnxn =
pyodbc.connect('DRIVER={FreeTDS};SERVER=1.2.3.4;PORT=1433;DATABASE=ZFP_CRM;UID=username;PWD=pass@cword;TDS_VERSION=8.0')
cursor = cnxn.cursor()
cursor.execute("select * from mlm_spol")
row = cursor.fetchone()
print row
However SqlSoup does not work. The code:
import pyodbc
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup
def connect():
return
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=ZFP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0;')
engine = create_engine('mssql+pyodbc://', creator=connect)
db = SqlSoup(engine)
row = db.mlm_spol.first()
print row
fails with:
Traceback (most recent call last):
File "/home/lada/mine/devel/python/ZFP/zfp_connect.py", line 16, in <module>
x = db.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 2426,
in run_callable
return conn.run_callable(callable_, *args, **kwargs)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1969,
in run_callable
return callable_(self, *args, **kwargs)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line
260, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line
412, in reflecttable
raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: mlm_spol
Any ideas?
Thanks,
Ladislav Lenart
On 8.10.2012 11:11, Ladislav Lenart wrote:
> 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\@word@10.230.128.140: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, lenart...@volny.cz 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" <mike...@zzzcomputing.com>
>>>> 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 sqlalchemy@googlegroups.com.
>>>>> To unsubscribe from this group, send email to
>>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>>> 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 sqlalchemy@googlegroups.com.
>>>> To unsubscribe from this group, send email to
>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>> 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 sqlalchemy@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> 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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
