Re: [sqlalchemy] I've got an InterfaceError without reason
On Oct 27, 2010, at 5:06 PM, justin wrote:
> I have a table in my db, named nameConversions.
> The scheme is as follows:
> nameConversions = Table('nameConversions', metadata,
>Column('ConversionID', Integer, primary_key=True),
>Column('TaxonCode', String(lenAbbr), nullable=False),
>Column('Note', String(1), nullable=False),
>Column('TaxonNode', Integer, nullable=False),
>Column('OfficialName', String(lenLongName), nullable=True),
>Column('CommonName', String(lenLongName), nullable=True),
>Column('Synonym', String(lenLongName), nullable=True),
> )
>
> Now, I am retrieving rows with TaxonCode 'SYTHE1' multiple times using
> SELECT, and it succeeds several times. But at the 5th execution with
> the keyword, it fails with InterfaceError. Below is the error
> message.
>
> sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding
> parameter 0 - probably unsupported type. u'SELECT
> "nameConversions"."ConversionID", "nameConversions"."TaxonCode",
> "nameConversions"."Note", "nameConversions"."TaxonNode",
> "nameConversions"."OfficialName", "nameConversions"."CommonName",
> "nameConversions"."Synonym" \nFROM "nameConversions" \nWHERE
> "nameConversions"."TaxonCode" = ?' ('SYTHE1',)
>
> What I cannot understand is that now that the SELECT process has been
> run successfully several times before, why all of a sudden it crashes
> with this error message?
the InterfaceError is generated by the DBAPI. SQLAlchemy wraps this in its own
exception but that's a DBAPI message. It's hard to tell above, but it looks
like perhaps ('SYTHE1',) is a tuple when it should be a string.
>
> What I have checked was whether the types of the keyword are all the
> same as 'str', and they are. I can't think of anything I should
> investigate other than this. I already have spent too much time on
> this. So please help me.
so if you think they're all string, try to trip it up to reveal the issue. Try
"for name in names[0:4]" (something special about the # 5?) , "for name in
reversed(names)" (something special about one of them, their ordering ?), use
pdb.set_trace(), etc.
>
> FYI. the python code that's run is the following.
> ---
>for name in names:
>SQLNameConversions =
> NameConversions.select(NameConversions.c.TaxonCode==str(name))
>exeNameConversions = SQLNameConversions.execute()
> ---
>
>
> Thank you guys for your time to read this and to try to help me out.
> Justin.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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 sqlalch...@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] I've got an InterfaceError without reason
I have a table in my db, named nameConversions.
The scheme is as follows:
nameConversions = Table('nameConversions', metadata,
Column('ConversionID', Integer, primary_key=True),
Column('TaxonCode', String(lenAbbr), nullable=False),
Column('Note', String(1), nullable=False),
Column('TaxonNode', Integer, nullable=False),
Column('OfficialName', String(lenLongName), nullable=True),
Column('CommonName', String(lenLongName), nullable=True),
Column('Synonym', String(lenLongName), nullable=True),
)
Now, I am retrieving rows with TaxonCode 'SYTHE1' multiple times using
SELECT, and it succeeds several times. But at the 5th execution with
the keyword, it fails with InterfaceError. Below is the error
message.
sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding
parameter 0 - probably unsupported type. u'SELECT
"nameConversions"."ConversionID", "nameConversions"."TaxonCode",
"nameConversions"."Note", "nameConversions"."TaxonNode",
"nameConversions"."OfficialName", "nameConversions"."CommonName",
"nameConversions"."Synonym" \nFROM "nameConversions" \nWHERE
"nameConversions"."TaxonCode" = ?' ('SYTHE1',)
What I cannot understand is that now that the SELECT process has been
run successfully several times before, why all of a sudden it crashes
with this error message?
What I have checked was whether the types of the keyword are all the
same as 'str', and they are. I can't think of anything I should
investigate other than this. I already have spent too much time on
this. So please help me.
FYI. the python code that's run is the following.
---
for name in names:
SQLNameConversions =
NameConversions.select(NameConversions.c.TaxonCode==str(name))
exeNameConversions = SQLNameConversions.execute()
---
Thank you guys for your time to read this and to try to help me out.
Justin.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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 to use object_session properly
hello all I want to know how to create Session with object_session which can be used to add data to the database i.e using Session.add_all().So i can pass a list as parameter to add_all as my length of list is of variable length. When i am trying to do that i was not able to associate instance properly and getting Session as None. I already gone through the below link but left with no clue. So please help me with this. http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.object_session Njoy the share of Freedom Anusha -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] 'Lookup' Tables
On Oct 27, 2010, at 2:36 PM, Mark Erbaugh wrote:
> I have a data graph mapped as:
>
> class BatchDetail(BASE):
>
> __tablename__ = TABLE_BATCH_DET
>
> id = Column(Integer, primary_key=True)
> batch = Column(ForeignKey(TABLE_BATCH_HDR + '.id'))
> account_id = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
> # other fields
>
> # -
>
> class BatchHeader(BASE):
>
> __tablename__ = TABLE_BATCH_HDR
> id = Column(Integer, primary_key=True)
>
> # other fields
>
> detail = relation('BatchDetail',
> backref=backref('header', remote_side=id))
>
> # ---
>
> class L3Acct(BASE):
>
> __tablename__ = TABLE_L3_ACCT
> __table_args__ = (UniqueConstraint("l2_id", "l3_acct"), {})
>
> id = Column(Integer, primary_key=True)
> parent = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
> l2_id = Column(ForeignKey(TABLE_L2_ACCT + '.id'))
> # other fields
>
>
> Basically, a 'batch' consists of a number of detail lines, each of which has
> an associated L3Account. In addition, each L3Acct is a child of a L2Acct
> (l2_id foreign key) and may be a child of another L3Acct (parent foreign key).
>
> The application considers the 'batch' (BatchHeader) as the main item. A batch
> is created and detail (BatchDetail) rows are added. The L3Acct is used as a
> lookup. In my mapping, I haven't set up a relationship between BatchDetail
> and L3Acct and I'm maintaining that relationship with code external to the
> mapping. Is it possible to let SA manage this relationship?
>
> if so, Here's what I'm having trouble with. When the application needs to
> add a new detail record, it can just create a new BatchDetail instance and
> append it to the detail collection. I assume that similarly, to associate a
> L3Acct, the BatchDetail instance needs to be added to a collection associated
> with L3Acct, but where is that collection? And how would one change the
> association from one L3Acct to another? Maybe a better way of asking this
> question is how to you work with a one-many relationship from the 'many'
> side?
>
I've done a little reading in the docs and some playing with some unit tests,
and I think I have it figured out.
Mark
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] 'Lookup' Tables
I have a data graph mapped as:
class BatchDetail(BASE):
__tablename__ = TABLE_BATCH_DET
id = Column(Integer, primary_key=True)
batch = Column(ForeignKey(TABLE_BATCH_HDR + '.id'))
account_id = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
# other fields
# -
class BatchHeader(BASE):
__tablename__ = TABLE_BATCH_HDR
id = Column(Integer, primary_key=True)
# other fields
detail = relation('BatchDetail',
backref=backref('header', remote_side=id))
# ---
class L3Acct(BASE):
__tablename__ = TABLE_L3_ACCT
__table_args__ = (UniqueConstraint("l2_id", "l3_acct"), {})
id = Column(Integer, primary_key=True)
parent = Column(ForeignKey(TABLE_L3_ACCT + '.id'))
l2_id = Column(ForeignKey(TABLE_L2_ACCT + '.id'))
# other fields
Basically, a 'batch' consists of a number of detail lines, each of which has an
associated L3Account. In addition, each L3Acct is a child of a L2Acct (l2_id
foreign key) and may be a child of another L3Acct (parent foreign key).
The application considers the 'batch' (BatchHeader) as the main item. A batch
is created and detail (BatchDetail) rows are added. The L3Acct is used as a
lookup. In my mapping, I haven't set up a relationship between BatchDetail and
L3Acct and I'm maintaining that relationship with code external to the mapping.
Is it possible to let SA manage this relationship?
if so, Here's what I'm having trouble with. When the application needs to add
a new detail record, it can just create a new BatchDetail instance and append
it to the detail collection. I assume that similarly, to associate a L3Acct,
the BatchDetail instance needs to be added to a collection associated with
L3Acct, but where is that collection? And how would one change the association
from one L3Acct to another? Maybe a better way of asking this question is how
to you work with a one-many relationship from the 'many' side?
Thanks,
Mark
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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: Working with mapper objects without saving them
Thanks, that looks like pretty much the thing I need.
Although, as a note, I mostly define my relationships in the opposite
direction than then example in the documentation; That is, I define a
"order" relationship in the Item model, with a "items" backref, which
I suppose is just a question of preference. Unless I'm missing
something though, this currently requires me to use cascade_backref
like so:
order = db.relationship(Order,
backref=db.backref('items',cascade_backrefs=False))
I.e. it's a slight brain twister, the backref of the backref meaning
the relationship being defined. Just throwing this out there, in case
someone thinks adding another argument which could be passed to
relationship() in a case like mine might be appropriate.
Michael
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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] Re: How to force a before_update() run without changes to instrumented attributes
if you just set it to itself, that emits a change event. You could also set it to something like the current timestamp, and have your before_update() extension expire the changes on it after detection. On Oct 27, 2010, at 1:48 PM, Nikolaj wrote: > Can you give an example of a forwards-compatible way to modify an > attribute to mark the instance dirty without creating actual net > changes? SA seems quite good at detecting my tricks. > > On Oct 27, 6:41 pm, Michael Bayer wrote: >> On Oct 27, 2010, at 11:43 AM, Nikolaj wrote: >> >>> I have a mapped class called Widget, with an uninstrumented attribute >>> called 'owner' (i.e. it does not have a column representation). Widget >>> has a MapperExtension with a before_update() method that runs a couple >>> of queries using connection.execute() and sets some attributes on the >>> instance. >> >>> I would like Widget.owner to behave like an instrumented attribute in >>> the sense that modifying it forces a MapperExtension.before_update() >>> run at flush-time. Is that possible? >> >>> Alternatively, is there a way to force a before_update() run by >>> marking an instance as dirty (possibly by modifying attributes without >>> actual net changes)? In that case I could make Widget.owner into a >>> property and mark the instance dirty in the setter. >> >> that last idea would work and would be forwards-compatible. Otherwise >> you'd need to emit a modified event to instance_state(myobject) which isn't >> public API. >> >> >> >>> Thanks, >> >>> N >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To post to this group, send email to sqlalch...@googlegroups.com. >>> To unsubscribe from this group, send email to >>> sqlalchemy+unsubscr...@googlegroups.com. >>> For more options, visit this group >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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 to force a before_update() run without changes to instrumented attributes
Can you give an example of a forwards-compatible way to modify an attribute to mark the instance dirty without creating actual net changes? SA seems quite good at detecting my tricks. On Oct 27, 6:41 pm, Michael Bayer wrote: > On Oct 27, 2010, at 11:43 AM, Nikolaj wrote: > > > I have a mapped class called Widget, with an uninstrumented attribute > > called 'owner' (i.e. it does not have a column representation). Widget > > has a MapperExtension with a before_update() method that runs a couple > > of queries using connection.execute() and sets some attributes on the > > instance. > > > I would like Widget.owner to behave like an instrumented attribute in > > the sense that modifying it forces a MapperExtension.before_update() > > run at flush-time. Is that possible? > > > Alternatively, is there a way to force a before_update() run by > > marking an instance as dirty (possibly by modifying attributes without > > actual net changes)? In that case I could make Widget.owner into a > > property and mark the instance dirty in the setter. > > that last idea would work and would be forwards-compatible. Otherwise you'd > need to emit a modified event to instance_state(myobject) which isn't public > API. > > > > > Thanks, > > > N > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] How to force a before_update() run without changes to instrumented attributes
On Oct 27, 2010, at 11:43 AM, Nikolaj wrote: > I have a mapped class called Widget, with an uninstrumented attribute > called 'owner' (i.e. it does not have a column representation). Widget > has a MapperExtension with a before_update() method that runs a couple > of queries using connection.execute() and sets some attributes on the > instance. > > I would like Widget.owner to behave like an instrumented attribute in > the sense that modifying it forces a MapperExtension.before_update() > run at flush-time. Is that possible? > > Alternatively, is there a way to force a before_update() run by > marking an instance as dirty (possibly by modifying attributes without > actual net changes)? In that case I could make Widget.owner into a > property and mark the instance dirty in the setter. that last idea would work and would be forwards-compatible. Otherwise you'd need to emit a modified event to instance_state(myobject) which isn't public API. > > Thanks, > > N > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] Storing lists of simple strings
On Oct 27, 2010, at 12:50 PM, Torsten Landschoff wrote:
> Replacing the Sheet class and following code like this, I can at least access
> the names list as if it really was a simple list.
>
> class Sheet(Base):
> __tablename__ = "sheet"
> sheet_id = Column("sheet_id", Integer, primary_key=True)
> # List of names
> _names = relation(Name,
> order_by=[Name.position],
> collection_class=ordering_list('position'),
> cascade="save-update, merge, delete, delete-orphan")
> names = association_proxy("_names", "value")
> # ... more columns
>
> engine = create_engine("sqlite:///", echo=True)
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
>
> session = Session()
> sheet = Sheet()
> sheet.names = ["Foo", "Bar", "Baz"]
> session.add(sheet)
> session.commit()
>
> session = Session()
> sheet = session.query(Sheet).one()
> assert sheet.names == ["Foo", "Bar", "Baz"]
>
> sheet.names[1:1] = ["Add", "Two"]
> session.commit()
>
> session = Session()
> sheet = session.query(Sheet).one()
> assert sheet.names == ["Foo", "Add", "Two", "Bar", "Baz"]
>
> But this code is too unwieldy IMHO. What I would like to have is something
> like
>
> class Sheet(object):
> ...
> names = make_ordered_list("names", String)
>
> But I failed to implement this. I can only think of gross hacks to make that
> work (like going up the stack frame and finding the type instance). Any hints
> on how to make this happen?
Configuration is too unwieldy ? Since you're using declarative , just using
@declared_attr would give you access to the class:
def make_ordered_list(key, pk, type_):
@declared_attr
def go(cls):
class Name(Base):
__tablename__ = "%s_names" % key
rel_id = Column("related_id", Integer, ForeignKey(pk),
primary_key=True, index=True)
position = Column("position", Integer)
value = Column("value", type_, primary_key=True)
def __init__(self, value):
self.value = value
private_key = "_" + key
setattr(cls, key, association_proxy(private_key, "value"))
return relation(
Name,
order_by=Name.position,
collection_class=ordering_list('position')
)
Name.__name__ = '%sName' % key
return go
class Sheet(Base):
__tablename__ = "sheet"
sheet_id = Column("sheet_id", Integer, primary_key=True)
_names = make_ordered_list("names", sheet_id, String)
if you wanted to get rid of saying "_names", you need to create something that
is added after the fact:
Sheet.names = make_ordered_list(Sheet, ...)
or class decorator:
@stores_names('names')
class Sheet(...)
This because the class is not mapped, and no mapper exists, until the class has
been constructed. Declarative lets you get to the mapper using the class-bound
attributes, with @declared_attr the path towards one that is dynamic. So this
is sort of an issue of you'd like more hooks into declarative's interpretation
of mapper().
On that note, if you're ambitious, you could make a wrapper around mapper() and
give that to declarative_base() as its "mapper" argument, which looks for
special attributes on the class and generates additional properties.
>
> Greetings, Torsten
>
> --
> DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
> Torsten Landschoff
>
> Office Dresden
> Tel: +49-(0)351-4519587
> Fax: +49-(0)351-4519561
>
> mailto:torsten.landsch...@dynamore.de
> http://www.dynamore.de
>
> Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
> Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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 sqlalch...@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] Create_all() -> Create_JustThese(engine, [Table1,Table2,....])?
Check the docs, create_all has an optional tables= parameter. On Oct 27, 2010 8:18 AM, "Martijn Moeling" wrote: Hi, I have a huge definition module where I create Python objects and use declarative. Since not all databases (Multiple for different customers) need all tables I do not like to use create_all is there any way to create just the tables I really need (according to some config list or so) say: class C1(Base): __tablename__ = "C1" .. class C2(Base): __tablename__ = "C2" .. class C3(Base): __tablename__ = "C3" .. create_JustThese(engine, [C1,C3]) -> table C2 is NOT created Please do not reply with why I would want this, I just want to know if it is possible and it would help me out big time if it is Martijn -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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 sqlalch...@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] Create_all() -> Create_JustThese(engine, [Table1,Table2,....])?
Hi, I have a huge definition module where I create Python objects and use declarative. Since not all databases (Multiple for different customers) need all tables I do not like to use create_all is there any way to create just the tables I really need (according to some config list or so) say: class C1(Base): __tablename__ = "C1" .. class C2(Base): __tablename__ = "C2" .. class C3(Base): __tablename__ = "C3" .. create_JustThese(engine, [C1,C3]) -> table C2 is NOT created Please do not reply with why I would want this, I just want to know if it is possible and it would help me out big time if it is Martijn -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Link problem on : http://www.sqlalchemy.org/features.html
the "View Current DBAPI Support" link on the above page gives a "page not found" error. Werner -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
