Discuss usage/design issues for SQLAlchemy, a Python SQL toolkit

What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE

when using SqlAlchemy Core (not using Session/ORM)?

I have quite a few tables with composite primary keys that can use it
for optimization.

At the moment what I have is quite ghetto, below is a contrive example
of it:

    stmt = str(user_table.insert().values(email=email, name=name))
    stmt += " ON DUPLICATE KEY UPDATE name=%s"
    engine.execute(stmt, email, name, name)

NOTE: I believe this is MySQL specific.

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

ichineseflashcards 8 | 1 Feb 08:22

Learn Chinese (Mandarin) faster by using flashcards with pictures

http://www.ichineseflashcards.com will help you learn Chinese
(Mandarin) faster by using flashcards with pictures, thanks

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Torsten Landschoff | 1 Feb 10:58

Re: ClassManager.new_instance does not longer initialize polymorphic column

Hi Michael,

On Tue, 2012-01-31 at 10:26 -0500, Michael Bayer wrote:

> If you're using new_instance() to create the instance, then you are
> deserializing data from somewhere, why isn't the discriminator value,
> which is after all one of the column values in the table, not present
> in this deserialization ?

Because I considered the value of the "type" column an implementation
detail and it was managed automatically by SQLAlchemy so far. Further,
the serialized format is not that close to the database format (it is
actually XML).

> Right now the event in question is emitted only from the __init__()
> method of your object, which is obviously what you don't call when
> deserializing.    The discriminator value is now considered to be just
> another instance variable that you can change freely - a default for
> it is configured from __init__().

I actually think about diverging from my original approach. In case
anybody cares: So far, I am using __init__ to initialize some required
columns from context (for example, the creator column is initialized
from the current user). During deserialization this should not happen as
the current user will probably be different from the creator of the
received instance.

Probably it will be more pythonic by changing the implementation that it
is possible to create an empty instance for every class and catch
missing information at the database level.

(Continue reading)

headers

Pau Tallada | 1 Feb 11:40

Possible bug with join condition on self-referential outer joins

Hi!

I have a table with a self-reference of two columns that represents a tree structure.

I was trying to build an outerjoin to select all the nodes have children but NO grandchildren, but the SQL constructed was incorrect, as it was not aliasing properly one of the columns.

