[sqlalchemy] relationship trouble when using UsageRecipes/Views
I am working on my localization stuff and run into a problem when I want
to add an relationship to a class.
I based my code on
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views , but maybe I
messed something up.
The following works:
r1 = session.query(db.Region_LV).get(175)
print type(result.country_lv)
print r1
print r1.country_lv.name
with this output:
Region_LV(centralkey=175, country_lv=Country_LV(centralkey=83,
created_at=datetime.datetime(2011, 9, 7, 9, 39, 10, 702000),
created_by=None, dialcode=30, fk_language_id=2, id=83, is2code=u'GR',
iswinecountry=1, name=u'Gr\xe8ce', shortname=None, un3code=u'GRC',
updated_at=datetime.datetime(2011, 9, 7, 9, 39, 10, 702000),
updated_by=None, website1=None, website2=None),
created_at=datetime.datetime(2011, 9, 7, 9, 39, 11, 452000),
created_by=None, fk_country_id=83, fk_language_id=2, id=175,
name=u'Sud-Ouest', shortname=u'Sud-Ouest',
updated_at=datetime.datetime(2011, 9, 7, 9, 39, 11, 452000),
updated_by=None)
Grèce
The model for all this is:
class Region(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'region'
centralkey = sa.Column(sa.BigInteger())
name = sa.Column(sa.Unicode(length=50), nullable=False)
shortname = sa.Column(sa.Unicode(length=10))
fk_country_id = sautils.reference_col('country')
__localize_columns__ = ['name', 'shortname']
Region_L, Region_LV = sautils.CreateLocalized(Region())
Region_LV.country_lv = sao.relationship('Country_LV')
CreateLocalized creates the Region_LV based on the usage recipe.
Now if I add:
Region_LV.language = sao.relationship('Language')
I get:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship Region_LV.language. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
needed as well.
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Region_LV.fk_language_id==Language.id)
I get:
sqlalchemy.exc.ArgumentError: Could not determine relationship direction
for primaryjoin condition 'country_lv.fk_language_id = language.id', on
relationship Region_LV.language. Ensure that the referencing Column
objects have a ForeignKey present, or are otherwise part of a
ForeignKeyConstraint on their parent Table, or specify the foreign_keys
parameter to this relationship.
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected
for argument 'foreign_keys'; got: 'Country_LV.fk_language_id', type
type 'str'
sqlalchemy.exc.ArgumentError: Could not locate any foreign-key-equated,
locally mapped column pairs for primaryjoin condition
'country_lv.fk_language_id = language.id' on relationship
Region_LV.language. For more relaxed rules on join conditions, the
relationship may be marked as viewonly=True.
Tried with viewonly but couldn't make that work either.
I guess/think my problem is that I don't define a ForeignKeyConstraint
for the fk_language_id column but I haven't found how this is done as
the Country_LV view is created using from sqlalchemy.sql import table.
I'd appreciate any tips on how to get this to work.
Werner
--
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.
Re: [sqlalchemy] subqueryload for a ColumnProperty?
For a deferred() itself, we don't have an option that does this.Though this is an unusual request. If you definitely want the deferreds to load, what's the issue having them render inline into the original query ?The advantage to subqueryload is primarily in that it loads multiple rows per parent object efficiently, without re-fetching the full parent row many times, or needing to use a less efficient OUTER JOIN. A deferred is always one row per parent - and no join is needed anyway. On Sep 6, 2011, at 6:49 PM, Sumeet Agarwal wrote: I have a collection of deferred `ColumnProperty`s that I'd like to start loading in subsequent queries. I know you can get this to happen on the first access of a `ColumnProperty` that is deferred, but I wonder if there's any way to specify this in the query. For relationships, `joinedload[_all()]` has a counterpart `subqueryload[_all()]`. Is there nothing similar we can do for `ColumnProperty`? -- 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.
Re: [sqlalchemy] relationship trouble when using UsageRecipes/Views
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for
argument 'foreign_keys'; got: 'Country_LV.fk_language_id', type type 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up). Clearly x.__table__.c.somename is a
Column object, not a string. Something is up with what you're sending it.
I've tried to reproduce with no luck. See attached. Also I don't even need
the primaryjoin/foreignkeys if the originating Table upon which the view is
based has foreign keys to the parent.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import DDLElement
from sqlalchemy.sql import table
from sqlalchemy.ext import compiler
Base= declarative_base()
# first the view stuff as it is on the wiki
class CreateView(DDLElement):
def __init__(self, name, selectable):
self.name = name
self.selectable = selectable
class DropView(DDLElement):
def __init__(self, name):
self.name = name
@compiler.compiles(CreateView)
def compile(element, compiler, **kw):
return CREATE VIEW %s AS %s % (element.name, compiler.sql_compiler.process(element.selectable))
@compiler.compiles(DropView)
def compile(element, compiler, **kw):
return DROP VIEW %s % (element.name)
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
CreateView(name, selectable).execute_at('after-create', metadata)
DropView(name).execute_at('before-drop', metadata)
return t
# now do an example using declartive
Base = declarative_base()
class MoreStuff(Base):
__tablename__ = 'morestuff'
id = Column(Integer, primary_key=True)
# if you use this one, then we don't even need the primaryjoin/foriegn_keys
# on the view, the FK propagates out
# stuff_id = Column(Integer, ForeignKey('stuff.id'))
# ... but lets say it wasn't there
stuff_id = Column(Integer)
data = Column(String(50))
class MSView(Base):
__table__ = view(msview, Base.metadata,
MoreStuff.__table__.select()
)
__mapper_args__ = {primary_key:__table__.c.id}
# cannot reproduce your error.
class Stuff(Base):
__tablename__ = 'stuff'
id = Column(Integer, primary_key=True)
data = Column(String(50))
# works
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.stuff_id])
# works
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=MSView.stuff_id)
# doesn't work, tries to resolve __table__ as a column
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.__table__.c.stuff_id])
# doesn't work, tries to resolve __table__ as a column
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=MSView.__table__.c.stuff_id)
# works
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.stuff_id])
# works
msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.__table__.c.stuff_id])
# all is well
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
e.execute(Stuff.__table__.insert(),
{'data':'apples'},
{'data':'pears'},
{'data':'oranges'},
{'data':'orange julius'},
{'data':'apple jacks'},
)
e.execute(MoreStuff.__table__.insert(),
{'stuff_id':3, 'data':'foobar'},
{'stuff_id':4, 'data':'foobar'}
)
s = Session(e)
print s.query(Stuff).options(eagerload(msview)).all()--
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.
Re: [sqlalchemy] relationship trouble when using UsageRecipes/Views
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument
'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce with
no luck. See attached. Also I don't even need the primaryjoin/foreignkeys if
the originating Table upon which the view is based has foreign keys to the
parent.
The additional column is in my case added in to the view as the base
table doesn't have it and it is filled by the stored procedure (which
combines the base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__
= {primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
Thanks a lot for your answer and your patience.
Werner
--
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.
Re: [sqlalchemy] relationship trouble when using UsageRecipes/Views
On Sep 7, 2011, at 11:53 AM, werner wrote:
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for
argument 'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce
with no luck. See attached. Also I don't even need the
primaryjoin/foreignkeys if the originating Table upon which the view is
based has foreign keys to the parent.
The additional column is in my case added in to the view as the base table
doesn't have it and it is filled by the stored procedure (which combines the
base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__ =
{primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
well that's also strange. If the mapper is not able to find a primary key,
the error is very clear:
sqlalchemy.exc.ArgumentError: Mapper Mapper|MSView|msview could not assemble
any primary key columns for mapped table 'msview'
not sure why that isn't what you were seeing.
--
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] CircularDependencyError, why?
The following gives CircularDependencyError
where I think there isn't, but you know I don't know that much the internals
of SQLAlchemy and maybe (and it is a strong possibility) I'm wrong. I've
ripped of the classes causing the error (copy/paste will re-produce the
error).
from sqlalchemy import orm, create_engine
from sqlalchemy.orm import relationship, synonym, validates
from sqlalchemy import (Table, Column, ForeignKey, Boolean, Integer, String)
from sqlalchemy.ext.declarative import (declarative_base, synonym_for,
declared_attr)
Base = declarative_base()
class SimpleEntity(Base):
__tablename__ = SimpleEntities
id = Column(id, Integer, primary_key=True)
entity_type = Column(db_entity_type, String(128), nullable=False)
__mapper_args__ = {
polymorphic_on: entity_type,
polymorphic_identity: SimpleEntity,
}
class Entity(SimpleEntity):
__tablename__ = Entities
__mapper_args__ = {polymorphic_identity: Entity}
entity_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
primary_key=True)
class Task(Entity):
__tablename__ = Tasks
__mapper_args__ = {polymorphic_identity: Task}
task_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
task_of_id = Column(Integer, ForeignKey(TaskableEntities.id))
class ProjectMixin(object):
#--
@declared_attr
def project_id(cls):
return Column(
project_id,
Integer,
ForeignKey(Projects.id),
)
#--
@declared_attr
def project(cls):
return relationship(
Project,
primaryjoin=\
cls.__name__ + .project_id==Project.project_id_local,
post_update=True, # for project itself
uselist=False
)
#--
def __init__(self,
project=None,
**kwargs):
self.project = project
class TaskableEntity(Entity, ProjectMixin):
__tablename__ = TaskableEntities
__mapper_args__ = {polymorphic_identity: TaskableEntity}
taskableEntity_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
tasks = relationship(
Task,
primaryjoin=taskableEntity_id==Task.task_of_id,
backref=task_of,
post_update=True,
)
class Project(TaskableEntity):
__tablename__ = Projects
project_id_local = Column(id, Integer,
ForeignKey(TaskableEntities.id),
primary_key=True)
__mapper_args__ = {polymorphic_identity: Project,
inherit_condition:
project_id_local==TaskableEntity.taskableEntity_id}
engine = create_engine(sqlite:///:memory:)
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=dengine)
session = Session()
The Error message:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected:
cycles:
set([
Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
Table('Projects', MetaData(None),
Column('id', Integer(), ForeignKey('TaskableEntities.id'),
table=Projects, primary_key=True, nullable=False), schema=None)]
) all edges:
set(
[
(Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
Table('Projects', MetaData(None),
Column('id', Integer(), ForeignKey('TaskableEntities.id'),
table=Projects, primary_key=True, nullable=False), schema=None)),
(Table('SimpleEntities', MetaData(None),
Column('id', Integer(), table=SimpleEntities,
primary_key=True, nullable=False),
Column('db_entity_type', String(length=128),
table=SimpleEntities, nullable=False), schema=None),
Table('Entities', MetaData(None),
Column('id', Integer(), ForeignKey('SimpleEntities.id'),
table=Entities,
Re: [sqlalchemy] CircularDependencyError, why?
On Sep 7, 2011, at 12:36 PM, Erkan Özgür Yılmaz wrote:
The following gives CircularDependencyError
where I think there isn't, but you know I don't know that much the internals
of SQLAlchemy and maybe (and it is a strong possibility) I'm wrong. I've
ripped of the classes causing the error (copy/paste will re-produce the
error).
According to the error message, TaskableEntity has a foreign key to Projects
and Projects has a foreign key to TaskableEntity, thus creating a cycle.
TaskableEntity would appear to have this Project foreign key via the
ProjectMixin class.So I'd add a use_alter=True to one of those ForeignKey
objects.
from sqlalchemy import orm, create_engine
from sqlalchemy.orm import relationship, synonym, validates
from sqlalchemy import (Table, Column, ForeignKey, Boolean, Integer, String)
from sqlalchemy.ext.declarative import (declarative_base, synonym_for,
declared_attr)
Base = declarative_base()
class SimpleEntity(Base):
__tablename__ = SimpleEntities
id = Column(id, Integer, primary_key=True)
entity_type = Column(db_entity_type, String(128), nullable=False)
__mapper_args__ = {
polymorphic_on: entity_type,
polymorphic_identity: SimpleEntity,
}
class Entity(SimpleEntity):
__tablename__ = Entities
__mapper_args__ = {polymorphic_identity: Entity}
entity_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
primary_key=True)
class Task(Entity):
__tablename__ = Tasks
__mapper_args__ = {polymorphic_identity: Task}
task_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
task_of_id = Column(Integer, ForeignKey(TaskableEntities.id))
class ProjectMixin(object):
#--
@declared_attr
def project_id(cls):
return Column(
project_id,
Integer,
ForeignKey(Projects.id),
)
#--
@declared_attr
def project(cls):
return relationship(
Project,
primaryjoin=\
cls.__name__ + .project_id==Project.project_id_local,
post_update=True, # for project itself
uselist=False
)
#--
def __init__(self,
project=None,
**kwargs):
self.project = project
class TaskableEntity(Entity, ProjectMixin):
__tablename__ = TaskableEntities
__mapper_args__ = {polymorphic_identity: TaskableEntity}
taskableEntity_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
tasks = relationship(
Task,
primaryjoin=taskableEntity_id==Task.task_of_id,
backref=task_of,
post_update=True,
)
class Project(TaskableEntity):
__tablename__ = Projects
project_id_local = Column(id, Integer,
ForeignKey(TaskableEntities.id),
primary_key=True)
__mapper_args__ = {polymorphic_identity: Project,
inherit_condition:
project_id_local==TaskableEntity.taskableEntity_id}
engine = create_engine(sqlite:///:memory:)
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=dengine)
session = Session()
The Error message:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected:
cycles:
set([
Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
Table('Projects', MetaData(None),
Column('id', Integer(), ForeignKey('TaskableEntities.id'),
table=Projects, primary_key=True, nullable=False), schema=None)]
) all edges:
set(
[
(Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
Table('Projects', MetaData(None),
Re: [sqlalchemy] CircularDependencyError, why?
Oh, that worked very well, thank you very much...
E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com
On Wed, Sep 7, 2011 at 8:04 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Sep 7, 2011, at 12:36 PM, Erkan Özgür Yılmaz wrote:
The following gives CircularDependencyError
where I think there isn't, but you know I don't know that much the
internals of SQLAlchemy and maybe (and it is a strong possibility) I'm
wrong. I've ripped of the classes causing the error (copy/paste will
re-produce the error).
According to the error message, TaskableEntity has a foreign key to
Projects and Projects has a foreign key to TaskableEntity, thus creating a
cycle. TaskableEntity would appear to have this Project foreign key via the
ProjectMixin class.So I'd add a use_alter=True to one of those
ForeignKey objects.
from sqlalchemy import orm, create_engine
from sqlalchemy.orm import relationship, synonym, validates
from sqlalchemy import (Table, Column, ForeignKey, Boolean, Integer,
String)
from sqlalchemy.ext.declarative import (declarative_base, synonym_for,
declared_attr)
Base = declarative_base()
class SimpleEntity(Base):
__tablename__ = SimpleEntities
id = Column(id, Integer, primary_key=True)
entity_type = Column(db_entity_type, String(128), nullable=False)
__mapper_args__ = {
polymorphic_on: entity_type,
polymorphic_identity: SimpleEntity,
}
class Entity(SimpleEntity):
__tablename__ = Entities
__mapper_args__ = {polymorphic_identity: Entity}
entity_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
primary_key=True)
class Task(Entity):
__tablename__ = Tasks
__mapper_args__ = {polymorphic_identity: Task}
task_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
task_of_id = Column(Integer, ForeignKey(TaskableEntities.id))
class ProjectMixin(object):
#--
@declared_attr
def project_id(cls):
return Column(
project_id,
Integer,
ForeignKey(Projects.id),
)
#--
@declared_attr
def project(cls):
return relationship(
Project,
primaryjoin=\
cls.__name__ + .project_id==Project.project_id_local,
post_update=True, # for project itself
uselist=False
)
#--
def __init__(self,
project=None,
**kwargs):
self.project = project
class TaskableEntity(Entity, ProjectMixin):
__tablename__ = TaskableEntities
__mapper_args__ = {polymorphic_identity: TaskableEntity}
taskableEntity_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
tasks = relationship(
Task,
primaryjoin=taskableEntity_id==Task.task_of_id,
backref=task_of,
post_update=True,
)
class Project(TaskableEntity):
__tablename__ = Projects
project_id_local = Column(id, Integer,
ForeignKey(TaskableEntities.id),
primary_key=True)
__mapper_args__ = {polymorphic_identity: Project,
inherit_condition:
project_id_local==TaskableEntity.taskableEntity_id}
engine = create_engine(sqlite:///:memory:)
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=dengine)
session = Session()
The Error message:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected:
cycles:
set([
Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
Table('Projects', MetaData(None),
Column('id', Integer(), ForeignKey('TaskableEntities.id'),
table=Projects, primary_key=True, nullable=False), schema=None)]
) all edges:
set(
[
(Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
Re: [sqlalchemy] one-to-many with mixins in inherited classes
I forgot to say thank you, I've solved it as you suggested and created a
TaskableEntity for task related entities...
E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com
On Sun, Sep 4, 2011 at 11:35 PM, Michael Bayer mike...@zzzcomputing.comwrote:
TaskMixin.tasks places a relationship named task_of onto the Task class
via the backref directive. Only one relationship of this name can exist on
the parent class, and a relationship is only configurable against a single
class. This single class can be of course a common base for many
subclasses, but in this case you're attempting to create two task_of
relationships, one hardwired to TaskableClassA and the other hardwired to
TaskableClassB.
It seems likely you'd like task_of to link to both classes and load
polymorphically - so TaskableClassA and TaskableClassB should both inherit
from a common base, with no __tablename__, such as TaskableClassBase - that
class then uses TaskMixin to establish behavior common to both.
Of course you don't really need the TaskMixin here since it can only be
used once with that backref on it.
you can try the modified versions attached - the first uses TaskMixin the
second removes it.
--
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.
On Sep 4, 2011, at 3:18 PM, Erkan Özgür Yılmaz wrote:
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship, validates
from sqlalchemy.ext import declarative
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
# The Base Class
class SimpleEntity(Base):
__tablename__ = SimpleEntities
id = Column(id, Integer, primary_key=True)
entity_type = Column(db_entity_type, String(128), nullable=False)
__mapper_args__ = {
polymorphic_on: entity_type,
polymorphic_identity: SimpleEntity,
}
name = Column(String(256), nullable=False)
#--
def __init__(self, name=None, **kwargs):
self.name = name
class Task(SimpleEntity):
__tablename__ = Tasks
task_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
primary_key=True)
__mapper_args__ = {polymorphic_identity: Task,
inherit_condition: task_id==SimpleEntity.id}
task_of_id = Column(Integer,
ForeignKey(SimpleEntities.id)
)
#--
def __init__(self, task_of=None, **kwargs):
super(Task, self).__init__(**kwargs)
self.task_of = task_of
class TaskMixin(object): # The mixin
def __init__(self, tasks=None, **kwargs):
if tasks is None:
tasks = []
self.tasks = tasks
#--
@declared_attr
def tasks(cls):
return relationship(
Task,
primaryjoin=Tasks.c.task_of_id==SimpleEntities.c.id,
backref=task_of,
)
# example class 1 - defining only one class with TaskMixin doesn't create
# any problem
class TaskableClassA(SimpleEntity, TaskMixin):
__tablename__ = TaskableAs
__mapper_args__ = {polymorphic_identity: TaskableClassA}
taskableClass_id = Column(id, Integer,
ForeignKey(SimpleEntities.id),
primary_key=True)
#--
def __init__(self, **kwargs):
super(SimpleEntity, self).__init__(**kwargs)
TaskMixin.__init__(self, **kwargs)
###
# example class 2 - which creates the problem
class TaskableClassB(SimpleEntity, TaskMixin):
__tablename__ = TaskableBs
__mapper_args__ = {polymorphic_identity: TaskableClassB}
taskableClass_id = Column(id, Integer,
ForeignKey(SimpleEntities.id),
primary_key=True)
#--
def __init__(self, **kwargs):
super(SimpleEntity, self).__init__(**kwargs)
TaskMixin.__init__(self, **kwargs)
a_taskable_object = TaskableClassA(name=taskable test object)
task1 = Task(name=Test Task1,
Re: [sqlalchemy] relationship trouble when using UsageRecipes/Views
On 09/07/2011 05:58 PM, Michael Bayer wrote:
On Sep 7, 2011, at 11:53 AM, werner wrote:
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument
'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce with
no luck. See attached. Also I don't even need the primaryjoin/foreignkeys if
the originating Table upon which the view is based has foreign keys to the
parent.
The additional column is in my case added in to the view as the base table
doesn't have it and it is filled by the stored procedure (which combines the
base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__ =
{primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
well that's also strange. If the mapper is not able to find a primary key,
the error is very clear:
sqlalchemy.exc.ArgumentError: Mapper Mapper|MSView|msview could not assemble
any primary key columns for mapped table 'msview'
not sure why that isn't what you were seeing.
Just tried to step back and see what other change I have done which
would explain my problem, but I missed to commit to source control
(didn't bother as it didn't work - should have known better).
Werner
Werner
--
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] order_by on model property
What is the right way to use .order_by() to order by the values returned by a model object property? My model object is like this: class Project(DeclarativeBase): __tablename__ = 'project' id = Column(Integer, primary_key=True) ... @property def remainderDue(self): return self.totalDue - float(self.totalPaid) The query I'm trying to run is: projects = DBSession.query(model.Project).order_by(desc(model.Project.remainderDue)) This returns the following error: Module sqlalchemy.sql.expression:1279 in _literal_as_text ArgumentError: SQL expression object or string expected. Tim -- 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.
Re: [sqlalchemy] order_by on model property
You'd use a hybrid for this case, and due to the usage of float() you'd probably want to produce a separate @expression that doesn't rely on a Python function. Docs and examples for hybrid are at http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html Separate @expression: http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior The float() call in SQL would likely be using CAST, so take a look at http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast for that. On Sep 7, 2011, at 2:27 PM, Tim Black wrote: What is the right way to use .order_by() to order by the values returned by a model object property? My model object is like this: class Project(DeclarativeBase): __tablename__ = 'project' id = Column(Integer, primary_key=True) ... @property def remainderDue(self): return self.totalDue - float(self.totalPaid) The query I'm trying to run is: projects = DBSession.query(model.Project).order_by(desc(model.Project.remainderDue)) This returns the following error: Module sqlalchemy.sql.expression:1279 in _literal_as_text ArgumentError: SQL expression object or string expected. Tim -- 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.
Re: [sqlalchemy] subqueryload for a ColumnProperty?
Still getting the hang of SQLAlchemy terminology. I didn't mean a simple deferred() column, but a deferred column_property() which defines a scalar select(). Like in the example here http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes . My goal is to, instead of including a correlated subquery in the SELECT. The column_property looks like Customer.num_orders = orm.column_property( select([func.count(Order.id)])\ .where(Order.customer_id == Customer.id)\ .correlate(Customer.__table__), deferred=True ) I'd rather do a separate query to load num_orders, rather than getting it lazily or by using undefer(). It almost feels like I might want to define num_orders as a relationship somehow? I dunno if what I would like to do maps cleanly to a SQLAlchemy pattern. On Wed, Sep 7, 2011 at 7:51 AM, Michael Bayer mike...@zzzcomputing.comwrote: For a deferred() itself, we don't have an option that does this.Though this is an unusual request. If you definitely want the deferreds to load, what's the issue having them render inline into the original query ?The advantage to subqueryload is primarily in that it loads multiple rows per parent object efficiently, without re-fetching the full parent row many times, or needing to use a less efficient OUTER JOIN. A deferred is always one row per parent - and no join is needed anyway. On Sep 6, 2011, at 6:49 PM, Sumeet Agarwal wrote: I have a collection of deferred `ColumnProperty`s that I'd like to start loading in subsequent queries. I know you can get this to happen on the first access of a `ColumnProperty` that is deferred, but I wonder if there's any way to specify this in the query. For relationships, `joinedload[_all()]` has a counterpart `subqueryload[_all()]`. Is there nothing similar we can do for `ColumnProperty`? -- 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.
Re: [sqlalchemy] subqueryload for a ColumnProperty?
On Sep 7, 2011, at 5:09 PM, Sumeet Agarwal wrote:
Still getting the hang of SQLAlchemy terminology.
I didn't mean a simple deferred() column, but a deferred column_property()
which defines a scalar select(). Like in the example here
http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes.
My goal is to, instead of including a correlated subquery in the SELECT. The
column_property looks like
My goal is to..., missing a verb there :). If it were me, I'd think
you're asking to unwrap the correlated subquery into a plain JOIN. Since that
works more efficiently.
I'd rather do a separate query to load num_orders, rather than getting it
lazily or by using undefer(). It almost feels like I might want to define
num_orders as a relationship somehow? I dunno if what I would like to do maps
cleanly to a SQLAlchemy pattern.
Using relationship() is kind of the old school approach, but yup I think that
actually allows you to get exactly subqueryload() for an attribute, so good
idea ! a rare moment that SQLAlchemy amazes even me. here's that since its
faster for me to just illustrate
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
orders = relationship(Order)
@property
def num_orders(self):
return self._num_orders.count if self._num_orders else 0
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
customer_id = Column(Integer, ForeignKey('customer.id'))
class OrderCount(object):
pass
order_counts = select([Order.customer_id,
func.count(Order.id).label('count')]).\
group_by(Order.customer_id).alias()
mapper(
OrderCount,
order_counts,
primary_key=order_counts.c.customer_id
)
Customer._num_orders = relationship(OrderCount, uselist=False)
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Customer(orders=[Order(), Order(), Order()]),
Customer(orders=[Order(), Order()]),
Customer(orders=[]),
])
s.commit()
for c in s.query(Customer).options(subqueryload(_num_orders)):
print c.id, c.num_orders
On Wed, Sep 7, 2011 at 7:51 AM, Michael Bayer mike...@zzzcomputing.com
wrote:
For a deferred() itself, we don't have an option that does this.Though
this is an unusual request. If you definitely want the deferreds to load,
what's the issue having them render inline into the original query ?The
advantage to subqueryload is primarily in that it loads multiple rows per
parent object efficiently, without re-fetching the full parent row many
times, or needing to use a less efficient OUTER JOIN. A deferred is always
one row per parent - and no join is needed anyway.
On Sep 6, 2011, at 6:49 PM, Sumeet Agarwal wrote:
I have a collection of deferred `ColumnProperty`s that I'd like to
start loading in subsequent queries. I know you can get this to happen
on the first access of a `ColumnProperty` that is deferred, but I
wonder if there's any way to specify this in the query.
For relationships, `joinedload[_all()]` has a counterpart
`subqueryload[_all()]`. Is there nothing similar we can do for
`ColumnProperty`?
--
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.
Re: [sqlalchemy] Question on session.expunge.all()
Great, thanks! .oO V Oo. On 09/06/2011 04:48 PM, Michael Bayer wrote: On Sep 6, 2011, at 10:40 AM, Vlad K. wrote: I have a products database which is daily syncronized with an external source via a csv file. There are several thousand rows in question. The synchronization does two things: 1. Update only price if changed for existing products 2. Insert new products if they don't exist with all fields from csv But basically, for each row in the csv, after the row is processed (one of the above two things is done), I don't need the object in session anymore. Memory and performance are of course an issue, and I can't find a way to test memory consumption with or without expunge_all() so my questions are: 1. Do I need to session.expunge_all() after each csv row is processed, or are they automatically garbage collected? 2. Is there any significant overhead inherent in expunge_all() that I'm not seeing right now? Performance-wise, it seems the task is complete in more or less same time with or without expunge_all() In modern SQLAlchemy, the Session maintains only weak references to objects that are clean, that is, are persistent in the database and have no pending changes to be flushed.As all references to them are lost, they are garbage collected by the Python interpreter.Note that objects are strongly referenced when they are present in the collection or attribute of a parent object, until that parent is also garbage collected.There is an overhead to process which occurs when the object is dereferenced and removed from the session (weakref callbacks handle the accounting). But calling expunge_all() probably isn't doing much here as the objects are likely being cleaned out in the same way regardless. While I'm at it, I also need to delete rows in the database that do not have corresponding row in the csv file (say linked by csv_key field), the first solution that comes to mind is building a list of keys in the csv file (few thousand keys) and then doing: session.query(Product).filter(not_(Product.product_id.in_(csv_keys))).delete() I believe there is less overhead in sending such a large (but single!) query to the database and leaving it to determine what to delete by itself, than selecting each row in the database and checking if its csv_key exists in the csv_keys list on the application side and then issuing delete statements for rows that matched the criteria. Am I wrong? That's definitely a dramatically faster way to do things, rather than to load each record individually and mark as deleted - it's the primary reason delete() and update() are there. You'll probably want to send False as the value of synchronize_session to the delete() call so that it doesn't go through the effort of locating local records that were affected (unless you need that feature). -- 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] PyODBCConnector, possible wrong assumption re Unicode in bind parameters
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=FreeTDSTDS_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 at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] PyODBCConnector, possible wrong assumption re Unicode in bind parameters
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=FreeTDSTDS_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
Re: [sqlalchemy] PyODBCConnector, possible wrong assumption re Unicode in bind parameters
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=209
http://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=FreeTDSTDS_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
