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