Re: [sqlalchemy] Use of synonym with declarative_base and map_column=True
>
> Please understand that synonym() is mostly an obsolete feature, that
>
ok, I did not realize this. I'm sorry it wasn't clear that I was misusing a
feature that was meant to not be used. I found it by scanning docs and just
thought it did what I wanted.
again this is very old documentation around a parameter that is
> obsolete within a feature that is itself seldom used, as it has been
> superseded by other approaches. The map_column parameter is also
>
I didn't mean to force usage of synonym. I simply meant to ask how to
accomplish what I wanted with what I thought the available tools were.
Sorry for the confusion.
> The hybrid_property is the modern way to achieve this, however if you
>
Thanks for the pointer, I will explore hybrid_property then.
> The Column must still be mapped to a name and this must be done
> explicitly in some way - the map_column parameter was a shortcut that
> worked with classical mappings, because it would locate the
> table-bound Column of the given name and then automatically map it to
> the stated "name" of the synonym. This cannot work with Declarative
> as implemented because Declarative will fail to map the Column at all
> when the attribute which names it is replaced. This is simple Python:
>
>
> class MyClass(object):
> my_attribute = "one"
>
> my_attribute = "two"
>
>
> Above, MyClass.my_attribute is named "two". The value "one" is gone.
> Declarative uses metaclasses in order to intercept the creation of
> the class, which means it is given a dictionary of names that are
> associated with the class. For a class as above, it will see exactly
> one key/value, that is "my_attribute = 'two'". Declarative will never
> see "one" at all and have no chance to act upon it. So in your
> example:
>
> class MyClass(Base):
> __tablename__ = 'my_table'
> id = Column(Integer, primary_key=True)
> job_status = Column(String(50))
> job_status = synonym("_job_status", map_column=True)
>
> the Column above will never be seen by Declarative and is discarded by
> the Python interpreter immediately.Please use this as evidence (in
> addition to the author of said feature and said documentation telling
> you that the feature never worked this way) that the feature cannot
> even hypothetically work in the way you describe.
>
>
I apologize for the confusion. I believed that this would be possible based
on my cursory understanding of how the python Enum class behaves. But I
didn't realize that it is allowed to work because of additional
functionality provided in python 3. (Enums can detect duplicate members and
throw). This is apparently the source of my confusion which led me down
this rabbit hole in the first place. I did not mean to question your
knowledge of the system, but rather point out that the documentation
combined with my misunderstanding of python 2/3 differences led me to
believe that the functionality would have been intended.
>
>
> > The section of the docs that I linked to
> > seem to explicitly state that it should work that way. Maybe I'm
> > misunderstanding what the docs mean, but the text seems to be clearly
> > indicating that what I'm asking should be possible, and even indicates
> what
> > a typical use would be.
>
> Please note that I am the author of this documentation and the creator
> of this functionality. It is very old and was inaccurately written
> back before Declarative existed.
>
>
I understand that you are the author, and it seems I expressed a message
that I didn't intend. Rather than questioning whether the functionality
actually existed, I was pointing out that the documentation led me here
because I wanted it to work the way I understood the documentation. I
wasn't meaning to say, "You're wrong because the documentation says so".
You also questioned what my intent was, and since the documentation seemed
to express what I wanted, I quoted it. It seems the disconnect was that you
knew this couldn't exist in python 2, and assumed I also knew that until
your recent post. I'll try to be more careful next time. I understand now
that the documentation was old and from before Declarative existed, but
since the sample was in the Declarative style, it wasn't obvious to me
before I started down this path.
Thank you for your patience and help.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
Re: [sqlalchemy] Use of synonym with declarative_base and map_column=True
I'm trying to achieve what it states in the documents that I linked to.
"if True, the synonym()
<http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#sqlalchemy.orm.synonym>
construct will locate the existing named MapperProperty
<http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.interfaces.MapperProperty>
based on the attribute name of this synonym()
<http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#sqlalchemy.orm.synonym>,
and assign it to a new attribute linked to the name of this synonym()
<http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#sqlalchemy.orm.synonym>.
That is, given a mapping like:
"
example code ...
"The above class MyClass will now have the job_status Column
<http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column>
object
mapped to the attribute named _job_status, and the attribute named
job_status will refer to the synonym itself. This feature is typically used
in conjunction with the descriptor argument in order to link a user-defined
descriptor as a “wrapper” for an existing column."
On Wednesday, January 24, 2018 at 9:00:21 PM UTC-8, Mike Bayer wrote:
>
> On Wed, Jan 24, 2018 at 10:17 PM, dykang >
> wrote:
> > Oh. This isn't the same example that I was talking about. From the
> example
> > in the doc, you are allowed to have the column and the synonym have the
> same
> > name, but your example has _job_status for the column and job_status for
> the
> > synonym. when I do it your way, inspect shows _job_status as an attr
> >
> >
> >>>> inspect(mc).attrs.keys()
> > ['job_status', '_job_status', 'id']
> >
> > The way listed in the docs had me believing that you could change these
> > lines from your example:
> > _job_status = Column("job_status", String(50))
> > job_status = synonym("_job_status")
> >
> > to
> >
> > job_status = Column("job_status", String(50))
> > job_status = synonym("_job_status")
> >
> >
> > no leading underscore. When doing it this way, the job_status column is
> > never detected or created. I just realized that the example I'm
> following is
> > literally the section for "map_column", so does that mean that
> functionality
> > isn't supported anymore?
>
> It has never worked that way, and I don't understand what that would
> do. There's a column, "job_status", and your class has a "job_status"
> attribute mapped to it, and you're done. What does the synonym
> accomplish ?
In my case I'm trying to attach an accessor to the job_status that changes
the gets/sets. The synonym allows me to create a getter/setter without the
need to expose that both exist. The section of the docs that I linked to
seem to explicitly state that it should work that way. Maybe I'm
misunderstanding what the docs mean, but the text seems to be clearly
indicating that what I'm asking should be possible, and even indicates what
a typical use would be.
> What does "job_status column is never created" mean, you
> don't actually want that column in the database?
>
>
I mean that if I try to follow the example in the documentation and allow
both the column and the synonym to have the same name, the table object
will not have a job_status column in it's mapper.
see this example/output
from sqlalchemy.ext import declarative
from sqlalchemy import *
from sqlalchemy.orm import *
from db import dbbase
Base = declarative.declarative_base()
class MyClass(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
job_status = Column(String(50), default = '')
job_status = synonym("_job_status")
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
2018-01-25 02:58:50,028 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-01-25 02:58:50,028 INFO sqlalchemy.engine.base.Engine ()
2018-01-25 02:58:50,029 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-01-25 02:58:50,029 INFO sqlalchemy.engine.base.Engine ()
2018-01-25 02:58:50,030 INFO sqlalchemy.engine.base.Engine PRAGMA
table_info("my_table")
2018-01-25 02:58:50,030 INFO sqlalchemy.engine.base.Engine ()
2018-01-25 02:58:50,031 INFO sqlalchemy.engine.base.Engine
CREATE TABLE my_table (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
--
No job_status column was created when the table was cr
Re: [sqlalchemy] Use of synonym with declarative_base and map_column=True
Oh. This isn't the same example that I was talking about. From the example
in the doc, you are allowed to have the column and the synonym have the
same name, but your example has _job_status for the column and job_status
for the synonym. when I do it your way, inspect shows _job_status as an attr
>>> inspect(mc).attrs.keys()
['job_status', '_job_status', 'id']
The way listed in the docs had me believing that you could change these
lines from your example:
_job_status = Column("job_status", String(50))
job_status = synonym("_job_status")
to
job_status = Column("job_status", String(50))
job_status = synonym("_job_status")
no leading underscore. When doing it this way, the job_status column is
never detected or created. I just realized that the example I'm following
is literally the section for "map_column", so does that mean that
functionality isn't supported anymore?
here's the link to the section I'm speaking about specifically:
http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#sqlalchemy.orm.synonym.params.map_column
The example is what I was copying because the description of what it does
is what I was hoping to achieve.
On Wednesday, January 24, 2018 at 5:45:51 PM UTC-8, Mike Bayer wrote:
> On Wed, Jan 24, 2018 at 7:25 PM, dykang >
> wrote:
> > Thank you for the tip, but If I remove map_column=True from the example,
> > then inspect returns the correct listing, but when I load an object from
> the
> > db, neither job_status nor _job_status are accessible on the object. It
> > seems to work on assignment, but not on load from db. Is there something
> > more I have to do?
>
> here is a complete example:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class MyClass(Base):
> __tablename__ = 'my_table'
> id = Column(Integer, primary_key=True)
> _job_status = Column("job_status", String(50))
> job_status = synonym("_job_status")
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
> mc = MyClass(job_status="some status")
> s.add(mc)
> s.commit()
> s.close()
>
> mc = s.query(MyClass).first()
> assert mc.job_status == "some status"
>
>
>
>
> >
> >>>> a = session.query(myclass.MyClass).get(1)
> >>>> a
> >
> >>>> a.id
> > 1
> >>>> a.__mapper__.columns
> > 0x7fa91f92c0c0>
> >>>> a.__mapper__.columns.keys()
> > ['id']
> >>>> a.job_status
> > Traceback (most recent call last):
> > File "", line 1, in
> > File
> >
> "virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
>
>
> > line 298, in __get__
> > return self.descriptor.__get__(instance, owner)
> > File
> >
> "virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
>
>
> > line 58, in fget
> > return getattr(obj, self.name)
> > AttributeError: 'MyClass' object has no attribute '_job_status'
> >
> >
> > On Wednesday, January 24, 2018 at 2:33:39 PM UTC-8, Mike Bayer wrote:
> >>
> >> remove map_column=True, will update the docs now.
> >>
> >> On Wed, Jan 24, 2018 at 5:01 PM, dykang wrote:
> >> > I was trying to use a synonym to map a column through a setter/getter
> >> > (with
> >> > use of descriptor kwarg). I'm not sure if I'm doing this right, but I
> >> > tried
> >> > to copy/paste the example out of the documentation, and it didn't
> work.
> >> > Could someone point out what I'm doing wrong or point me at a better
> way
> >> > to
> >> > do what I want? I've tried with 1.1.12 and 1.2.1 with python 2.7.
> >> >
> >> > this is the entirety of what I'm trying to do:
> >> > --
> >> >
> >> > from sqlalchemy.ext import declarative
> >> > Base = declarative.declarative_base()
> >> > from sqlalchemy import *
> >> > from sqlalchemy.orm import synonym
> >> >
> >> > class MyClass(Base):
> >> > __tablename__ = 'my_table'
> >> >
Re: [sqlalchemy] Use of synonym with declarative_base and map_column=True
Thank you for the tip, but If I remove map_column=True from the example,
then inspect returns the correct listing, but when I load an object from
the db, neither job_status nor _job_status are accessible on the object. It
seems to work on assignment, but not on load from db. Is there something
more I have to do?
>>> a = session.query(myclass.MyClass).get(1)
>>> a
>>> a.id
1
>>> a.__mapper__.columns
>>> a.__mapper__.columns.keys()
['id']
>>> a.job_status
Traceback (most recent call last):
File "", line 1, in
File
"virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
line 298, in __get__
return self.descriptor.__get__(instance, owner)
File
"virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
line 58, in fget
return getattr(obj, self.name)
AttributeError: 'MyClass' object has no attribute '_job_status'
On Wednesday, January 24, 2018 at 2:33:39 PM UTC-8, Mike Bayer wrote:
>
> remove map_column=True, will update the docs now.
>
> On Wed, Jan 24, 2018 at 5:01 PM, dykang >
> wrote:
> > I was trying to use a synonym to map a column through a setter/getter
> (with
> > use of descriptor kwarg). I'm not sure if I'm doing this right, but I
> tried
> > to copy/paste the example out of the documentation, and it didn't work.
> > Could someone point out what I'm doing wrong or point me at a better way
> to
> > do what I want? I've tried with 1.1.12 and 1.2.1 with python 2.7.
> >
> > this is the entirety of what I'm trying to do:
> > --
> >
> > from sqlalchemy.ext import declarative
> > Base = declarative.declarative_base()
> > from sqlalchemy import *
> > from sqlalchemy.orm import synonym
> >
> > class MyClass(Base):
> > __tablename__ = 'my_table'
> > id = Column(Integer, primary_key=True)
> > job_status = Column(String(50))
> > job_status = synonym("_job_status", map_column=True)
> >
> > --
> >
> > This fails because the synonym won't compile. It appears that the
> job_status
> > Column value has been overridden by the synonym before it's had a chance
> to
> > create the synonym to the original. It seems I can get around this by
> not
> > using the map_column value and just renaming the column to _job_status,
> but
> > I was hoping that using sqlalchemy.inspect(row_instance).attrs would
> only
> > return job_status, and not _job_status (I'm not even sure if this is
> true,
> > it was just a guess on how it might work and what I wanted to happen)
> >
> > Traceback (most recent call last):
> > File "", line 1, in
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py",
>
>
> > line 64, in __init__
> > _as_declarative(cls, classname, cls.__dict__)
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
>
>
> > line 88, in _as_declarative
> > _MapperConfig.setup_mapping(cls, classname, dict_)
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
>
>
> > line 116, in setup_mapping
> > cfg_cls(cls_, classname, dict_)
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
>
>
> > line 148, in __init__
> > self._early_mapping()
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
>
>
> > line 151, in _early_mapping
> > self.map()
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
>
>
> > line 576, in map
> > **self.mapper_args
> > File "", line 2, in mapper
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
>
>
> > line 692, in __init__
> > self._configure_properties()
> > File
> >
> "/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
>
>
> > line 1383, in _configure_properties
> > self._confi
[sqlalchemy] Use of synonym with declarative_base and map_column=True
I was trying to use a synonym to map a column through a setter/getter (with
use of descriptor kwarg). I'm not sure if I'm doing this right, but I tried
to copy/paste the example out of the documentation, and it didn't work.
Could someone point out what I'm doing wrong or point me at a better way to
do what I want? I've tried with 1.1.12 and 1.2.1 with python 2.7.
this is the entirety of what I'm trying to do:
--
from sqlalchemy.ext import declarative
Base = declarative.declarative_base()
from sqlalchemy import *
from sqlalchemy.orm import synonym
class MyClass(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
job_status = Column(String(50))
job_status = synonym("_job_status", map_column=True)
--
This fails because the synonym won't compile. It appears that the
job_status Column value has been overridden by the synonym before it's had
a chance to create the synonym to the original. It seems I can get around
this by not using the map_column value and just renaming the column to
_job_status, but I was hoping that using
sqlalchemy.inspect(row_instance).attrs would only return job_status, and
not _job_status (I'm not even sure if this is true, it was just a guess on
how it might work and what I wanted to happen)
Traceback (most recent call last):
File "", line 1, in
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py",
line 64, in __init__
_as_declarative(cls, classname, cls.__dict__)
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
line 88, in _as_declarative
_MapperConfig.setup_mapping(cls, classname, dict_)
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
line 116, in setup_mapping
cfg_cls(cls_, classname, dict_)
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
line 148, in __init__
self._early_mapping()
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
line 151, in _early_mapping
self.map()
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
line 576, in map
**self.mapper_args
File "", line 2, in mapper
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
line 692, in __init__
self._configure_properties()
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
line 1383, in _configure_properties
self._configure_property(key, prop, False)
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py",
line 1677, in _configure_property
prop.set_parent(self, init)
File
"/virtualenv/workspace/local/lib/python2.7/site-packages/sqlalchemy/orm/descriptor_props.py",
line 626, in set_parent
% (self.name, parent.mapped_table.description, self.key))
sqlalchemy.exc.ArgumentError: Can't compile synonym '_job_status': no
column on table 'my_table' named 'job_status'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: migrating from 0.4 to 0.5
Thanks for your response. Would it be unreasonable to request a global override for this? Perhaps a sessionmaker option? It feels like it is not totally unreasonable, and it helps me prevent a monkey patch or having to type "subtransactions=True" every time I call begin. Obviously not a huge problem, but just something I'd appreciate. On Apr 18, 7:59 am, Michael Bayer wrote: > On Apr 17, 2009, at 7:36 PM, dykang wrote: > > > > > Hi, > > > I was trying to migrate from 0.4 to 0.5, and I noticed a behavior in > > 0.5 that wasn't listed in the migration document that I'd like to > > disable. It appears that in 0.5, begin no longer allows for > > subtransactions by default, and that there is no global flag to turn > > this behavior on. Is there a recommended strategy for this other than > > search/replace? > > > Also, since subtransactions were explicity disabled by default now, is > > there some danger to them that I should be aware of when using them? > > the flag was added so that people understand that the begin() they are > issuing is not a real BEGIN. they are forced to understand what a > "subtransaction" is. > > as is typical in python, the two strategies to migrate are search and > replace, or just a simple monkeypatch on the Session class to get you > through the day. --~--~-~--~~~---~--~~ 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] migrating from 0.4 to 0.5
Hi, I was trying to migrate from 0.4 to 0.5, and I noticed a behavior in 0.5 that wasn't listed in the migration document that I'd like to disable. It appears that in 0.5, begin no longer allows for subtransactions by default, and that there is no global flag to turn this behavior on. Is there a recommended strategy for this other than search/replace? Also, since subtransactions were explicity disabled by default now, is there some danger to them that I should be aware of when using them? --~--~-~--~~~---~--~~ 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: How can I get mapped data from an aliased subselect?
thanks again for your help. I was curious though, I don't really understand what select_from is doing, and why it is able to map this data. I couldn't really find good explanations in the documentation about this. is there a good place for me to look to understand what this is doing? My understanding is that it replaces the selectable for the query, but I don't understand why this would change the mapping properties of this query. On Apr 16, 1:04 pm, Michael Bayer wrote: > the sophisticated column correlation you're looking for will only work > if you use query.select_from(s), in which case there's no point in > creating the final subquery, just call select_from(j) to prevent an > unnecessary layer of nesting. It also seems like your ultimate query > is better expressed just as a join of three tables instead of a join > to a subquery. this is true because this was a contrived example used to simplify the query in such a way that I could provide a (hopefully) easy to understand example that duplicated my problem. > > Otherwise, to use from_statement() you'd have to construct your select > statement such that the names of all columns match exactly what the > query would look for normally - i.e. "outside_id" for "test_table_id" > since you're aliasing on "outside", etc. from_statement() is a more > brute force approach in that way. this is generally what I try to do, and have gotten relatively good at. however this particular query with the nested aliases was failing. Do you have any advice or tips for how to get this to work? I got this particular example working with select_from, but I'd still be very interested in knowing how I could have accomplished my goal using from_statement with this example. --~--~-~--~~~---~--~~ 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: How can I get mapped data from an aliased subselect?
oh, haha, sorry. i've been staring at this too long. the real error (the one that I do get when I fix the example) is sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'inside.id'" they looked very similar, and i didn't look close enough. sorry On Apr 16, 12:27 pm, Michael Bayer wrote: > the error youre showing me, which is "sqlalchemy.exc.OperationalError: > (OperationalError) (1054, "Unknown column 'inside.foo' in 'on > clause'") ", has nothing to do with mapping. the SQL is not being > understood by the database. from_statement() results in the SQL you > pass being executed verbatim, and the issue with the statement is what > I said earlier. > > On Apr 16, 2009, at 3:24 PM, dykang wrote: > > > > >> So above, you're joining "table2" to "s". the ON clause must be in > >> terms of "table2" and "s", not "outside", which is meaningless in > >> that > >> context. > > > You are correct, that was a mistake in my example, but does not change > > my mapping error. > > The error was not that the query it was creating was incorrect, but > > that I was unable to map the results > > back into my mapped classes. --~--~-~--~~~---~--~~ 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: How can I get mapped data from an aliased subselect?
> So above, you're joining "table2" to "s". the ON clause must be in > terms of "table2" and "s", not "outside", which is meaningless in that > context. You are correct, that was a mistake in my example, but does not change my mapping error. The error was not that the query it was creating was incorrect, but that I was unable to map the results back into my mapped classes. --~--~-~--~~~---~--~~ 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] How can I get mapped data from an aliased subselect?
The following example is very contrived, but it's a very simplified
version of what I am actually trying to do.
Basically I'm trying to join with a derived table, that itself is a
self join.
Can someone help me figure out what I am doing wrong with this
example?
Thanks
#create meta
#create session
import sqlalchemy
table = sqlalchemy.Table (
'test_table', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('foo', sqlalchemy.Integer),
mysql_engine='InnoDB'
)
table2 = sqlalchemy.Table (
'test_table_2', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('bar', sqlalchemy.Integer),
mysql_engine='InnoDB'
)
class Test(object):
pass
class Test2(object):
pass
mapper =sqlalchemy.orm.mapper(Test, table)
mapper =sqlalchemy.orm.mapper(Test2, table2)
meta.create_all()
for i in range(3):
row1 = Test()
row1.foo = i
session.add(row1)
row2 = Test2()
row2.bar = i
session.add(row2)
session.flush()
inside = table.alias('inside')
outside = inside.join(table, table.c.foo < 2)
s = outside.select(use_labels=True).alias('outside')
j = table2.join(s, table2.c.bar == outside.c.inside_foo)
s = j.select(use_labels=True)
rows = session.query(Test2).add_entity(Test, alias=inside).add_entity
(Test, alias=outside).from_statement(s).all()
for row in rows:
print row
At this point, i get this exception:
sqlalchemy.exc.OperationalError: (OperationalError) (1054, "Unknown
column 'inside.foo' in 'on clause'") u'SELECT test_table_2.id AS
test_table_2_id, test_table_2.bar AS test_table_2_bar,
outside.inside_id AS outside_inside_id, outside.inside_foo AS
outside_inside_foo, outside.test_table_id AS outside_test_table_id,
outside.test_table_foo AS outside_test_table_foo \nFROM test_table_2
INNER JOIN (SELECT inside.id AS inside_id, inside.foo AS inside_foo,
test_table.id AS test_table_id, test_table.foo AS test_table_foo
\nFROM test_table AS inside INNER JOIN test_table ON test_table.foo <
%s) AS outside ON test_table_2.bar = inside.foo' [2]
I believe that it has to do with the fact that I have two aliases,
and that the query is selecting outside.inside_foo as
outside_inside_foo (a chained alias), but that the mapper is not aware
of the chaining. Is there a way to accomplish what I want to do here?
Thanks,
David
--~--~-~--~~~---~--~~
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: using for_update returns stale data if old row already exists in identity_map
wow, thanks for the replies. I really appreciate the quick responses. I've been "using" sqlalchemy for a few years now, and I have been very impressed by the turnaround on questions and bugs when dealing with this group. Again, thank you. I have just a few more (inline) comments below On Apr 15, 10:32 pm, Michael Bayer wrote: > On Apr 15, 2009, at 8:57 PM, dykang wrote: > > > > > ah, but since the ORM actually is forced to execute the query anyway, > > why not update the object in the identity map with the correct data, > > and raise an exception if the current object is dirty? It seems bad > > procedure > > to be loading an object for update when it's already been modified. > > I tried an experiment with postgres using serializable isolation, and > as it turns out if you've already read the row into the current > transaction, then a concurrent transaction modifies the row, then you > request select...for update of that same row a second time in the > first transaction, it throws a concurrent modification error. so > with serializable, the primary isolation mode we have in mind, the use > case never comes up. > indeed, humorously enough, I had never really considered the now obvious fact and serializable would be the primary isolation mode in mind for sqlalchemy. > if you're in read committed, it re-reads the latest data from the row > from the outside regardless of the usage of FOR UPDATE or not. the PG > docs don't say anything about FOR UPDATE changing the isolation > characteristics of the SELECT...only the locking (and as you can tell > we're using PG, not MySQL, as the baseline for "best" behavior). So > to really work with non-serializable isolation and have the ORM return > data similar to what the database does, data should always be > refreshed with every SELECT, not just those with FOR UPDATE. the This is true. And for some reason I took for granted that this is how it would actually work when I read that the identity map was not a cache. It makes sense that it does not work this way by default, especially given your isolation target. > autoflush feature, which is generally turned on, prevents the issue of > any pending data being overwritten - its always flushed out before the > SELECT occurs. > > This is a lot simpler to implement, that of "populate_existing" on at > all times, and would possibly be a flag that folks could use if they > decided they are expliclty using non-serializable isolation, would > like to have the details of that behavior available to them (i.e. they > really want the same row to change its value throughout the > transaction), and they're willing to take the performance penalty of > re-populating all attributes every time. > > my reasons for not enabling this by default are that its a performance > hit and would be disastrous to use without autoflush. If it were to > work in theory without autoflush, it would have to verify attributes > as having no pending changes before populating, else raise an error Are objects not marked as dirty on changes? Do you check each attribute of each object in the session on every flush? If this process has poor performance, doesn't autoflush suffer the same consequences? > The performance and complexity overhead of that would be infeasable, > not to mention that it's solving a problem that is better solved by > choosing a stricter isolation level. Maybe I'm confused, but I'm pretty confident that there are valid reasons to allow for not choosing a stricter isolation level. It may be the case that I have to accept that I'm using a system that is not the SQLA target, but I don't believe a stricter isolation level is always a good thing. In fact the postgresql documentation even states: "Since the cost of redoing complex transactions may be significant, this mode is recommended only when updating transactions contain logic sufficiently complex that they may give wrong answers in Read Committed mode." > Keeping the feature specific to > just FOR UPDATE doesn't seem to address the full need of "i want to > work in non-serializable isolation", since any SELECT returns fresh I agree completely. > data. Maybe the FOR UPDATE case more strongly suggests the feature > than the non FOR UPDATE case, but I can't make that decision across Again, this makes a lot of sense. But my experience has always been that this IS the case. It is completely foreign to me that a load FOR UPDATE would even possibly return stale data, and I was completely shocked when I discovered the behavior. However, my experience has been limited to oracle read committed and mysql (repeatable rea
[sqlalchemy] Re: using for_update returns stale data if old row already exists in identity_map
On Apr 15, 5:37 pm, Michael Bayer wrote:
> On Apr 15, 2009, at 8:13 PM, dykang wrote:
>
>
>
>
>
> > The following code is a simple reproduction of the issue.
>
> > # create your metadata and session
> > import sqlalchemy
>
> > table = sqlalchemy.Table (
> > 'test_table', meta,
> > sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
> > sqlalchemy.Column('foo', sqlalchemy.Integer),
> > mysql_engine='InnoDB'
> > )
>
> > class Bar(object):
> > pass
>
> > mapper =sqlalchemy.orm.mapper(Bar, table)
> > meta.create_all()
>
> > row1 = Bar()
> > row1.foo = 1
> > session.begin()
> > session.save(row1)
> > session.flush()
> > s = table.update(table.c.foo == 1, values={table.c.foo: 2})
> > session.connection(Bar).execute(s)
> > session.commit()
>
> > s = table.select(id == 1, for_update=True)
> > row2 = session.query(Bar).from_statement(s).all()[0]
> > print row2.foo
>
> I cant reproduce that. The session expires all data after commit().
> How are you creating your session ?
>
>
>
> > I am using sqlalchemy 0.4.7 and have tried it in 0.4.8 as well. I
> > have not tried updating to 0.5.x yet, as I have some code that I'm not
> > ready to migrate yet. Am I doing something wrong, or do I have a bad
> > expectation? when using for_update, I do not expect to ever get stale
> > data.
>
> that explains that. 0.4 doesn't include expire-on-commit, so you
> should upgrade. Otherwise, call session.expire_all() if you're stuck
> with 0.4 to see the results of your non-ORM UPDATE statements within
> your current session. the usage of for_update has no effect on this
> behavior, it only determines if SQLA sends out a FOR UPDATE within the
> SELECT, thereby placing a read/write lock on the row.
since I am using isolation level "read committed" this actually
happens even if the update
is done in a different process. it's obviously less likely to happen,
but it can.
you can reproduce it by
changing bottom part to this and following the instructions in the
comments
row1 = Bar()
row1.foo = 1
session.begin()
session.save(row1)
session.flush()
#s = table.update(table.c.foo == 1, values={table.c.foo: 2})
#session.connection(Bar).execute(s)
session.commit()
import pdb;pdb.set_trace()
# in another window, enter mysql and run "update test_table set
foo=2"; commit;
# then in this window, type "continue"
s = table.select(id == 1, for_update=True)
row2 = session.query(Bar).from_statement(s).all()[0]
print row2.foo
>
> > In fact, since I am using transaction isolation level "read
> > committed" even without the for_update, I would want to get the
> > correct, updated value. Since the identity_map is NOT a cache, and the
> > select statement is always executed, I would expect the updated values
> > to always be returned.
>
> the ORM is always going to use the values in the identity map within
> the session, and it is a cache in some ways. Its a cache of data as
> it exists within the current transaction, since in the typical
> isolated transaction, there's no need to reload data until the
> transaction is complete.
in a situation where you load for update, you should always get up to
date data.
i believe that even in a repeatable read scenario, if I executed the
query with a locking read,
it would return me the most up to date data, and not a repeated read.
>
> In this sense, your usage of a non-isolated transaction mode (and the
> expectation of receiving rows that way) is somewhat incompatible with
> SQLAlchemy's notion of a unit of work. However, there's a "reload
> fresh data every time" mode if you use the populate_existing() method
> on Query, which is provided for those few folks who want to work that
> way (not many folks want to work this way...I can't see what
> advantages there are to working non-isolated other than evading
> deadlocks). you can add a method to Session which just returns a
> Query object with populate_existing() already set, if you like.
I was, indeed, avoiding deadlocks. I tend to try to keep the time that
I hold a
lock as short as possible. This usually means not locking a row until
I know,
for sure, that I am going to update it.
>
> The reason you wouldn't want to use populate_existing every time is
> because it will blow away any changes pending on your attributes,
> which is why its never a default behavior. but if you're trying
> to do a total read-only thing, it might fit the bill.
ah, but since the ORM actual
[sqlalchemy] using for_update returns stale data if old row already exists in identity_map
The following code is a simple reproduction of the issue.
# create your metadata and session
import sqlalchemy
table = sqlalchemy.Table (
'test_table', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('foo', sqlalchemy.Integer),
mysql_engine='InnoDB'
)
class Bar(object):
pass
mapper =sqlalchemy.orm.mapper(Bar, table)
meta.create_all()
row1 = Bar()
row1.foo = 1
session.begin()
session.save(row1)
session.flush()
s = table.update(table.c.foo == 1, values={table.c.foo: 2})
session.connection(Bar).execute(s)
session.commit()
s = table.select(id == 1, for_update=True)
row2 = session.query(Bar).from_statement(s).all()[0]
print row2.foo
It also ends up being an issue if you use read committed
transaction isolation level and the update happens in another session
(but that's harder to supply as a super simple script)
I am using sqlalchemy 0.4.7 and have tried it in 0.4.8 as well. I
have not tried updating to 0.5.x yet, as I have some code that I'm not
ready to migrate yet. Am I doing something wrong, or do I have a bad
expectation? when using for_update, I do not expect to ever get stale
data.
In fact, since I am using transaction isolation level "read
committed" even without the for_update, I would want to get the
correct, updated value. Since the identity_map is NOT a cache, and the
select statement is always executed, I would expect the updated values
to always be returned.
--~--~-~--~~~---~--~~
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: SQL error for mapped select (new in version 0.4) on PostgreSQL
Sorry, I didn't clarify, I was speaking about with 0.3, not with 0.4. On Dec 14, 11:25 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Dec 14, 2007, at 1:34 PM, dykang wrote: > > > > > > > This example can't work in this sequence, or this query can't be run > > and mapped? > > If I wanted to execute this query: > > SELECT inner_query.test_id AS inner_query_test_id, > > inner_query.test_other_id AS inner_query_test_other_id, > > inner_query.test_active AS inner_query_test_active, > > inner_query.test2_id AS inner_query_test2_id, > > inner_query.test2_other_id AS inner_query_test2_other_id, > > inner_query.test2_active AS inner_query_test2_active > > FROM (SELECT test.id AS test_id, test.other_id AS test_other_id, > > test.active AS test_active, test2.id AS test2_id, test2.other_id AS > > test2_other_id, test2.active AS test2_active > > FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query > > > is my only option to do this query through text and build out my own > >column_labelsdictionary? > > im not seeing how you have that impression? the constructed "s2" > query in your example produces that exact SQL. as I said, you only > need to inform the Query about the "s2" alias, using the > contains_alias() option for the primary mapper and add_entity() for > additional mappers, so that it can translate incoming columns for the > mappers. > > print > session > .query > (Test > ).options > (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute()) > print > session > .query > (Test > ).from_statement > (s2).options(contains_alias(s2)).add_entity(Test2,alias=s2).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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
This example can't work in this sequence, or this query can't be run
and mapped?
If I wanted to execute this query:
SELECT inner_query.test_id AS inner_query_test_id,
inner_query.test_other_id AS inner_query_test_other_id,
inner_query.test_active AS inner_query_test_active,
inner_query.test2_id AS inner_query_test2_id,
inner_query.test2_other_id AS inner_query_test2_other_id,
inner_query.test2_active AS inner_query_test2_active
FROM (SELECT test.id AS test_id, test.other_id AS test_other_id,
test.active AS test_active, test2.id AS test2_id, test2.other_id AS
test2_other_id, test2.active AS test2_active
FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query
is my only option to do this query through text and build out my own
column_labels dictionary?
On Dec 14, 8:06 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> this example as is cant really work in any version because the query
> doesnt know to map the "s2" alias to the mappers that its using, so
> you have to explicitly connect them. 0.3 has more limited capability
> to do this.
>
> anyway in 0.4 you can do it like this:
>
> print
> session
> .query
> (Test
> ).options
> (contains_alias(s2)).add_entity(Test2,alias=s2).instances(s2.execute())
>
> and if youre on trunk also like this:
>
> print session.query(Test).select_from(s2).add_entity(Test2,
> alias=s2).all()
>
> On Dec 14, 2007, at 3:30 AM, dykang wrote:
>
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
>
> > metadata = MetaData("mysql://mrlabs:[EMAIL PROTECTED]:3306/test")
> > metadata.bind.echo=True
>
> > table = Table("test", metadata,
> >Column("id", Integer, primary_key=True),
> >Column("other_id", Integer),
> >Column("active", Boolean))
>
> > table2 = Table("test2", metadata,
> >Column("id", Integer, primary_key=True),
> >Column("other_id", Integer),
> >Column("active", Boolean))
> > table.create()
> > table.insert().execute([{"other_id":1, "active": False}, {"other_id":
> > 2, "active": True}])
>
> > table2.create()
> > table2.insert().execute([{"other_id":1, "active": False}, {"other_id":
> > 2, "active": True}])
>
> > test = table.select(table.c.active).alias("test")
> > class Test(object):
> >pass
>
> > class Test2(object):
> >pass
>
> > mapper(Test, table)
> > mapper(Test2, table2)
>
> > session = create_session()
> > q = join(table, table2, table2.c.other_id == table.c.id)
> > s = q.select(use_labels=True).alias("inner_query")
> > s2 = s.select(use_labels=True).alias("outer_query")
>
> > print session.query(Test,
> > Test2).instances(s2.execute(use_labels=True))
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL error for mapped select (new in version 0.4) on PostgreSQL
Hi, I was looking at this bug fix, and I couldn't figure out where
part 2 of the problem was actually fixed.
If you can ignore the absurdity of this example, it shows a situation
where the second problem still appears to
exist:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData("mysql://mrlabs:[EMAIL PROTECTED]:3306/test")
metadata.bind.echo=True
table = Table("test", metadata,
Column("id", Integer, primary_key=True),
Column("other_id", Integer),
Column("active", Boolean))
table2 = Table("test2", metadata,
Column("id", Integer, primary_key=True),
Column("other_id", Integer),
Column("active", Boolean))
table.create()
table.insert().execute([{"other_id":1, "active": False}, {"other_id":
2, "active": True}])
table2.create()
table2.insert().execute([{"other_id":1, "active": False}, {"other_id":
2, "active": True}])
test = table.select(table.c.active).alias("test")
class Test(object):
pass
class Test2(object):
pass
mapper(Test, table)
mapper(Test2, table2)
session = create_session()
q = join(table, table2, table2.c.other_id == table.c.id)
s = q.select(use_labels=True).alias("inner_query")
s2 = s.select(use_labels=True).alias("outer_query")
print session.query(Test,
Test2).instances(s2.execute(use_labels=True))
for which I get the output:
2007-12-14 00:27:59,637 INFO sqlalchemy.engine.base.Engine.0x..30
SELECT inner_query.test_id AS inner_query_test_id,
inner_query.test_other_id AS inner_query_test_other_id,
inner_query.test_active AS inner_query_test_active,
inner_query.test2_id AS inner_query_test2_id,
inner_query.test2_other_id AS inner_query_test2_other_id,
inner_query.test2_active AS inner_query_test2_active
FROM (SELECT test.id AS test_id, test.other_id AS test_other_id,
test.active AS test_active, test2.id AS test2_id, test2.other_id AS
test2_other_id, test2.active AS test2_active
FROM test JOIN test2 ON test2.other_id = test.id) AS inner_query
2007-12-14 00:27:59,637 INFO sqlalchemy.engine.base.Engine.0x..30 []
Traceback (most recent call last):
File "test2.py", line 41, in ?
print session.query(Test,
Test2).instances(s2.execute(use_labels=True))
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/orm/query.py", line 1047, in instances
self.select_mapper._instance(context, row, result)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/orm/mapper.py", line 1443, in _instance
identitykey = self.identity_key_from_row(row)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/orm/mapper.py", line 950, in
identity_key_from_row
return (self.class_, tuple([row[column] for column in
self.pks_by_table[self.mapped_table]]), self.entity_name)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/engine/base.py", line 1172, in __getitem__
return self.__parent._get_col(self.__row, key)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/engine/base.py", line 993, in _get_col
rec = self._convert_key(key)
File "/Users/david/working/env_dandd/lib/python2.4/SQLAlchemy-0.3.11-
py2.4.egg/sqlalchemy/engine/base.py", line 930, in _convert_key
raise exceptions.NoSuchColumnError("Could not locate column in row
for column '%s'" % (str(key)))
sqlalchemy.exceptions.NoSuchColumnError: "Could not locate column in
row for column 'test.id'"
This is from a checkout of the rel_0_3 branch rev 3936.
Thanks,
David
On Oct 17, 7:07 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Oct 17, 2007, at 4:07 AM, klaus wrote:
>
>
>
> > Thanks a lot! The solution is so simple that I feel a little
> > embarassed...
>
> im embarrased that bug's been present for so long !
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] question about unicode usage.
I have a question about inconsistency in unicode handling when using a
bindparam explicitly and when using a literal when constructing my
query. It appears that if I use a unicode object in the actual query
whereclause, the convert_bind_param function of the base String will
get called(query1). However, if I use a bindparam in the whereclause
and then pass in the unicode object as the bindparam,
convert_bind_param is not called(query2).
The code below demonstrates what I am talking about. Is there a reason
that these two ways of constructing a query should have inconsistent
behavior? If I am missing something, I would appreciate any info that
would make it clear to me as to why this is the expected behavior.
Thanks,
DY
import sqlalchemy
meta = sqlalchemy.DynamicMetaData()
meta.connect ('mysql://some_dsn')
test = sqlalchemy.Table('my_test_table', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column ('name', sqlalchemy.Unicode (255)),
mysql_engine='InnoDB',
)
query1 = sqlalchemy.select([test],
sqlalchemy.and_ (
test.c.id == sqlalchemy.bindparam ('id'),
test.c.name == u'\u201csolutions\u201d',
#option 1
)
)
results = query1.execute(id=1)
query2 = sqlalchemy.select([test],
sqlalchemy.and_ (
test.c.id == sqlalchemy.bindparam ('id'),
test.c.name == sqlalchemy.bindparam('name'),
)
)
results = query2.execute(id=1, name=u'\u201csolutions\u201d')
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: limit=bindparam("result_limit")
I am trying to cut down on time the db spends compiling statements.
Using bind params
allows the query to look the same to the db, thus allowing it to not
parse it again. It's the
same use case that makes people want to use bind params anywhere. Am I
missing
something here? I'm a relative newbie to mysql and sqlalchemy, so I
may be missing information, but I'm
assuming that using a bindparam in sqlalchemy will result in a
prepared statement executed
using the assigned values that I want. If it is using a prepared
statement,
I'd expect the query to look something like
select * from table where limit=?
For Oracle, the rownum is simply part of the where clause, so I
wouldn't expect an issue with using
a bindparam for the limit.
On Sep 22, 8:09 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> "limit" is not really portable to all databases; in some cases
> (particularly Oracle) SQLAlchemy has to use some completely different
> syntaxes to come up with LIMIT.
>
> Additionally, the purpose of a bind parameter is to represent data
> that is compared against or inserted into a column expression.
> Whereas the LIMIT expression is part of the SQL construct
> itself...seems like PG and sqlite both allow it though but im not
> sure how oracle, mssql can deal with it (im pretty sure mysql allows
> it).
>
> more importantly whats the use case here ? just trying to cut down
> on the time SQLAlchemy spends compile statements ?
>
> On Sep 18, 2007, at 5:30 PM, dykang wrote:
>
>
>
> > Hi,
>
> > I was trying to write a query where the limit clause was actually a
> > bind param, however it appears that, (in 3.10), this isn't possible.
> > Am I missing something, or is this a bug?
>
> > for example I'm just doing
> > s = select ([Table], whereclause, limit=bindparam('mylimit'))
> > s.execute(mylimit=5)
>
> > Can someone tell me if this is not a supported behavior, or what I
> > need to do for this to work?
>
> > Thanks,
> > D
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] limit=bindparam("result_limit")
Hi,
I was trying to write a query where the limit clause was actually a
bind param, however it appears that, (in 3.10), this isn't possible.
Am I missing something, or is this a bug?
for example I'm just doing
s = select ([Table], whereclause, limit=bindparam('mylimit'))
s.execute(mylimit=5)
Can someone tell me if this is not a supported behavior, or what I
need to do for this to work?
Thanks,
D
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: bindparams don't work with in_?
ah ha! That's unfortunate as it prevents me from precompiling any
query with an 'in' in the where clause, but it makes sense after you
mention it. Anyway, thanks for pointing out my mistake,
David
On Feb 11, 9:56 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote:
> ive been alerted that the bindparam() function doesnt work inside of
> in_() and theres a new ticket that addresses this issue, but i havent
> had time to confirm.
>
> however, even if this is fixed, your usage would still be incorrect
> (except perhaps with postgres, which supports an ARRAY type
> implicitly); normally you cant pass an array to a bind parameter
> value. you would have to have a bindparam() instance for each scalar
> value youd like to pass, i.e.:
>
> s = table.select(table.c.somecol.in_(bindparam('param1'),
> bindparam('param2'), bindparam('param3')))
> s.execute(param1=2, param2=3, param3=4)
>
> On Feb 10, 4:46 pm, "dykang" <[EMAIL PROTECTED]> wrote:
>
> > from sqlalchemy import *
> > I was having some trouble understanding how to use the bindparams, and
> > I haven't been able to get them to work with the in_ operator. The
> > following code is a simple demonstration of what I'm trying (with
> > mysql). It connects to a db, creates a small table and then tries to
> > compile and execute a query that uses in_. When I try to execute the
> > code, I get the following exception:
> > sqlalchemy.exceptions.SQLError: (TypeError) not all arguments
> > converted during string formatting 'SELECT testings.id,
> > testings.some_data, testings.some_int \nFROM testings \nWHERE
> > testings.id = %s' [[2, 3, 4]]
>
> > I'm not really clear on how to use the bindparams properly, but this
> > seems to be incorrect, any help would be appreciated,
>
> > David
> > #==begin source below
>
> > meta = DynamicMetaData ()
> > meta.connect ('some uri')
> > meta.engine.echo=True
> > dbsession = create_session (bind_to=(meta.engine))
>
> > TestTbl = Table ('testings', meta,
> > Column ('id', Integer, Sequence ('testings_id_seq'),
> > primary_key=True),
> > Column ('some_data', Unicode (40)),
> > Column ('some_int', Integer),
> > mysql_engine='InnoDB')
>
> > class Testing (object):
> > pass
>
> > TestTbl.create ()
>
> > s = select ([TestTbl], TestTbl.c.id.in_(bindparam('my_id'))).compile
> > ()
> > some_mapper = mapper (Testing, TestTbl)
> > results = dbsession.query(Testing).instances (s.execute(my_id=[2,3,4]))
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] bindparams don't work with in_?
from sqlalchemy import *
I was having some trouble understanding how to use the bindparams, and
I haven't been able to get them to work with the in_ operator. The
following code is a simple demonstration of what I'm trying (with
mysql). It connects to a db, creates a small table and then tries to
compile and execute a query that uses in_. When I try to execute the
code, I get the following exception:
sqlalchemy.exceptions.SQLError: (TypeError) not all arguments
converted during string formatting 'SELECT testings.id,
testings.some_data, testings.some_int \nFROM testings \nWHERE
testings.id = %s' [[2, 3, 4]]
I'm not really clear on how to use the bindparams properly, but this
seems to be incorrect, any help would be appreciated,
David
#==begin source below
meta = DynamicMetaData ()
meta.connect ('some uri')
meta.engine.echo=True
dbsession = create_session (bind_to=(meta.engine))
TestTbl = Table ('testings', meta,
Column ('id', Integer, Sequence ('testings_id_seq'),
primary_key=True),
Column ('some_data', Unicode (40)),
Column ('some_int', Integer),
mysql_engine='InnoDB')
class Testing (object):
pass
TestTbl.create ()
s = select ([TestTbl], TestTbl.c.id.in_(bindparam('my_id'))).compile
()
some_mapper = mapper (Testing, TestTbl)
results = dbsession.query(Testing).instances (s.execute(my_id=[2,3,4]))
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] bitwise operators?
Hi, I was wondering if there was any way to write the following query without having to use a text block. select * from table where table.flags & 1 = 1 and table.flags & 4 = 0 I couldn't find any indication that it would be supported in the docs. The closest I found was the the ability to use the op () function. It allows me to use use the '&' operator, but it doesn't allow me to check what the result is. so I have been able to write the following query select * from table where table.flags & 1 but I have not been able to write the query select * from table where table.flags & 1 = 1 Thanks in advance, David --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
