"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\@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.
>
--
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.
