I never for a moment thought that your change was thoughtless. To the contrary, I have huge respect for SQLAlchemy. I will try to test the drop_all and your pyodbc issue with my setup and to report here later today.
Meanwhile, I can tell you how to build the stack because it is a bit tricky given certain package issues. I chose to build unixODBC and FreeTDS and pyodbc from sources into /usr/local. 1. unixODBC First I removed system packages unixODBC and unixODBC-dev (names may vary by Linux distro). Then wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz tar zxvf unixODBC-2.3.0.tar.gz cd unixODBC-2.3.0 ./configure make sudo make install As root ensure that /usr/local/etc/odbcinst.ini has the following entries: [FreeTDS] Driver = /usr/local/lib/libtdsodbc.so [SQLServer] Driver = /usr/local/lib/libtdsodbc.so The second entry is for aesthetics only - so you can use "SQLServer" in your connection strings. Odbc.ini file is not important if you use fully qualified host names in your connection string but at your option you may configure DSNs there. 2. FreeTDS Also uninstall any system packages that you may have for this, then: wget http://www.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz tar xvf freetds-stable.tgz cd freetds-0.91 ./configure --with-unixodbc=/usr/local --enable-msdblib --with- tdsver=8.0 make sudo make install Remove or otherwise disable /etc/freetds.conf if any. Modify /usr/ local/etc/freetds.conf to read as follows: [global] # TDS protocol version tds version = 8.0 client charset = UTF-8 # This is probably not needed... text size = 64512 # Typical SQLServer [server_name] host = hostname.somedomain port = 2431 tds version = 8.0 At this point you should be able to login successfully using: tsql -S server_name -U username -P password 3. pyodbc Pyodbc package on pypi is currently broken in that it is missing a utils and web folders from sources and does not build. There is a bug for this (with my comments too) at http://code.google.com/p/pyodbc/issues/detail?id=192. There are two ways around it but before you start (and at runtime) make sure that /usr/local/lib is in LD_LIBRARY_PATH (or add it permanently system-wide using ldconfig). export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH 3.1 Quick way pip install https://github.com/mkleehammer/pyodbc/zipball/2.1.9 This will result in pyodbc-2.1.0-unsupported out of 2.1.9 code base installed, which looks ugly and may potentially confuse other packages, which check its version. But it works fine. The reason for this is that the github code version does not hat PKG-INFO file. 3.2 Longer way, which I followed 3.2.1 Get official package and git sources wget http://pyodbc.googlecode.com/files/pyodbc-2.1.9.zip unzip pyodbc-2.1.9.zip wget --no-check-certificate -O git-pyodbc-2.1.9.zip https://github.com/mkleehammer/pyodbc/zipball/2.1.9 unzip git-pyodbc-2.1.9.zip 3.2.2 Copy missing utils and web folders to the packaged version cp -R mkleehammer-pyodbc-e3c95dc/utils/ pyodbc-2.1.9/ cp -R mkleehammer-pyodbc-e3c95dc/web/ pyodbc-2.1.9/ 3.2.3 Build and install python setup.py bdist_egg cd dist easy_install pyodbc-2.1.9-py2.6-linux-i686.egg # filename may vary based on architecture At run time be sure to have the LD_LIBRARY_PATH and TDSVER=8.0 variables set. The latter is not needed if you put the same into connection string as I have in the example above. I hope this helps. Victor Olex http://linkedin.com/in/victorolex http://twitter.com/agilevic On Sep 7, 11:53 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > The ticket for SQLAlchemy is: > > http://www.sqlalchemy.org/trac/ticket/2273 > > For Pyodbc I've opened: > > http://code.google.com/p/pyodbc/issues/detail?id=209http://code.google.com/p/pyodbc/issues/detail?id=210 > > as you can see, issue 210 is quite serious. Would be curious what results > you get for the script there. > > On Sep 7, 2011, at 11:25 PM, Michael Bayer wrote: > > > I can't actually make that string work at all with FreeTDS, but I am on > > 0.82. If I turn on Python unicodes with FreeTDS 0.82, which until > > recently was the FreeTDS release for years, everything breaks immediately - > > the CREATE TABLE statements won't even work, as you can see below just the > > strings u'A', u'dbo' blow it up: > > > sqlalchemy.exc.DBAPIError: (Error) ('HY004', '[HY004] [FreeTDS][SQL > > Server]Invalid data type (0) (SQLBindParameter)') 'SELECT > > [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], > > [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], > > [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], > > [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], > > [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], > > [COLUMNS_1].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS > > [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND > > [COLUMNS_1].[TABLE_SCHEMA] = ?' (u'A', u'dbo') > > > You can be assured that the code you see in pyodbc.py is not by accident - > > dozens of hours went into making this thing work with Pyodbc + FreeTDS, and > > I would vastly prefer that it just accept u'' strings - but on 0.82, it > > does not. > > > So I'm just thrilled that A. FreeTDS has apparently broken compatibility > > with all of that effort and B. I can't barely even get FreeTDS 0.91 to > > work, nor can I C. get pyodbc 2.1.9 to build. > > > FreeTDS 0.91 doesn't appear to work for me period. Your exact query > > *does* work, but if I try to create or drop tables, I get either: > > > MemoryError: > > > or an erroneously blank result set, when trying to query from > > INFORMATION_SCHEMA.COLUMNS, depending on how I set up that flag. > > > So I can't really test or do anything with FreeTDS 0.91. > > > Can you please try a "metadata.drop_all()" and "metadata.create_all()" for > > me and tell me if it works as expected, both with and without the patch ? > > > Your flag is not a big deal but the much more ominous issue is a whole new > > set of users installing 0.91 and not being able to do simple checks for > > table existence. > > > What OS you're on would be helpful here as well, as it appears I'm at least > > going to have to test from a linux VM to a windows VM to even get this > > going. > > > On Sep 7, 2011, at 7:12 PM, Victor Olex wrote: > > >> Using SQLAlchemy 0.7.2 with pyodbc 2.1.9, FreeTDS 0.91, unixODBC 2.3.0 > >> and SQL Server 2008 I find that the supports_unicode_bind may be > >> incorrectly set to False in the PyODBCConnector.initialize. As a > >> result a unicode parameter gets encoded as str and to make matters > >> worse the value gets silently overridden with empty Unicode string > >> (u''). > > >> Consider a simple table (IDENTITY, NTEXT, VARCHAR(255)) with one > >> record: > >> ID, col1, col2 > >> 1, 'Łódź', 'abc'. > > >> We will update existing value in col1 to 'Łódź!'. > > >>>>> from sqlalchemy import Column, Sequence, create_engine > >>>>> from sqlalchemy.types import UnicodeText, Integer, VARCHAR > >>>>> from sqlalchemy.orm import sessionmaker > >>>>> from sqlalchemy.ext.declarative import declarative_base > > >>>>> Base = declarative_base() > >>>>> metadata = Base.metadata > > >>>>> class A(Base): > >> ... __tablename__ = 'A' > >> ... id = Column(u'ID', Integer, Sequence('A_PK'), > >> primary_key=True) > >> ... col1 = Column(u'col1', UnicodeText()) > >> ... col2 = Column(u'col2', VARCHAR(255)) > >> ... > >>>>> e = > >>>>> create_engine('mssql://user:pwd@sqlserverhost:2431/MYDB?driver=FreeTDS&TDS_Version=8.0', > >>>>> echo=True) > >>>>> Session=sessionmaker() > >>>>> s = Session(bind=e) > >>>>> lodz = u'\u0141\xf3d\u017a' > >>>>> oa = s.query(A).one() > >> 2011-09-07 17:22:25,260 INFO sqlalchemy.engine.base.Engine SELECT > >> user_name() as user_name; > >> 2011-09-07 17:22:25,261 INFO sqlalchemy.engine.base.Engine () > >> 2011-09-07 17:22:25,270 INFO sqlalchemy.engine.base.Engine > >> SELECT default_schema_name FROM > >> sys.database_principals > >> WHERE name = ? > >> AND type = 'S' > > >> 2011-09-07 17:22:25,271 INFO sqlalchemy.engine.base.Engine > >> (u'SPEED_IT',) > >> 2011-09-07 17:22:25,291 INFO sqlalchemy.engine.base.Engine BEGIN > >> (implicit) > >> 2011-09-07 17:22:25,292 INFO sqlalchemy.engine.base.Engine SELECT [A]. > >> [ID] > >> AS [A_ID], [A].col1 AS [A_col1], [A].col2 AS [A_col2] > >> FROM [A] > >> 2011-09-07 17:22:25,292 INFO sqlalchemy.engine.base.Engine () > >>>>> oa.col1 > >> u'\u0141\xf3d\u017a' > >>>>> oa.col2 > >> 'abc' > >>>>> oa.col1 = u'\u0141\xf3d\u017a!' > >>>>> s.commit() > >> 2011-09-07 17:23:17,016 INFO sqlalchemy.engine.base.Engine UPDATE [A] > >> SET > >> col1=? WHERE [A].[ID] = ? > >> 2011-09-07 17:23:17,016 INFO sqlalchemy.engine.base.Engine > >> ('\xc5\x81\xc3\xb3d\xc5\xba!', 1) > >> 2011-09-07 17:23:17,061 INFO sqlalchemy.engine.base.Engine COMMIT > >>>>> oa.col1 > >> 2011-09-07 17:23:24,226 INFO sqlalchemy.engine.base.Engine BEGIN > >> (implicit) > >> 2011-09-07 17:23:24,227 INFO sqlalchemy.engine.base.Engine SELECT [A]. > >> [ID] > >> AS [A_ID], [A].col1 AS [A_col1], [A].col2 AS [A_col2] > >> FROM [A] > >> WHERE [A].[ID] = ? > >> 2011-09-07 17:23:24,227 INFO sqlalchemy.engine.base.Engine (1,) > >> u'' > > >> Using a patched initialize method with the supports_unicode_binds line > >> #110 removed the parameter gets passed as Unicode and the database > >> updates correctly as does the in memory object. Different version > >> combinations of pyodbc, FreeTDS and SQL may likely yield a different > >> result so unless a deterministic factor is found I would like to > >> propose adding parameter bind_unicode to dialect class and connection > >> url. > > >> Regards and respect, > > >> Victor Olex > >>http://linkedin.com/in/victorolex > >>http://twitter.com/agilevic > > >> -- > >> 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 > >> athttp://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 > > athttp://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.
