Hello.
UPDATE: The raw SqlSoup.execute() works:
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)
x = db.execute("select * from mlm_spol").fetchone()
print x
Any ideas what I have to do to make SqlSoup's ORM work? I am out of ideas.
Ladislav Lenart
On 8.10.2012 15:02, Ladislav Lenart wrote:
> 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.
