Ok, I will give it yet another try, but please note that the following 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
i.e. raw SqlSoup.execute() sees the table and returns a valid result.
I regard this as a proof that the connection was established successfully and
the problem lies elsewhere. Am I wrong?
Ladislav Lenart
On 8.10.2012 16:20, Michael Bayer wrote:
> "no such table" usually means you're not connected to the database that you
> think you are.
>
> I'd strongly suggest configuring an ODBC datasource within FreeTDS, and using
> standard connection techniques. Hostname, port, tds version go into
> freetds.conf, and database names go into odbc.ini.
>
> In freetds conf for example I have:
>
> [ms_2005]
> host = 172.16.248.128
> port = 1213
> tds version = 8.0
> client charset = UTF8
> text size = 50000000
>
>
> and on my mac in /Library/ODBC/odbc.ini I have:
>
> [ODBC Data Sources]
> ms_2005 = test
>
> [ms_2005]
> Driver = /usr/local/lib/libtdsodbc.so
> Description = test
> Trace = No
> Servername = ms_2005
>
>
> I then connect with SQLAlchemy as:
>
> create_engine("mssql://scott:tiger@ms_2005")
>
> see http://freetds.schemamania.org/userguide/prepodbc.htm for freetds' docs
> on all this.
>
>
>
> On Oct 8, 2012, at 9:02 AM, 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\@[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.
>>
>
--
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.
