[sqlalchemy] Autoload, Oracle and unicode conversion
When working with Oracle, we use the `convert_unicode` parameter, and
use Python unicode objects everywhere. This works fabulously, and we
even set `assert_unicode` to 'error' to make sure that nothing else is
ever used.
However, when reflecting a table using the `autoload` keyword, these
properties are not set on the resulting columns. This leads to
interesting errors such as:
sqlalchemy.exc.NotSupportedError: (NotSupportedError)
Variable_TypeByValue(): unhandled data type unicode 'INSERT INTO
testtable5 (id, b) VALUES (:id, :b)' {'b': u'test', 'id': None}
Is there a way to instrument the columns by telling the autoload
mechanism that I would like to use `convert_unicode` for the columns
where it makes sense, or by modifying the reflected columns after the
table has been loaded?
We're using SQLAlchemy 0.5. For convenience, here is a piece of code
to test it with:
from sqlalchemy import create_engine, MetaData, Table, String,
Integer, CLOB, Column, Sequence
users_metadata = MetaData()
table_name = 'testtable'
testtable = Table(
table_name, users_metadata,
Column('id', Integer, Sequence(table_name + '_id_sequence',
start=1, increment=1), primary_key=True),
Column('b', CLOB(convert_unicode=True,
assert_unicode='error')))
engine = create_engine('oracle://localhost/...', echo=True)
connection = engine.connect()
users_metadata.bind = connection
testtable.create(engine)
# works
connection.execute(testtable.insert(values={'b': u'test'}))
metadata = MetaData(bind=connection)
testtable = Table(table_name, metadata, autoload=True)
# fail
connection.execute(testtable.insert(values={'b': u'test'}))
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Sprox 0.6.6 Released
I lied, we found a few more ways to tweak sprox 0.6. (www.sprox.org) Thanks to Alessandro Molina (amol) for providing a speed enhancement when rendering dictionaries. Thanks to Temmu Yli-Elsila for his help in debugging the preventCache problem with DojoGrid in IE. Sprox will now work properly with IE 7 for the grid, but you will need tw/tw.dojo 0.9.8, which I expect to release tomorrow. http://pypi.python.org/pypi/sprox/0.6.6 cheers. -chris --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Want a custom implementation of get_bind that ignores metadata bindings
Yup. We screwed up by using metadata.bind but I think we may be stuck
with it. Is it possible to bind a metadata collection within a
session? i.e would session.configure(binds={metadata_collection_1 :
e1, metadata_collection_2 : e2}) work? We would like to be able to
bind groups of tables at the same time rather than doing them
individually or having a single common bind for the session ... a lot
of our applications access data across multiple data-servers and with
multiple-logins
pjjH
On Sep 24, 12:12 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 23, 2009, at 6:36 PM, phrrn...@googlemail.com wrote:
I have a hidden WriterSession which I am using behind the scenes to
manage a number of API entries that write data in bulk e.g. upsert
(MappedClass, iterator_that_returns_dicts). I want the session to only
look at its own binds and to ignore any that are in place on the
metadata collection. I wrote my own get_bind that does this
(horrible!) hack:
if self._Session__binds:
b = self._Session__binds
if c_mapper:
if c_mapper.base_mapper in b:
return b[c_mapper.base_mapper]
elif c_mapper.mapped_table in b:
return b[c_mapper.mapped_table]
if self.bind:
return self.bind
I don't really understand how the double underscore stuff works in
Python. Mike, how would you feel about exposing the session bind
information with an interface that is more amenable to subclassing?
The binds collection on Session is set via the binds argument, or
one at a time using bind_mapper() and bind_table(). get_bind() does
not consult the metadata's bind unless none of session.bind or or
__binds has been configured. So there shouldn't be any need to
hack get_binds().
Also I would strongly advise against using metadata.bind for any
application that uses more than one engine. Here's what the 0.5
docs
athttp://www.sqlalchemy.org/docs/05/metadata.html#binding-metadata-to-a...
have to say:
Note that the feature of binding engines is completely optional. All
of the operations which take advantage of “bound” MetaData also can be
given an Engine or Connection explicitly with which to perform the
operation.
Here's what 0.6 has to say
athttp://www.sqlalchemy.org/docs/06/metadata.html#binding-metadata-to-a...
:
Binding the MetaData to the Engine is a completely optional feature.
The above operations can be achieved without the persistent bind using
parameters: (examples)
Should you use bind ? It’s probably best to start without it. If you
find yourself constantly needing to specify the same Engine object
throughout the entire application, consider binding as a convenience
feature which is applicable to applications that don’t have multiple
engines in use and don’t have the need to reference connections
explicitly. It should also be noted that an application which is
focused on using the SQLAlchemy ORM will not be dealing explicitly
with Engine or Connection objects very much in any case, so it’s
probably less confusing and more “future proof” to not use the bind
attribute.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: unexpected chained relations and append behaviour
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty Sent: 24 September 2009 16:16 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: unexpected chained relations and append behaviour Hello Simon, thanks for your answer, I will have a look into that. By the way: len(car.parts) does indeed work, try it ;) Greetings, Tom len(car.parts) works with your current configuration, because accessing car.parts loads the entire relation and returns it as a python list. But if you change it to be a 'dynamic' relation, it will no longer be a list but a Query instance, which no longer has a __len__ method. Simon --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Want a custom implementation of get_bind that ignores metadata bindings
phrrn...@googlemail.com wrote:
Yup. We screwed up by using metadata.bind but I think we may be stuck
with it.
why is that ? just detach it. Unless you have lots of
mytable.select().execute() types of statements going on, nothing will
really happen.
But also, if you are setting Session.bind/binds, it doesn't even matter.
Is it possible to bind a metadata collection within a
session? i.e would session.configure(binds={metadata_collection_1 :
e1, metadata_collection_2 : e2}) work? We would like to be able to
bind groups of tables at the same time rather than doing them
individually or having a single common bind for the session ... a lot
of our applications access data across multiple data-servers and with
multiple-logins
sure !
def bind_metadata_to_engine_in_my_session(engine, session, metadata):
session.configure(binds=dict((table, engine) for table in
metadata.tables.values()))
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Text vs. PGText possible bug
Ran across something that I suspect might be a bug. If I define my
table like:
asset_table = Table('asset', metadata,
Column('path', Text, primary_key=True,
server_default=FetchedValue(),
server_onupdate=FetchedValue()),
autoload=True)
Then anytime I query for an asset and eagerload a related table the
backref on the related table isn't populated, causing a second query to
the DB.
If instead I define that column of type PGText then the backrefs are
populated properly. I attached a test which is a simplified version of
my table mappings.
Attached is a test of this behavior. The output when the column is
defined as Text or String looks like:
testshow/eps/201/s01/t01
testshow/chr/test/test
2009-09-24 17:17:03,214 INFO sqlalchemy.engine.base.Engine.0x...1f10
SELECT asset.updated AS asset_updated, asset.name AS asset_name,
asset.type AS asset_type, asset.path AS asset_path, asset.parent AS
asset_parent, asset.is_file AS asset_is_file, asset.created_by AS
asset_created_by
FROM asset
WHERE asset.path = %(param_1)s
2009-09-24 17:17:03,214 INFO sqlalchemy.engine.base.Engine.0x...1f10
{'param_1': 'testshow/eps/201/s01/t01'}
testshow/eps/201/s01/t01
When defined as PGText the output is:
testshow/eps/201/s01/t01
testshow/chr/test/test
testshow/eps/201/s01/t01
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
import sys
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.types import *
from sqlalchemy.databases.postgres import PGText
DB_HOST = 'localhost'
DB_NAME = 'test_db'
DB_USER = 'testuser'
DB_PASS = 'testpass'
db_uri = 'postgres://%s:%...@%s/%s' % (DB_USER,DB_PASS,DB_HOST,DB_NAME)
db = create_engine (db_uri)
metadata = MetaData(db)
class Asset(object):
pass
class AssetRelation(object):
pass
#asset_table = Table('asset', metadata,autoload=True)
#asset_table = Table('asset', metadata,
# Column('path', Text, primary_key=True,
# server_default=FetchedValue(),
# server_onupdate=FetchedValue()),
# autoload=True)
asset_table = Table('asset', metadata,
Column('path', PGText, primary_key=True,
server_default=FetchedValue(),
server_onupdate=FetchedValue()),
autoload=True)
relation_table = Table('relation',metadata, autoload=True)
asset_mapper = mapper(Asset, asset_table,
properties = {
'Related' : relation(AssetRelation, backref='Source', primaryjoin=asset_table.c.path==relation_table.c.src_asset,order_by=relation_table.c.target_asset,lazy=True)
})
mapper(AssetRelation, relation_table, properties = {
'Target' : relation(Asset, backref='Relatee', primaryjoin=asset_table.c.path==relation_table.c.target_asset, viewonly=True,lazy=False)
})
session=create_session()
a=session.query(Asset).options(eagerload(Asset.Related)).get('testshow/eps/201/s01/t01')
db.echo=True
print a.path
r=a.Related[0]
print r.target_asset
b=r.Source
print b.path
session.close()
sys.exit(0)