note_t Table('node_t', metadata,

Column('id', Integer, primary_key=True),

Column('project_id', Integer),

Column('parent_id', Integer),

sa.ForeignKeyConstraint(

['project_id', 'parent_id'],

['node_t.project_id', 'node_t.id]))

mapper(Node, node_t, properties= {

'children' : relationship(Node,

remote_side=[note_t.c.id, node_t.c.project_id]

)

})

print str(session.query(Node).outerjoin(Node, Node.children, aliased=True)))

Generated (simplified):

SELECT node.id, node.project_id, node.parent_id

FROM node

LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id

AND node.project_id = node.project_id

Expected:

SELECT node.id, node.project_id, node.parent_id

FROM node

LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id

AND node.project_id = parent.project_id

Making the join condition explicit generates the correct SQL

Parent = aliased(Node)

print str(session.query(Node).outerjoin(Parent, (Node.parent_id == Parent.id) & (Node.project_id == Parent.project_id)))

I have attached a small test file (test.py) that shows this behaviour and is based on one of your tests suites (test_relationships).

Thanks in advance!

Pau.

--

----------------------------------

Pau Tallada Crespí

Dep. d'Astrofísica i Cosmologia

Port d'Informació Científica (PIC)

Tel: +34 93 586 8233

----------------------------------

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

Attachment (test.py): text/x-python, 2987 bytes

headers

Christian Démolis | 1 Feb 15:54

Re: Overload Query Object

Hi,

right, it's that

thanks

2012/1/31 Tate Kim <insightptr <at> gmail.com>

Hi,

Have you checked the __iter__ method ?
From: Christian Démolis <christiandemolis <at> gmail.com>

Sender: sqlalchemy <at> googlegroups.com

Date: Tue, 31 Jan 2012 17:39:54 +0100

To: sqlalchemy<sqlalchemy <at> googlegroups.com>

ReplyTo: sqlalchemy <at> googlegroups.com

Subject: [sqlalchemy] Overload Query Object

Hi Michael,

i overload class Query in my script.
i have 4 ways to obtain query's results.

1/ session.query(model.x).all()
2/ session.query(model.x).first()
3/ session.query(model.x).one()
4/ for e in session.query(model.x):
        print e

in case 1,2,3, i know which method is used
What method is used in case 4 ?

Thanks in advance.
Chris

class Query(Query):
    def __init__(self, *arg, **kw):
        self._populate_existing = True
        super(Query, self).__init__(*arg, **kw)

    def all(self):
        print "<all>", threading.current_thread()
        param.lock_bdd.acquire()
        global session
        try:
            x = super(Query, self).all()
        except exc2.OperationalError:
            import common
            common.alerte("L'écriture a échoué. Retentez l'action", "Erreur MySQL")
            session.rollback()
        except exc2.StatementError:
            import common
            common.alerte("L'écriture a échoué. Retentez l'action", "Erreur MySQL")
            session.rollback()
        except:
            raise
        param.lock_bdd.release()
        print "</all>", threading.current_thread()
        return x

    def one(self):
        print "<one>", threading.current_thread()
        param.lock_bdd.acquire()
        global session
        try:
            x = super(Query, self).one()
        except exc2.OperationalError:
            import common
            common.alerte("L'écriture a échoué. Retentez l'action", "Erreur MySQL")
            session.rollback()
        except exc2.StatementError:
            import common
            common.alerte("L'écriture a échoué. Retentez l'action", "Erreur MySQL")
            session.rollback()
        except:
            raise
        param.lock_bdd.release()
        print "</one>", threading.current_thread()
        return x

    def first(self):
        print "<first>", threading.current_thread()
        param.lock_bdd.acquire()
        global session
        try:
            x = super(Query, self).first()
        except exc2.OperationalError:
            import common
            common.alerte("L'écriture a échoué. Retentez l'action", "Erreur MySQL")
            session.rollback()
        except exc2.StatementError:
            import common
            common.alerte("L'écriture a échoué. Retentez l'action", "Erreur MySQL")
            session.rollback()
        except:
            raise
        param.lock_bdd.release()
        print "</first>", threading.current_thread()
        return x

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> 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 <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> 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 <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Michael Bayer | 1 Feb 16:49

Re: Possible bug with join condition on self-referential outer joins

This is essentially ticket #1401 and I've attached this there as well as moved up the priority, however this issue is extremely complicated and would require some serious rethinking of the relationship()'s inner workings. It would take several days to come up with a general solution so I can't give you a fix for this right now.

http://www.sqlalchemy.org/trac/ticket/1401

On Feb 1, 2012, at 5:40 AM, Pau Tallada wrote:

Hi!

I have a table with a self-reference of two columns that represents a tree structure.

I was trying to build an outerjoin to select all the nodes have children but NO grandchildren, but the SQL constructed was incorrect, as it was not aliasing properly one of the columns.

note_t Table('node_t', metadata,

Column('id', Integer, primary_key=True),

Column('project_id', Integer),

Column('parent_id', Integer),

sa.ForeignKeyConstraint(

['project_id', 'parent_id'],

['node_t.project_id', 'node_t.id]))

mapper(Node, node_t, properties= {

'children' : relationship(Node,

remote_side=[note_t.c.id, node_t.c.project_id]

)

})

print str(session.query(Node).outerjoin(Node, Node.children, aliased=True)))

Generated (simplified):

SELECT node.id, node.project_id, node.parent_id

FROM node

LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id

AND node.project_id = node.project_id

Expected:

SELECT node.id, node.project_id, node.parent_id

FROM node

LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id

AND node.project_id = parent.project_id

Making the join condition explicit generates the correct SQL

Parent = aliased(Node)

print str(session.query(Node).outerjoin(Parent, (Node.parent_id == Parent.id) & (Node.project_id == Parent.project_id)))

I have attached a small test file (test.py) that shows this behaviour and is based on one of your tests suites (test_relationships).

Thanks in advance!

Pau.

--
----------------------------------
Pau Tallada Crespí

Dep. d'Astrofísica i Cosmologia
Port d'Informació Científica (PIC)
Tel: +34 93 586 8233

----------------------------------

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
<test.py>

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Thijs Engels | 1 Feb 17:14

Relationship definition problem

I am currently facing a problem with a legacy database for which I am
creating a SQLAlchemy layer. With regards to mirroring the columns
everything goes well. However I am having difficulties defining a
relationship which is not many-to-many, but does use something which can
be compared with an association proxy (I think). The simplified model:

class Session(Base):
    __tablename__ = 'sessions'
    id = Column(Integer, primary_key=True)

class Area(Base):
    __tablename__ = 'areas'
    session_id = Column(Integer, ForeignKey('sessions.id'),
    primary_key=True)
    area_id = Column(Integer, primary_key=True)
    name = Column(String)

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    session_id = Column(Integer, ForeignKey('sessions.id'))

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer,  ForeignKey('parents.id'))
    area_id = Column(Integer)

    area = relationship(???)

In short; the Area class has a multi-column primary key (sesseion_id,
area_id). I am trying to create the relationship for area in the Child
class, however this would need the session_id which can be found in the
parent class. This was my last effort (again assuming I could treat the
parent class as a association proxy):

    area = relationship(
        'Area',
        secondary='parents',
        primaryjoin='children.c.parent_id==parents.c.id',
        secondaryjoin='and_(parents.c.session_id==areas.c.session_id,
        children.c.area_id==areas.c.id)',
        viewonly=True
    )

No able to figure out any foreign key relations if those would be
required.

Any help and/or insight would be appreciated,

Thijs

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Michael Bayer | 1 Feb 17:46

Re: Relationship definition problem

On Feb 1, 2012, at 11:14 AM, Thijs Engels wrote:

> In short; the Area class has a multi-column primary key (sesseion_id,
> area_id). I am trying to create the relationship for area in the Child
> class, however this would need the session_id which can be found in the
> parent class. This was my last effort (again assuming I could treat the
> parent class as a association proxy):
> 
>    area = relationship(
>        'Area',
>        secondary='parents',
>        primaryjoin='children.c.parent_id==parents.c.id',
>        secondaryjoin='and_(parents.c.session_id==areas.c.session_id,
>        children.c.area_id==areas.c.id)',
>        viewonly=True
>    )
> 
> No able to figure out any foreign key relations if those would be
> required.
> 
> Any help and/or insight would be appreciated,

it's not very streamlined but a blunt approach using EXISTS seems to work:

class Child(Base):
   __tablename__ = 'children'
   id = Column(Integer, primary_key=True)
   parent_id = Column(Integer,  ForeignKey('parents.id'))
   area_id = Column(Integer)

   area = relationship(Area, foreign_keys=area_id, 
                        primaryjoin=
                            (Area.area_id==area_id) 
                            & 
                            exists().where(
                                (Parent.session_id==Area.session_id) & 
                                (parent_id==Parent.id))
                            )

There's two ways to navigate from Child to Area.   What does it mean for Child.area_id to have a value that
doesn't match on Child.parent_id->Parent.session_id->Area.session_id ?  Is that essentially
corrupt data ?    That there's two ways to navigate implies that there are essentially two different
relationships to Area - one is the straight relationship() on FK.  The other is a long association proxy
across Parent->Session->Area.   Then the "area" you're looking for here is the intersection of those two.  
The primaryjoin we have above is basically doing the same thing in SQL, though if I wanted to be able to get at
Child->Area in both ways independently I might build it out as the two relationships.  If there's no need
for two different ways to navigate from Child->Area, that only points out how this relational design is
wrong - it's capable of storing corrupt data.   Child.area_id might be just a denormalized "shortcut" to
the longer chain across Parent.

--

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Michael Bayer | 1 Feb 19:56

Re: What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE

assuming this is the same thing asked on reddit: http://www.reddit.com/r/Python/comments/p5grh/sqlalchemy_whats_the_idiomatic_way_of_writing/

I mentioned ticket #960 as where we'd someday support "MERGE" and its variants, but this can also be rolled
with @compiles, see the example in that ticket.

http://www.sqlalchemy.org/trac/ticket/960

On Jan 31, 2012, at 11:32 PM, Didip Kerabat wrote:

> when using SqlAlchemy Core (not using Session/ORM)?
> 
> I have quite a few tables with composite primary keys that can use it
> for optimization.
> 
> At the moment what I have is quite ghetto, below is a contrive example
> of it:
> 
>    stmt = str(user_table.insert().values(email=email, name=name))
>    stmt += " ON DUPLICATE KEY UPDATE name=%s"
>    engine.execute(stmt, email, name, name)
> 
> NOTE: I believe this is MySQL specific.
> 
> -- 
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlalchemy <at> googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> 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 <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Didip Kerabat | 1 Feb 20:09

Re: What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE

Correct, the same guy.

Thank you for the pointer! Today I learned about the compiles decorator.

- Didip -

On Feb 1, 2012, at 10:56 AM, Michael Bayer wrote:

> assuming this is the same thing asked on reddit: http://www.reddit.com/r/Python/comments/p5grh/sqlalchemy_whats_the_idiomatic_way_of_writing/
> 
> I mentioned ticket #960 as where we'd someday support "MERGE" and its variants, but this can also be rolled
with @compiles, see the example in that ticket.
> 
> http://www.sqlalchemy.org/trac/ticket/960
> 
> 
> On Jan 31, 2012, at 11:32 PM, Didip Kerabat wrote:
> 
>> when using SqlAlchemy Core (not using Session/ORM)?
>> 
>> I have quite a few tables with composite primary keys that can use it
>> for optimization.
>> 
>> At the moment what I have is quite ghetto, below is a contrive example
>> of it:
>> 
>>   stmt = str(user_table.insert().values(email=email, name=name))
>>   stmt += " ON DUPLICATE KEY UPDATE name=%s"
>>   engine.execute(stmt, email, name, name)
>> 
>> NOTE: I believe this is MySQL specific.
>> 
>> -- 
>> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy <at> googlegroups.com.
>> To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> 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 <at> googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> 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 <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

188	May 2012
267	April 2012
302	March 2012
319	February 2012
225	January 2012
351	December 2011
231	November 2011
224	October 2011
272	September 2011
319	August 2011
302	July 2011
343	June 2011
219	May 2011
300	April 2011
288	March 2011
406	February 2011
383	January 2011
320	December 2010
326	November 2010
366	October 2010
429	September 2010
407	August 2010
387	July 2010
394	June 2010
426	May 2010
444	April 2010
449	March 2010
496	February 2010
345	January 2010
311	December 2009
306	November 2009
464	October 2009
420	September 2009
472	August 2009
329	July 2009
338	June 2009
444	May 2009
445	April 2009
459	March 2009
442	February 2009
546	January 2009
471	December 2008
459	November 2008
439	October 2008
599	September 2008
542	August 2008
533	July 2008
562	June 2008
604	May 2008
494	April 2008
394	March 2008
439	February 2008
520	January 2008
488	December 2007
509	November 2007
417	October 2007
408	September 2007
466	August 2007
590	July 2007
576	June 2007
402	May 2007
621	April 2007
649	March 2007
585	February 2007
544	January 2007
462	December 2006
372	November 2006
387	October 2006
341	September 2006
224	August 2006
455	July 2006
442	June 2006
422	May 2006
396	April 2006
487	March 2006
351	February 2006
246	January 2006
95	December 2005
48	November 2005

Mon	Tue	Wed	Thu	Fri	Sat	Sun

		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29