[sqlalchemy] MSSQL/PyODBC reflection problem
I'm using python 3.2.2, sqlalchemy 0.7.4 and pyodbc 3.0.2
from sqlalchemy import create_engine, MetaData, Table
engine = create_engine('mssql+pyodbc://user:pass@dsn')
metadata = MetaData(engine)
my_view = Table(PGRDAT, metadata, autoload=True)
and getting this error:
Traceback (most recent call last):
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 675, in _do_get
return self._pool.get(wait, self._timeout)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\util\queue.py, line 137, in get
raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File mssql_test.py, line 4, in module
my_view = Table(PGRDAT, metadata, autoload=True)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\schema.py, line 292, in __new__
table._init(name, metadata, *args, **kw)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\schema.py, line 353, in _init
self._autoload(metadata, autoload_with, include_columns)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\schema.py, line 377, in _autoload
self, include_columns
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2276, in run_callable
conn = self.contextual_connect()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2342, in contextual_connect
self.pool.connect(),
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 210, in connect
return _ConnectionFairy(self).checkout()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 371, in __init__
rec = self._connection_record = pool._do_get()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 697, in _do_get
con = self._create_connection()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 174, in _create_connection
return _ConnectionRecord(self)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 259, in __init__
pool.dispatch.first_connect.exec_once(self.connection, self)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\event.py, line 262, in exec_once
self(*args, **kw)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\event.py, line 271, in __call__
fn(*args, **kw)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\strategies.py, line 167, in first_connect
dialect.initialize(c)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\connectors\pyodbc.py, line 127, in initialize
super(PyODBCConnector, self).initialize(connection)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\dialects\mssql\base.py, line 1130, in initialize
super(MSDialect, self).initialize(connection)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\default.py, line 176, in initialize
self._get_default_schema_name(connection)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\dialects\mssql\base.py, line 1146, in
_get_default_schema_name
user_name = connection.scalar(SELECT user_name() as user_name;)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1339, in scalar
return self.execute(object, *multiparams, **params).scalar()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1405, in execute
params)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1582, in _execute_text
statement, parameters
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1665, in _execute_context
result = context.get_result_proxy()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\dialects\mssql\base.py, line 737, in get_result_proxy
return base.ResultProxy(self)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2722, in __init__
self._init_metadata()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2729, in _init_metadata
self._metadata = ResultMetaData(self, metadata)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2585, in __init__
colname = dialect._description_decoder(colname)
AttributeError: 'MSDialect_pyodbc' object has no attribute
'_description_decoder'
Any idea how to avoid this error?
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group,
Re: [sqlalchemy] MSSQL/PyODBC reflection problem
this is #2355:
http://www.sqlalchemy.org/trac/ticket/2355
Pyodbc + MSSQL is not yet tested for Py3K. (note it's on the map as
'development': www.sqlalchemy.org/docs/core/engines.html#supported-databases )
PyODBC + MSSQL is a tricky platform overall so you might need to stick with
py2k for the moment.The above ticket shouldn't be hard to fix, but it's not
yet clear what other pyodbc + py3k issues might need to be solved once that one
is fixed.
On Jan 2, 2012, at 4:48 AM, keson wrote:
I'm using python 3.2.2, sqlalchemy 0.7.4 and pyodbc 3.0.2
from sqlalchemy import create_engine, MetaData, Table
engine = create_engine('mssql+pyodbc://user:pass@dsn')
metadata = MetaData(engine)
my_view = Table(PGRDAT, metadata, autoload=True)
and getting this error:
Traceback (most recent call last):
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 675, in _do_get
return self._pool.get(wait, self._timeout)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\util\queue.py, line 137, in get
raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File mssql_test.py, line 4, in module
my_view = Table(PGRDAT, metadata, autoload=True)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\schema.py, line 292, in __new__
table._init(name, metadata, *args, **kw)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\schema.py, line 353, in _init
self._autoload(metadata, autoload_with, include_columns)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\schema.py, line 377, in _autoload
self, include_columns
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2276, in run_callable
conn = self.contextual_connect()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2342, in contextual_connect
self.pool.connect(),
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 210, in connect
return _ConnectionFairy(self).checkout()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 371, in __init__
rec = self._connection_record = pool._do_get()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 697, in _do_get
con = self._create_connection()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 174, in _create_connection
return _ConnectionRecord(self)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\pool.py, line 259, in __init__
pool.dispatch.first_connect.exec_once(self.connection, self)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\event.py, line 262, in exec_once
self(*args, **kw)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\event.py, line 271, in __call__
fn(*args, **kw)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\strategies.py, line 167, in first_connect
dialect.initialize(c)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\connectors\pyodbc.py, line 127, in initialize
super(PyODBCConnector, self).initialize(connection)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\dialects\mssql\base.py, line 1130, in initialize
super(MSDialect, self).initialize(connection)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\default.py, line 176, in initialize
self._get_default_schema_name(connection)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\dialects\mssql\base.py, line 1146, in
_get_default_schema_name
user_name = connection.scalar(SELECT user_name() as user_name;)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1339, in scalar
return self.execute(object, *multiparams, **params).scalar()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1405, in execute
params)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1582, in _execute_text
statement, parameters
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 1665, in _execute_context
result = context.get_result_proxy()
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\dialects\mssql\base.py, line 737, in get_result_proxy
return base.ResultProxy(self)
File c:\Python32\lib\site-packages\sqlalchemy-0.7.4-py3.2.egg
\sqlalchemy\engine\base.py, line 2722, in __init__
self._init_metadata()
File
[sqlalchemy] cascade delete in relationship and session.execute(table.update())
Hi,
Currently I am trying to implement a feature that transfers all
products in a store to another store, and then destroy the old store
which contains no product. The sqlalchemy version is 0.6.8.
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message. So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine. But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted? (2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Thank you very much, and happy new year!
--
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.
