Re: [sqlalchemy] Session remove/close MySQL
Thx all NullPool solve my problem create_engine(cnx_str, poolclass=NullPool) 2014-02-07 19:11 GMT+01:00 Claudio Freire klaussfre...@gmail.com: On Fri, Feb 7, 2014 at 2:35 PM, Michael Bayer mike...@zzzcomputing.com wrote: The connection pool, if in use, will then not actually close the connection if it is to remained pooled, it calls rollback() as part of the pool release mechanism. Recent versions of SQLAlchemy allow this to show up in the engine logs like any other rollback, so you probably wouldn't have noticed. And *this* is what was not happening. Somehow, transactions remained open on the database (I checked). that kind of thing generally happens to people when they aren't cleaning up their sessions, or are using awkward engine/connection patterns. the pool has had a lot of bugs fixed but I haven't seen a bug where the pool isn't emitting the rollback when the connection is marked closed. There was an awkward pattern involved: using the session's connection as returned by Session.connection() manually to issue some textual SQL. Other than that, normal thread-local session stuff. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Session remove/close MySQL
Hi all, Actually, i have some problem closing my session... I tried using scopedsession with session.remove I tried using normal session with session.close But in both cases, the Mysql session stay open. Why closing session has no effet on current Mysql connections ? -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] twophase error sqlalchemy
Hi, AttributeError: 'NoneType' object has no attribute 'twophase' 243. 244. 245. 246. 247. 248. 249. 250. 251. 252. Session already has a Connection associated for the given Connection's Engine) else: conn = bind.contextual_connect() if self.session.twophase and self._parent is None: transaction = conn.begin_twophase() elif self.nested: transaction = conn.begin_nested() else: Anybody understand this error ? -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Optimize and SqlAlchemy
Hi all, I have a very strange problem... I have an application which runs under SQL Alchemy. When my application is running and i launch OPTIMIZE TABLE in PhpMyAdmin, my process blocks showing this error Waiting for metadata lock When i close my application, the process unlocks. What happen in SqlAlchemy, do the library keep metadata locked during all the session ? Thx in advance Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Association Proxy append a new item to relationship in same way as classic many to many relationship
*Hi M. Bayer and thanks for your answer,
i m doing exactly what explain here :
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects
it works well
but when i want to append a new element in relationship i have an error due
to creator function missing*
*here part of my model*
# Many to Many
class DossierTarife(Base):
__tablename__ = 'tarifer_dossier'
IdDossier = Column(Integer, ForeignKey('dossier.IdDossier'),
primary_key=True)
IdAt = Column(Integer, ForeignKey('article_tarife.IdAt'),
primary_key=True)
dossier = relationship(Dossier, backref=backref(tarifer_dossier,
cascade=all, delete-orphan))
article_tarife = relationship(ArticleTarife,
backref=backref(tarifer_dossier))
class Dossier(Base):
__tablename__ = 'dossier'
IdDossier = Column('IdDossier', Integer, primary_key=True)
...
class ArticleTarife(Base):
__tablename__ = 'article_tarife'
IdAt = Column('IdAt', Integer, primary_key=True)
...
Dossier.LesTar = association_proxy(tarifer_dossier, article_tarife)
ArticleTarife.LesTar = association_proxy(tarifer_dossier, dossier)
*I do my simulation with this*
print =
d =
model.session.query(model.Dossier).filter(model.Dossier.IdDossier==500315).first()
print d.NomDossier, len(d.LesTar), d.LesTar
print --
s =
model.session.query(model.ArticleTarife).filter(model.ArticleTarife.IdAt==366).first()
if s in d.LesTar:
d.LesTar.remove(s)
else:
d.LesTar.append(s)
print len(d.LesTar), d.LesTar
common.merge_object(d)
print s.Nom or s.Valeur
print s.LesTar
print ---
print
*It works well, i can access articletarife objects from dossier and dossier
objects from articletarife*
=
VIANEOS 9 [model.ArticleTarife object at 0x06E4B0F0, model.ArticleTarife
object at 0x06E4BE30,
model.ArticleTarife object at 0x06E4B190, model.ArticleTarife object at
0x06E4BEB0, model.Articl
eTarife object at 0x06E4B8D0, model.ArticleTarife object at 0x06E3F310,
model.ArticleTarife obje
ct at 0x06E4B210, model.ArticleTarife object at 0x06E4BFB0,
model.ArticleTarife object at 0x06E2
8F90]
--
8 [model.ArticleTarife object at 0x06E4BE30, model.ArticleTarife object
at 0x06E4B190, model.Ar
ticleTarife object at 0x06E4BEB0, model.ArticleTarife object at
0x06E4B8D0, model.ArticleTarife
object at 0x06E3F310, model.ArticleTarife object at 0x06E4B210,
model.ArticleTarife object at 0x
06E4BFB0, model.ArticleTarife object at 0x06E28F90]
1.13
[model.Dossier object at 0x06C69490, model.Dossier object at
0x06C69C10, model.Dossier object a
t 0x06C69270, model.Dossier object at 0x06381250, None, model.Dossier
object at 0x06381930, mo
del.Dossier object at 0x06381D50, model.Dossier object at 0x06381CF0,
model.Dossier object at 0x
063A3330, None, None, None, None, None, model.Dossier object at
0x06E28CF0, model.Dossier object
*But when sqlalchemy need to append a new element to relation ship, i have
this error*
Traceback (most recent call last):
File atao.py, line 2, in module
main.run()
File I:\main.py, line 392, in run
app = AtaoApplication(redirect=False)
File C:\Python26\lib\site-packages\wx-2.8-msw-unicode\wx\_core.py, line
7981, in __init__
self._BootstrapApp()
File C:\Python26\lib\site-packages\wx-2.8-msw-unicode\wx\_core.py, line
7555, in _BootstrapApp
return _core_.PyApp__BootstrapApp(*args, **kwargs)
File I:\main.py, line 383, in OnInit
param.main_window = MainWindow()
File I:\main.py, line 172, in __init__
d.LesTar.append(s)
File
C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg\sqlalchemy\ext\associationpro
xy.py, line 469, in append
item = self._create(value)
File
C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg\sqlalchemy\ext\associationpro
xy.py, line 396, in _create
return self.creator(value)
TypeError: __init__() takes exactly 1 argument (2 given)
*
i read what explain here
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#creation-of-new-values
**but i have no intermediary object, i have the full object like classic
many to many relationship pattern describe here :
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#building-a-many-to-many-relationship
.
What do i need in my model to have the same behaviour that classic many to
many relationship ?
I already try adding : creator=lambda x: x to my association proxies*
2012/3/27 Michael Bayer mike...@zzzcomputing.com
On Mar 27, 2012, at 4:35 AM, Christian Démolis wrote:
Up!
2012/3/23 Christian Démolis christiandemo...@gmail.com
Hi all,
class A(Base):
__tablename__ = 'a'
IdA = Column('IdA', Integer, primary_key=True)
AllTheB = association_proxy(many_to_many_relation, relation_b)
class ManyToManyRelation
[sqlalchemy] Association Proxy append a new item to relationship in same way as classic many to many relationship
Hi all,
class A(Base):
__tablename__ = 'a'
IdA = Column('IdA', Integer, primary_key=True)
AllTheB = association_proxy(many_to_many_relation, relation_b)
class ManyToManyRelation(
__tablename__ = 'many_to_many_relation'
IdA = Column(Integer, ForeignKey('A.IdA'), primary_key=True)
IdB = Column(Integer, ForeignKey('B.IdB'), primary_key=True)
relation_a = relationship(A, backref=backref(tarifer_dossier,
cascade=all, delete-orphan))
relation_b = relationship(B, backref=backref(tarifer_dossier,
cascade=all, delete-orphan))
class B(Base):
__tablename__ = 'b'
IdB = Column('IdB ', Integer, primary_key=True)
AllTheA = association_proxy(many_to_many_relation, relation_a)
x_a is instance of A
x_b is instance of B
x_a.AllTheB returns me all the objects B relative to x_a
When i want to append new element e (instance of A)
x_a.AllTheB.append(e)
I have an error due to create mechanism of association_prox
How can i have the same (simple) behaviour of classical many to many
relationship ?
Thanks in advance
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Handle many to many relationship
Thanks a lot, i will try it.
Chris
2012/3/16 zz elle zze...@gmail.com
Hi,
You can define a SA object associated to the table tarife_dossier:
- you set its IdDossier (IdAt) attributes to its associated dossier
(article) id
- or you set dossier (article_tarife) to its associated dossier
(articke) object
and you save it !
PS: tarife ou tarif ?
On Thursday, March 15, 2012 12:38:26 PM UTC+1, Christian Démolis wrote:
Hi all,
DossierTarife = Table('tarifer_dossier', Base.metadata,
Column('IdDossier', Integer, ForeignKey('dossier.IdDossier'**)),
Column('IdAt', Integer, ForeignKey('article_tarife.**IdAt'))
)
Dossier.LesTar = relation(ArticleTarife, secondary=DossierTarife,
backref=backref('dossier'))
ArticleTarife.LesTar = relation(Dossier, secondary=DossierTarife,
backref=backref('article_**tarife'))
When i want to change a many to many relation, the tutorial says that i
must add object in the relationship (list) :
self.tarif_cible.LesTar.**append(d)
where d is an instance of Dossier. But Dossier is an heavy table, so
object is heavy too.
Is it any other simple way to change a many to many association (example
: directly access DossierTarife table) ???
Thx in advance,
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/FZb31d8fPMcJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Overload Query Object
Hi, right, it's that thanks 2012/1/31 Tate Kim insight...@gmail.com ** Hi, Have you checked the __iter__ method ? -- *From: * Christian Démolis christiandemo...@gmail.com *Sender: * sqlalchemy@googlegroups.com *Date: *Tue, 31 Jan 2012 17:39:54 +0100 *To: *sqlalchemysqlalchemy@googlegroups.com *ReplyTo: * sqlalchemy@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@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[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@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 write and access attribute in many to many table
Hi all,
I have a question about many to many Table containing attribute.
How to access and write Max attribute in many to many table ?
I already read that but i try to not use mapper and stay in declarative mode
which is more user friendly :)
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html
PlageTypeActe = Table('plage_type_acte', Base.metadata,
Column('IdPlage', Integer, ForeignKey('plage_type.IdPlage'),
primary_key=True),
Column('IdActe', Integer, ForeignKey('acte.IdActe'), primary_key=True),
*Column('Max', Integer)*
)
class PlageType(Base):
__tablename__ = 'plage_type'
Typage = 2
IdPlage = Column('IdPlage', Integer, primary_key=True)
Debut = Column('Debut', DateTime)
Fin = Column('Fin', DateTime)
JourSemaine = Column('JourSemaine', Integer)
EtatPrecedent = Column('EtatPrecedent', String)
EtatCourant = Column('EtatCourant', String)
EtatSuivant = Column('EtatSuivant', String)
Max = Column('Max', Integer)
SurchargeVad = Column('SurchargeVad', Boolean)
StopVad = Column('StopVad', Time)
Creation = Column('Creation', DateTime)
Modification = Column('Modification', DateTime)
MaxDejaVenu = Column('MaxDejaVenu', Integer)
MaxJamaisVenu = Column('MaxJamaisVenu', Integer)
MaxActif = Column('MaxActif', Integer)
MaxInactif = Column('MaxInactif', Integer)
MaxPatient = Column('MaxPatient', Integer)
MaxDelegue = Column('MaxDelegue', Integer)
MaxCmu = Column('MaxCmu', Integer)
MaxNonCmu = Column('MaxNonCmu', Integer)
AgeMini = Column('AgeMini', Integer)
AgeMaxi = Column('AgeMaxi', Integer)
IdSemaineType = Column('IdSemaineType',
ForeignKey('semaine_type.IdSemaineType'))
IdDossier = Column('IdDossier', ForeignKey('dossier.IdDossier'))
IdLieu = Column('IdLieu', ForeignKey('lieu.IdLieu'))
NomRemplacement = Column('NomRemplacement', String)
*PlageTypeActe = relationship(Acte, secondary=PlageTypeActe,
backref=plage_type)*
def __init__(self,
Debut=datetime.datetime.today(),
Fin=datetime.datetime.today(),
JourSemaine=0,
EtatPrecedent=,
EtatCourant=,
EtatSuivant=,
Max=0,
SurchargeVad=0,
StopVad=datetime.time(),
Creation=datetime.datetime.today(),
Modification=datetime.datetime.today(),
MaxDejaVenu=-1,
MaxJamaisVenu=-1,
MaxActif=-1,
MaxInactif=-1,
MaxPatient=-1,
MaxDelegue=-1,
MaxCmu=-1,
MaxNonCmu=-1,
AgeMini = 0,
AgeMaxi = 0,
IdSemaineType = 0,
IdDossier = 0,
IdLieu = 0,
NomRemplacement =
):
self.Debut = Debut
self.Fin = Fin
self.JourSemaine = JourSemaine
self.EtatPrecedent = EtatPrecedent
self.EtatCourant = EtatCourant
self.EtatSuivant = EtatSuivant
self.Max = Max
self.SurchargeVad = SurchargeVad
self.StopVad = StopVad
self.Creation = Creation
self.Modification = Modification
self.MaxDejaVenu = MaxDejaVenu
self.MaxJamaisVenu = MaxJamaisVenu
self.MaxActif = MaxActif
self.MaxInactif = MaxInactif
self.MaxPatient = MaxPatient
self.MaxDelegue = MaxDelegue
self.MaxCmu = MaxCmu
self.MaxNonCmu = MaxNonCmu
self.AgeMini = AgeMini
self.AgeMaxi = AgeMaxi
self.IdSemaineType = IdSemaineType
self.IdDossier = IdDossier
self.IdLieu = IdLieu
self.NomRemplacement = NomRemplacement
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Attach a string to each results of a query
Okay it works
literal() and literal_column() functions works with sqlite but only
literal() function works with MySQLdb connector
Thanks a lot !)
2010/12/9 Michael Bayer mike...@zzzcomputing.com
On Dec 9, 2010, at 11:14 AM, Christian Démolis wrote:
Could not locate column in row for column '%s' % key)
sqlalchemy.exc.NoSuchColumnError: 'Could not locate column in row for
column \'
lng\''
I don t want to call a column by litteral name.
you're thinking of the label() function. literal() and literal_column()
produce standalone expressions, and are documented at:
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=literal_column#sqlalchemy.sql.expression.literal
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=literal_column#sqlalchemy.sql.expression.literal_column
Below is an example application illustrating their use in the context you
describe. Hope this helps.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
data = Column(String(50))
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
sess = Session(engine)
sess.add_all([Foo(data='f1'), Foo(data='f2'), Foo(data='f3')])
sess.commit()
assert sess.query(
Foo.id,
Foo.data,
literal_column('technique_number_one'),
literal('technique_number_two')).\
order_by(Foo.id).all() == [
(1, 'f1', technique_number_one, technique_number_two),
(2, 'f2', technique_number_one, technique_number_two),
(3, 'f3', technique_number_one, technique_number_two),
]
I want to attach an arbitrary string of my choice to each row results of my
query.
Look the screenshot to see what i want.
Thx
2010/12/9 Michael Bayer mike...@zzzcomputing.com
you want to use literal_column('coucou') to achieve that effect, or if a
bind is OK then just use literal(coucou).
On Dec 9, 2010, at 8:30 AM, Christian Démolis wrote:
Hi,
SELECT IdActe, coucou
FROM `acte`
WHERE 1
How to attach a string to each result of a query ?
i try that but it doesn't work :(
s = model.session.query(milieu, model.Dossier.NomEntreprise,
model.Dossier.AgendaSensGroupement, model.Dossier.AgendaUnite, *Coucou*
)
Thx
Chris
--
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.comsqlalchemy%2bunsubscr...@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.
Clipboard01.jpg
--
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.comsqlalchemy%2bunsubscr...@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] Attach a string to each results of a query
Hi, SELECT IdActe, coucou FROM `acte` WHERE 1 How to attach a string to each result of a query ? i try that but it doesn't work :( s = model.session.query(milieu, model.Dossier.NomEntreprise, model.Dossier.AgendaSensGroupement, model.Dossier.AgendaUnite, *Coucou*) Thx Chris -- 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] Strange behaviour with func.timediff?
Hi, I found a usecase that is illogical Here is the first code and his result in term. All is *False* milieu = model.aliased(model.Plage) s2 = model.session.query( milieu, model.func.timediff(milieu.Fin, milieu.Debut), *model.func.timediff(milieu.Fin, milieu.Debut)datetime.timedelta(minutes=self.duree_cible)* ) for e in s2: print e (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600), False) (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, 12, 6, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, 12, 7, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8, 0),datetime.datetime(2010, 12, 21, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14, 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8, 0),datetime.datetime(2010, 12, 27, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14, 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8, 0),datetime.datetime(2010, 12, 28, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14, 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011, 1, 3, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14, 0),datetime.datetime(2011, 1, 3, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011, 1, 4, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(618L,'L',datetime.datetime(2011, 1, 4, 14, 0),datetime.datetime(2011, 1, 4, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(619L,'L',datetime.datetime(2011, 1, 10, 8, 0),datetime.datetime(2011, 1, 10, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(620L,'L',datetime.datetime(2011, 1, 10, 14, 0),datetime.datetime(2011, 1, 10, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(621L,'L',datetime.datetime(2011, 1, 11, 8, 0),datetime.datetime(2011, 1, 11, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(622L,'L',datetime.datetime(2011, 1, 11, 14, 0),datetime.datetime(2011, 1, 11, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(623L,'L',datetime.datetime(2011, 1, 17, 8, 0),datetime.datetime(2011, 1, 17, 10, 0)), datetime.timedelta(0, 7200), False)
Re: [sqlalchemy] Strange behaviour with func.timediff?
Thanks, Ok, i use MySQLdb as database driver... I solved the problem with func.unix_timestamp def lg(self): extrait les créneaux libres groupés xref = time.time() haut = model.aliased(model.Plage) milieu = model.aliased(model.Plage) bas = model.aliased(model.Plage) s = model.session.query(milieu) s = self.filtrage_requete(s, milieu) s = s.filter(milieu.EtatCourant==L) *s = s.filter(model.func.unix_timestamp(milieu.Fin)-(self.duree_cible*60)=model.func.unix_timestamp(milieu.Debut)) * if param.dossier_concerne.AgendaGroupement and param.dossier_concerne.AgendaSensGroupement==1: # on groupe par le haut uniquement s = s.filter(haut.Fin==milieu.Debut) s = s.filter(haut.EtatCourant==O) if param.dossier_concerne.AgendaGroupement and param.dossier_concerne.AgendaSensGroupement==0: # on groupe par le bas uniquement s = s.filter(bas.Debut==milieu.Fin) s = s.filter(bas.EtatCourant==O) s = s.order_by(milieu.Debut) s = s.limit(100) s = s.all() print lg, time.time()-xref return s As u can see, your wonderful library is used in many projects around the world !) You're the best! 2010/11/18 Michael Bayer mike...@zzzcomputing.com This has everything to do with the database driver in use and the database itself. Acceptance of a Python timedelta object by the DBAPI and proper interpretation of it against the result of a TIMEDIFF call (which is MySQL specific) is not a given on most DBAPIs. For cases like these your best bet is to first look at all SQL being emitted, and try to stick to foolproof types like ints as far as what you pass to the database. As for SQLAlchemy, we're just passing SQL strings and Python constructs to cursor.execute(). On Nov 18, 2010, at 5:04 AM, Christian Démolis wrote: Hi, I found a usecase that is illogical Here is the first code and his result in term. All is *False* milieu = model.aliased(model.Plage) s2 = model.session.query( milieu, model.func.timediff(milieu.Fin, milieu.Debut), *model.func.timediff(milieu.Fin, milieu.Debut)datetime.timedelta(minutes=self.duree_cible)* ) for e in s2: print e (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600), False) (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, 12, 6, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, 12, 7, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200
[sqlalchemy] TIMEDIFF and SQLAlchemy
Hi,
Do you know how to do this query with sqlalchemy?
*SELECT
Id, TIMEDIFF( End, Start)
FROM
plage
WHERE
TIMEDIFF(End,Start)=TIME('02:20:00');*
In my model, Start and End are DateTime
Start = Column('Start', DateTime)
End = Column('End', DateTime)
--
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] Very strange behaviour in SqlAlchemy (maybe a bug)
Thanks
2010/10/14 Michael Bayer mike...@zzzcomputing.com
On Oct 13, 2010, at 10:48 AM, Christian Démolis wrote:
Hi,
q = model.session.query(
#
model.Collaborateur.LesIns.any(model.or_(model.Instruction.FinValiditetime.time(),
model.Instruction.FinValidite==None)),
model.Collaborateur.Fonction
)
q = q.limit(5)
print str(q)
for e in q.all() :
print Fonction, e.Fonction
This is the result (result1.jpg)
q = model.session.query(
model.Collaborateur.LesIns.any(model.or_(model.Instruction.FinValiditetime.time(),
model.Instruction.FinValidite==None)),
model.Collaborateur.Fonction
)
q = q.limit(5)
print str(q)
for e in q.all() :
print Fonction, e.Fonction
The SQL is correct. The any() has no label and is throwing off the label
names - this is a bug and is fixed in rd67812029db9, downloadable at
http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz . Otherwise,
apply any(...).label('some label') so that e.Fonction targets the column you
want.
--
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.comsqlalchemy%2bunsubscr...@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] Very strange behaviour in SqlAlchemy (maybe a bug)
Hello, I actually try to migrate to SqlAlchemy 0.6.4 (before i was in 0.5.2) One of my query seems to not work properly in this new version It seems to be a bug in Sql Alchemy because of this part of my query *model.Collaborateur.LesIns.any(model.or_(model.Instruction.FinValiditetime.time(), model.Instruction.FinValidite==None))* All the field which are after this part seems to be corrupted the n+1 field seems to return the result of the n field n+2 field return the result of n+1 field... s = model.session.query(model.Collaborateur.IdCollaborateur, model.Collaborateur.Nom, model.Collaborateur.Prenom, model.Collaborateur.Type, model.Collaborateur.Civilite, model.Collaborateur.Titre, model.Collaborateur.Inactif, model.Collaborateur.MotCle, model.Collaborateur.IdProfilIca , model.Collaborateur.Responsable, model.Collaborateur.LesIns.any(model.or_(model.Instruction.FinValiditetime.time(), model.Instruction.FinValidite==None)), model.Collaborateur.TypeEnvoi, model.Collaborateur.Fonction ) s = s.filter(model.Collaborateur.IdDossier==983) # On commence par extraire de la bdd les collaborateurs qui font partie du dossier for e in s: print Fonction, e.Fonction, TypeEnvoi, e.TypeEnvoi All works better when i put the green part at the end of the query (after the red part) but i wonder why it works before in 0.5.2 and not in 0.6.4??? Thx Chris -- 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] Sql Alchemy non Transactionnal
Thx a lot, now i flush every time i add, merge or delete object. I hope it will help to prevent error transaction has closed during query (the connection is unstable between the code and the bdd) def add_object(objet): Persiste un élément en base de données if not objet: return model.session.add(objet) model.session.flush() return objet def add_objects(objets) : Persiste des éléments en base de données if not objets: return # Si la liste des objets passés en paramètre est vide on ne fait rien for objet in objets: # On ajoute les objets model.session.add(objet)#, dont_load=loading) model.session.flush() def merge_object(objet, loading=False): Cf principe ajout base if not objet: return model.session.merge(objet, dont_load=loading) model.session.flush() 2010/9/20 Michael Bayer mike...@zzzcomputing.com On Sep 20, 2010, at 10:37 AM, Christian Démolis wrote: Hi, Can i use SqlAlchemy in a non transactionnal way ? How can i do it? Session() has an autocommit=True flag that will commit every flush() operation immediately and not retain an open transaction during usage. You may very well want to set autoflush=False and call flush() manually with this mode. Note that there are still 'transactions' in use here, as the flush() will always place its multiple operations into a transaction. -- 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.comsqlalchemy%2bunsubscr...@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] Filter on relation???
Ok, i already use session.query, no problem with my code. It works very well at the moment. I m just curious about advanced functionnality of my favourite orm :) Thanks to you, i have discovered that we can : - define associationproxy - define a properties which returns the result of a particular queries i will do experimentations now. Thanks for your patience and sry for my poor english 2009/11/24 Michael Bayer mike...@zzzcomputing.com Conor wrote: I cannot speak to how feasible it is to add this feature to a relation, but if it is added then it would likely be built on top of query options since there is so much overlap. I think your multiple definition approach is possible, but it is not something I would want to attempt. I'd like to add that the most flexible way of all to get any information you like from an object attribute is to just use a query() from scratch, acquired via object_session(self). You're already asking for read-only information, therefore you don't need the peristence side of relation(), and as far as loading everything eagerly via join that can still be accomplished just using a join() + extra cols in query() if you really needed it. for reference the general idea of entirely custom properties is at: http://www.sqlalchemy.org/docs/05/mappers.html#building-query-enabled-properties -- 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.comsqlalchemy%2bunsubscr...@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] Filter on relation???
Is it possible to put a filter on a relation in the declaration?
Example :
LeNomDuUtilisateur = relation(Utilisateur, filter_by=Utilisateur.Login,
backref=backref('verrouillage'))
--
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] Filter on relation???
Thx for your answer Thomas
I want the attribute to not return the complete object just some of the
attribute of the other table.
In my case Utilisateur has some attributes : Login, Nom, Prenom, ...
I dont want Verouillage.LeNomDuUtilisateur to return complete object
Utilisateur
I want it to return only Login attributes.
Primaryjoin seems to work on condition (where clause) and not on select
condition (select ... in a query)
Is it possible to limit selected attributes in the relation?
2009/11/24 Tefnet Developers - Tomasz Jezierski develop...@tefnet.pl
Dnia 2009-11-24, Wt o godzinie 11:18 +0100, Christian Démolis pisze:
Is it possible to put a filter on a relation in the declaration?
Example :
LeNomDuUtilisateur = relation(Utilisateur,
filter_by=Utilisateur.Login, backref=backref('verrouillage'))
I'm not sure what exactly your example means.. but if you want extra
filters on relation, you can change primaryjoin
http://www.sqlalchemy.org/docs/05/mappers.html#specifying-alternate-join-conditions-to-relation
something like:
LeNomDuUtilisateur = relation(Utilisateur,
primaryjoin=sqlalchemy.and_(defaultjoincondition,
yourextrafiltercondition), backref='verrouillage')
Tomasz Jezierski
Tefnet
www.tefnet.pl
--
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.comsqlalchemy%2bunsubscr...@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] Filter on relation???
Cool, it's very powerful, it will allow me to save my Bandwidth because i
take just what i want and not the entire object.
Thanks Conor
2009/11/24 Conor conor.edward.da...@gmail.com
Christian Démolis wrote:
Thx for your answer Thomas
I want the attribute to not return the complete object just some of
the attribute of the other table.
In my case Utilisateur has some attributes : Login, Nom, Prenom, ...
I dont want Verouillage.LeNomDuUtilisateur to return complete object
Utilisateur
I want it to return only Login attributes.
Primaryjoin seems to work on condition (where clause) and not on
select condition (select ... in a query)
Is it possible to limit selected attributes in the relation?
You can use sqlalchemy.ext.associationproxy.association_proxy to turn a
collection of related objects into a collection of related object
attributes:
_LeUtilisateur = relation(Utilisateur, backref=backref('verrouillage'))
LeNomDuUtilisateur = association_proxy('_LeUtilisateur', 'Login')
-Conor
2009/11/24 Tefnet Developers - Tomasz Jezierski develop...@tefnet.pl
mailto:develop...@tefnet.pl
Dnia 2009-11-24, Wt o godzinie 11:18 +0100, Christian Démolis pisze:
Is it possible to put a filter on a relation in the declaration?
Example :
LeNomDuUtilisateur = relation(Utilisateur,
filter_by=Utilisateur.Login, backref=backref('verrouillage'))
I'm not sure what exactly your example means.. but if you want extra
filters on relation, you can change primaryjoin
http://www.sqlalchemy.org/docs/05/mappers.html#specifying-alternate-join-conditions-to-relation
something like:
LeNomDuUtilisateur = relation(Utilisateur,
primaryjoin=sqlalchemy.and_(defaultjoincondition,
yourextrafiltercondition), backref='verrouillage')
Tomasz Jezierski
Tefnet
www.tefnet.pl http://www.tefnet.pl
--
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.comsqlalchemy%2bunsubscr...@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] Filter on relation???
I m disappointed
I already use session.query everywhere in my code.
Maybe this mechanism of prefiltered select relation without dl all the
object (proxy) will appear in a future version of SQL Alchemy (we already
can indicate the order_by, why not the select column)...
I can t use the defer query option because i need to obtain my object in
multiple ways. (entire object, only some attribute...)
What do you think of that (it s just an idea)?
Declare an object twice (one with all the attribute and one with only some
attributes).
In my relation, when i want to access to only some attribute i bind the
relation to the light object
It s a brutal method, i dont think if it can work...
2009/11/24 Conor conor.edward.da...@gmail.com
Christian Démolis wrote:
Cool, it's very powerful, it will allow me to save my Bandwidth
because i take just what i want and not the entire object.
Thanks Conor
I think I gave you the wrong impression: association_proxy does not
replace the original relation in any way. You are still using a full
SELECT when using the association_proxy, because the association_proxy
does its thing outside of SQL.
If you need to pick and choose columns in the SELECT statement, I would
advise doing that in the query:
q = session.query(Utilisateur.Login)
q = q.join(Utilisateur.verouillage)
q = q.filter(Verouillage.id == some_id)
logins = q.all()
Alternatively, you can look into using the sqlalchemy.orm.defer query
option to tell the query to NOT load a given column when loading a given
class. More info at
http://www.sqlalchemy.org/docs/05/reference/orm/query.html#query-options.
-Conor
2009/11/24 Conor conor.edward.da...@gmail.com
mailto:conor.edward.da...@gmail.com
Christian Démolis wrote:
Thx for your answer Thomas
I want the attribute to not return the complete object just some of
the attribute of the other table.
In my case Utilisateur has some attributes : Login, Nom, Prenom,
...
I dont want Verouillage.LeNomDuUtilisateur to return complete
object
Utilisateur
I want it to return only Login attributes.
Primaryjoin seems to work on condition (where clause) and not on
select condition (select ... in a query)
Is it possible to limit selected attributes in the relation?
You can use sqlalchemy.ext.associationproxy.association_proxy to
turn a
collection of related objects into a collection of related object
attributes:
_LeUtilisateur = relation(Utilisateur,
backref=backref('verrouillage'))
LeNomDuUtilisateur = association_proxy('_LeUtilisateur', 'Login')
-Conor
2009/11/24 Tefnet Developers - Tomasz Jezierski
develop...@tefnet.pl mailto:develop...@tefnet.pl
mailto:develop...@tefnet.pl mailto:develop...@tefnet.pl
Dnia 2009-11-24, Wt o godzinie 11:18 +0100, Christian
Démolis pisze:
Is it possible to put a filter on a relation in the
declaration?
Example :
LeNomDuUtilisateur = relation(Utilisateur,
filter_by=Utilisateur.Login, backref=backref('verrouillage'))
I'm not sure what exactly your example means.. but if you
want extra
filters on relation, you can change primaryjoin
http://www.sqlalchemy.org/docs/05/mappers.html#specifying-alternate-join-conditions-to-relation
something like:
LeNomDuUtilisateur = relation(Utilisateur,
primaryjoin=sqlalchemy.and_(defaultjoincondition,
yourextrafiltercondition), backref='verrouillage')
Tomasz Jezierski
Tefnet
www.tefnet.pl http://www.tefnet.pl http://www.tefnet.pl
--
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.comsqlalchemy%2bunsubscr...@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: problem with like
Thx Kyle and Conor, i finally dit that s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier) # s = s.filter(ContactTel.Tel.like(NumeroApparu)) s = s.filter(ContactTel.Tel.op(regexp)((.)*.join(list(NumeroApparu))) ) i construct a regular expression from the telephone number and do the regexp search sql 2009/10/14 Kyle Schaffrick k...@raidi.us On Mon, 12 Oct 2009 13:47:19 -0500 Conor conor.edward.da...@gmail.com wrote: Christian Démolis wrote: Hi, The idea of creating another column is good but it will multiplicate the size of my table by 2 for nothing. Is it possible to use MYSQL regular expression search with sql alcmehy? If yes, what is the command? MySQL supports RLIKE/REGEXP operators; you can see how to use them in SQLAlchemy in this thread: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7c6abe2ab9d5061 Be aware that regexp matching cannot utilize indexes, so you may have performance problems. If you want to get fancy, you can try creating an index on the expression REPLACE(REPLACE(tel_column, ' ', ''), '.', '') which should create an index on the normalized phone numbers. In theory, as long as your queries use the exact same function sequence as the index, the index will be scanned instead of the full table. I don't know what limitations MySQL has in this regard. Even if it does work, the only real advantage it provides over using another column is the normalized form is hidden in the index instead of the table. Hope it helps, -Conor Indeed this problem is easily solved with expression indexes (a.k.a. functional or calculated indexes). Unfortunately MySQL does not appear to support them. Their suggested workaround is to add a column to store the precomputed expression, with a trigger to keep it up to date, and index that column. This also means the optimization is not transparent: you have to explicitly use the precomputed column in your query. It seems expression indexes have been MySQL's todo list since at least 2007 :( You can of course still use RLIKE/REGEXP instead of adding this redundant column, but you'll get a full table scan every time. A classic space/time performance tradeoff :) -Kyle 2009/10/10 Andre Stechert stech...@gmail.com mailto:stech...@gmail.com This is not really a sqlalchemy question, but the quick answer is that you need to convert both your indexed data and your queries to the same normal form. In your example, you appear to be correctly stripping spaces and periods in your query. If you haven't done that in the database, then you should do it there, too. If you need to preserve the original formatting of the telephone number column, then create another column that contains the stripped phone numbers. You probably also want to put an index on that column. Lastly, a minor note on the sample code: you appear to be missing a % operator in your LIKE query. Cheers, Andre On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis christiandemo...@gmail.com mailto:christiandemo...@gmail.com wrote: Hi everybody, I m stuck with a query about telephone number : I want to find in my database all the contact who have a telephone number. The difficulty is that some number in the database can have space or . between numbers example : 06.06.50.44.11 or 45 87 12 45 65 This my query with like but it s not what i want because i ignore telephone number who have special chars NumeroApparu = 064544 s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the doc but lack of explication... --~--~-~--~~~---~--~~ 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: problem with like
Hi, The idea of creating another column is good but it will multiplicate the size of my table by 2 for nothing. Is it possible to use MYSQL regular expression search with sql alcmehy? If yes, what is the command? 2009/10/10 Andre Stechert stech...@gmail.com This is not really a sqlalchemy question, but the quick answer is that you need to convert both your indexed data and your queries to the same normal form. In your example, you appear to be correctly stripping spaces and periods in your query. If you haven't done that in the database, then you should do it there, too. If you need to preserve the original formatting of the telephone number column, then create another column that contains the stripped phone numbers. You probably also want to put an index on that column. Lastly, a minor note on the sample code: you appear to be missing a % operator in your LIKE query. Cheers, Andre On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis christiandemo...@gmail.com wrote: Hi everybody, I m stuck with a query about telephone number : I want to find in my database all the contact who have a telephone number. The difficulty is that some number in the database can have space or . between numbers example : 06.06.50.44.11 or 45 87 12 45 65 This my query with like but it s not what i want because i ignore telephone number who have special chars NumeroApparu = 064544 s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the doc but lack of explication... --~--~-~--~~~---~--~~ 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] Documentation please
Hello, class sqlalchemy.sql.expression.ColumnOperators¶ Defines comparison and math operations. __init__()¶ x.__init__(...) initializes x; see x.__class__.__doc__ for signature asc()¶ between(cleft, cright)¶ collate(collation)¶ concat(other)¶ contains(other, **kwargs)¶ desc()¶ distinct()¶ endswith(other, **kwargs)¶ ilike(other, escape=None)¶ in_(other)¶ like(other, escape=None)¶ match(other, **kwargs)¶ op(opstring)¶ operate(op, *other, **kwargs)¶ reverse_operate(op, other, **kwargs)¶ startswith(other, **kwargs)¶ timetuple¶ Hack, allows datetime objects to be compared on the LHS. * Where can i found complete documentation about these commands?* --~--~-~--~~~---~--~~ 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] problem with like
Hi everybody, I m stuck with a query about telephone number : I want to find in my database all the contact who have a telephone number. The difficulty is that some number in the database can have space or . between numbers example : 06.06.50.44.11 or 45 87 12 45 65 This my query with like but it s not what i want because i ignore telephone number who have special chars NumeroApparu = 064544 s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) i saw *class *sqlalchemy.sql.expression.ColumnOperators¶http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/expressions.html?highlight=like#sqlalchemy.sql.expression.ColumnOperatorsin the doc but lack of explication... --~--~-~--~~~---~--~~ 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: Orm slow to update why?
*Hello M. Bayer
Ok i understand now what it is strange
My configuration
I m on windows xp pro sp3
Python is 2.5.2
SqlAlchemy is 0.5.6
Dell optiplex 755
Intel core 2 duo e8300
2go ram
**
I dont pass any paremeters in create_engine, is it wrong?*
chaine =
'mysql://'+ParametresDeConnection[Identifiant]+:+ParametresDeConnection[MotDePasse]+'@'+ParametresDeConnection['Ip']+'/'+ParametresDeConnection[BaseDeDonnees]
engine = create_engine(chaine)
*
This is the final part of my declaration*
class Query(Query):
def __init__(self, *arg, **kw):
self._populate_existing = True
super(Query, self).__init__(*arg, **kw)
Session = scoped_session(sessionmaker(autocommit=True, bind=engine,
query_cls=Query))
session = Session()
import MySQLdb
db = MySQLdb.connection(host=192.168.0.110, user=apm, passwd=apm,
db=azeane)
print AvecMySQlDB
xref = time.time()
for x in xrange(100):
db.query(UPDATE utilisateur SET Dispo=+str(x%2)+ WHERE
IdUtilisateur=1)
r = db.store_result()
print Update 1000 MySQLdb, time.time()-xref
xreftotal = time.time()
for x in xrange(100):
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==1).update({'Dispo':x%2},
False)
print Update 1000 SqlAlchemy, time.time()-xref
*As u can see, i m not in transactional mode.* *I add a new benchmark and i
execute it with local database and tunisia database
Local results
Update 1000 MySQLdb 0.016324249
Update 1000 SqlAlchemy 0.235000133514
Tunisia results
Update 1000 MySQLdb 0.016324249
Update 1000 SqlAlchemy 31.0309998989
It s crazy ^^
I don t know why my station is slow? Do u have an idea?*
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
Maybe because the database is in Tunisia and my Computer in France.
I don t use sqlite, i use MySQL.
I just did a test on internet in Tunisia, 39kbits/sec upload and
417kbits/sec
right but, you had these results:
MySQLdb - .09 seconds
ORM - .3 seconds
so, network overhead of sending update string to tunisia, is at most
.09 seconds. SQLAlchemy also pulls the cursor.rowcount back, so that
perhaps adds network overhead as well (and you would see this in the
profiling results), although that should be tiny. so .21 seconds of
overhead approximately would appear to be spent locally.whereas on my
system within .21 seconds I can issue that same amount of work a few
thousand times. the simple ORM examples you have should not be issuing
any other SQL statements.
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
With debug mode it seems to take 0.15 second, my timer print 0.45 s
i don t know why this difference?
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
UPDATE
utilisateur SET `Dispo`=%s WHERE utilisateur.`IdU
tilisateur` = %s
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
[0,
1L]
2009-10-01 17:00:38,743 INFO sqlalchemy.engine.base.Engine.0x...7f50
COMMIT
With ORM force update 0.45368665
On my workstation which is an intel mac, I can run 1000
Session.execute(update...) against a sqlite memory db statements in
.15
seconds, using the simple time.time() approach to measure. The sqlite
cursor directly, which is against an empty table so is insanely fast,
can
run 1000 in .01 seconds, so that is .14 seconds of overhead within SQLA.
In your case, you're getting .2-.3 seconds of overhead just for *one*
statement, subtracting what your database call takes natively. Meaning
that while I can execute around 8000 Session.execute() statements per
second, you can execute less than five. That seems very strange.
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Michael Bayer wrote:
Christian Démolis wrote:
Hello,
I tried all the method to compare the different methods :
Here's a decorator I would advise using:
http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
Also I would advise testing this as well. Session.execute() creates
a
text() construct which does some regular expression matching that you
don't need:
engine.execute(update table set foo=bar)
*TEST CODE*
xref = time.time()
self.UtilisateurCourant.Dispo = 1
session.merge(self.UtilisateurCourant, dont_load=True)
session.flush()
print With ORM dont_load, time.time()-xref
xref = time.time()
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==self.UtilisateurCourant.IdUtilisateur).update({'Dispo':0},
False)
print With ORM force update, time.time()-xref
xref = time.time()
if self.UtilisateurCourant.IdUtilisateur:
session.execute(UPDATE utilisateur SET Dispo=0
[sqlalchemy] Re: Orm slow to update why?
I does a mistake in my last, that is the results (error in parameters of
mysqldb connection)
Local database
Update 1000 MySQLdb 0.0319998264313
Update 1000 SqlAlchemy 0.265999794006
France-Tunisia
Update 1000 MySQLdb 10.391324
Update 1000 SqlAlchemy 42.157924
Sorry
2009/10/2 Christian Démolis christiandemo...@gmail.com
*Hello M. Bayer
Ok i understand now what it is strange
My configuration
I m on windows xp pro sp3
Python is 2.5.2
SqlAlchemy is 0.5.6
Dell optiplex 755
Intel core 2 duo e8300
2go ram
**
I dont pass any paremeters in create_engine, is it wrong?*
chaine =
'mysql://'+ParametresDeConnection[Identifiant]+:+ParametresDeConnection[MotDePasse]+'@'+ParametresDeConnection['Ip']+'/'+ParametresDeConnection[BaseDeDonnees]
engine = create_engine(chaine)
*
This is the final part of my declaration*
class Query(Query):
def __init__(self, *arg, **kw):
self._populate_existing = True
super(Query, self).__init__(*arg, **kw)
Session = scoped_session(sessionmaker(autocommit=True, bind=engine,
query_cls=Query))
session = Session()
import MySQLdb
db = MySQLdb.connection(host=192.168.0.110, user=apm, passwd=apm,
db=azeane)
print AvecMySQlDB
xref = time.time()
for x in xrange(100):
db.query(UPDATE utilisateur SET Dispo=+str(x%2)+ WHERE
IdUtilisateur=1)
r = db.store_result()
print Update 1000 MySQLdb, time.time()-xref
xreftotal = time.time()
for x in xrange(100):
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==1).update({'Dispo':x%2},
False)
print Update 1000 SqlAlchemy, time.time()-xref
*As u can see, i m not in transactional mode.* *I add a new benchmark and
i execute it with local database and tunisia database
Local results
Update 1000 MySQLdb 0.016324249
Update 1000 SqlAlchemy 0.235000133514
Tunisia results
Update 1000 MySQLdb 0.016324249
Update 1000 SqlAlchemy 31.0309998989
It s crazy ^^
I don t know why my station is slow? Do u have an idea?*
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
Maybe because the database is in Tunisia and my Computer in France.
I don t use sqlite, i use MySQL.
I just did a test on internet in Tunisia, 39kbits/sec upload and
417kbits/sec
right but, you had these results:
MySQLdb - .09 seconds
ORM - .3 seconds
so, network overhead of sending update string to tunisia, is at most
.09 seconds. SQLAlchemy also pulls the cursor.rowcount back, so that
perhaps adds network overhead as well (and you would see this in the
profiling results), although that should be tiny. so .21 seconds of
overhead approximately would appear to be spent locally.whereas on my
system within .21 seconds I can issue that same amount of work a few
thousand times. the simple ORM examples you have should not be issuing
any other SQL statements.
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
With debug mode it seems to take 0.15 second, my timer print 0.45 s
i don t know why this difference?
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
UPDATE
utilisateur SET `Dispo`=%s WHERE utilisateur.`IdU
tilisateur` = %s
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
[0,
1L]
2009-10-01 17:00:38,743 INFO sqlalchemy.engine.base.Engine.0x...7f50
COMMIT
With ORM force update 0.45368665
On my workstation which is an intel mac, I can run 1000
Session.execute(update...) against a sqlite memory db statements in
.15
seconds, using the simple time.time() approach to measure. The sqlite
cursor directly, which is against an empty table so is insanely fast,
can
run 1000 in .01 seconds, so that is .14 seconds of overhead within
SQLA.
In your case, you're getting .2-.3 seconds of overhead just for *one*
statement, subtracting what your database call takes natively.
Meaning
that while I can execute around 8000 Session.execute() statements per
second, you can execute less than five. That seems very strange.
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Michael Bayer wrote:
Christian Démolis wrote:
Hello,
I tried all the method to compare the different methods :
Here's a decorator I would advise using:
http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
Also I would advise testing this as well. Session.execute() creates
a
text() construct which does some regular expression matching that
you
don't need:
engine.execute(update table set foo=bar)
*TEST CODE*
xref = time.time()
self.UtilisateurCourant.Dispo = 1
session.merge(self.UtilisateurCourant,
dont_load=True)
session.flush()
print With ORM dont_load, time.time()-xref
xref
[sqlalchemy] Re: Orm slow to update why?
Hello,
I tried all the method to compare the different methods :
*TEST CODE*
xref = time.time()
self.UtilisateurCourant.Dispo = 1
session.merge(self.UtilisateurCourant, dont_load=True)
session.flush()
print With ORM dont_load, time.time()-xref
xref = time.time()
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==self.UtilisateurCourant.IdUtilisateur).update({'Dispo':0},
False)
print With ORM force update, time.time()-xref
xref = time.time()
if self.UtilisateurCourant.IdUtilisateur:
session.execute(UPDATE utilisateur SET Dispo=0 WHERE
IdUtilisateur=+str(self.UtilisateurCourant.IdUtilisateur))
print With ORM pure SQL, time.time()-xref
import MySQLdb
db = MySQLdb.connection(host=192.168.45.28, user=apm,
passwd=apm, db=test_christian)
xref = time.time()
db.query(UPDATE utilisateur SET Dispo=1 WHERE
IdUtilisateur=1)
r = db.store_result()
print With MySQLdb without ORM, time.time()-xref
*TEST RESULTS*
With ORM dont_load 0.45368665
With ORM force update 0.29631335
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0939998626709
With ORM dont_load 0.452999830246
With ORM force update 0.297000169754
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0939998626709
With ORM dont_load 0.45368665
With ORM force update 0.29631335
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0940001010895
2009/9/30 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
Thx for your answer.
MakeReleased is a method of com object windows agent (self.agent =
DispatchWithEvents('CosmoAgent.clsCCAgent', Evenement))
It takes 0 second to execute as we can see in the execute print
yes I realized later there were two blocks of timer calls. The inclusion
of that code made the example harder to read.
In particular using session.merge() with an object will issue a SELECT
first to locate the current row. this is likely the cause of the
slowness in this specific case since you seem to have a slow network
(profiling would reveal this too). Passing dont_load=True to merge()
will skip the SELECT step and trust that the state you are passing it is
the state that is within the database.
Orm does additional steps
is it possible to force him update only one attribute of the object?
you may say:
session.query(MyObject).filter(some_criterion).update({'attrname':somenewvalue},
False)
which will issue an UPDATE statement matching the criterion. this is the
fastest way by far using the ORM only.
Changing the False to evaluate or expire will also update or expire
the state of your ORM instance - but that will add some overhead.
--~--~-~--~~~---~--~~
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] Bypass checking to database structure (metadata.create_all)
Hi again, Is there any way to avoid checking database structure during the metadata.create_all declaration's phase? It can be good to check when we are in test phase but when we are in production and we are sure of our model, it can be good to bypass create_all checking to database. --~--~-~--~~~---~--~~ 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: Bypass checking to database structure (metadata.create_all)
Thx Simon, I tried Base.metadata.create_all(engine, checkfirst=False) but it throws an error. When checkfirst is True, the declaration works. I don t understand... Maybe orm needs additional information in declarative classes when checkfirst=False because orm doesn t look in database in this case? These is the error : Z:\python Declaration.py Le temps de chargement des modules SQL ALCHEMY 0.25 Le temps de dÚclaration SQL ALCHEMY 0.156000137329 Traceback (most recent call last): File Declaration.py, line 1435, in module Base.metadata.create_all(engine, checkfirst=False) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sche ma.py, line 1796, in create_all bind.create(self, checkfirst=checkfirst, tables=tables) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 1129, in create self._run_visitor(self.dialect.schemagenerator, entity, connection=connectio n, **kwargs) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 1158, in _run_visitor visitorcallable(self.dialect, conn, **kwargs).traverse(element) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 89, in traverse return traverse(obj, self.__traverse_options__, self._visitor_dict) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 200, in traverse return traverse_using(iterate(obj, opts), obj, visitors) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 194, in traverse_using meth(target) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ compiler.py, line 831, in visit_metadata self.traverse_single(table) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 79, in traverse_single return meth(obj) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ compiler.py, line 870, in visit_table self.execute() File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 1812, in execute return self.connection.execute(self.buffer.getvalue()) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 888, in _execute_text return self.__execute_context(context) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0 ], context=context) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidat ed=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, Erreur de syntaxe pr \xe8s de ' \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlongitude VARCHAR, \n\teloi gn' \xe0 la ligne 3) '\nCREATE TABLE maps_ville (\n\t`IdVille` INTEGER NOT NULL AUTO_INCREMENT, \n\tnom VARCHAR, \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlong itude VARCHAR, \n\teloignement VARCHAR, \n\turl VARCHAR, \n\tPRIMARY KEY (`IdVil le`)\n)\n\n' () 2009/10/1 King Simon-NFHD78 simon.k...@motorola.com -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Christian Démolis Sent: 01 October 2009 10:40 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Bypass checking to database structure (metadata.create_all) Hi again, Is there any way to avoid checking database structure during the metadata.create_all declaration's phase? It can be good to check when we are in test phase but when we are in production and we are sure of our model, it can be good to bypass create_all checking to database. create_all (and drop_all) have a 'checkfirst' parameter that defaults to True. If you set it to False, SA won't check to see if your tables already exist before issuing the CREATE statements: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData.create_all Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http
[sqlalchemy] Re: Bypass checking to database structure (metadata.create_all)
Ok, i just realize that create_all is useless when database already exist. Starting my application is 6 seconds faster now. Thanks all 2009/10/1 limodou limo...@gmail.com On Thu, Oct 1, 2009 at 6:25 PM, Christian Démolis christiandemo...@gmail.com wrote: Thx Simon, I tried Base.metadata.create_all(engine, checkfirst=False) but it throws an error. When checkfirst is True, the declaration works. I don t understand... Maybe orm needs additional information in declarative classes when checkfirst=False because orm doesn t look in database in this case? These is the error : Z:\python Declaration.py Le temps de chargement des modules SQL ALCHEMY 0.25 Le temps de dÚclaration SQL ALCHEMY 0.156000137329 Traceback (most recent call last): File Declaration.py, line 1435, in module Base.metadata.create_all(engine, checkfirst=False) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sche ma.py, line 1796, in create_all bind.create(self, checkfirst=checkfirst, tables=tables) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 1129, in create self._run_visitor(self.dialect.schemagenerator, entity, connection=connectio n, **kwargs) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 1158, in _run_visitor visitorcallable(self.dialect, conn, **kwargs).traverse(element) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 89, in traverse return traverse(obj, self.__traverse_options__, self._visitor_dict) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 200, in traverse return traverse_using(iterate(obj, opts), obj, visitors) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 194, in traverse_using meth(target) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ compiler.py, line 831, in visit_metadata self.traverse_single(table) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ visitors.py, line 79, in traverse_single return meth(obj) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\sql\ compiler.py, line 870, in visit_table self.execute() File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 1812, in execute return self.connection.execute(self.buffer.getvalue()) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 888, in _execute_text return self.__execute_context(context) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0 ], context=context) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File c:\python25\lib\site-packages\SQLAlchemy-0.5.6-py2.5.egg\sqlalchemy\engi ne\base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidat ed=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, Erreur de syntaxe pr \xe8s de ' \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlongitude VARCHAR, \n\teloi gn' \xe0 la ligne 3) '\nCREATE TABLE maps_ville (\n\t`IdVille` INTEGER NOT NULL AUTO_INCREMENT, \n\tnom VARCHAR, \n\tcp VARCHAR, \n\tlatitude VARCHAR, \n\tlong itude VARCHAR, \n\teloignement VARCHAR, \n\turl VARCHAR, \n\tPRIMARY KEY (`IdVil le`)\n)\n\n' () It seems that there is no length for VARCHAR, the right syntax should be VARCHAR(length), and I also need this problem when I testing in Mysql, but there is no problem in Sqlite. -- I like python! UliPad The Python Editor: http://code.google.com/p/ulipad/ UliWeb simple web framework: http://uliwebproject.appspot.com My Blog: http://hi.baidu.com/limodou --~--~-~--~~~---~--~~ 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: Orm slow to update why?
With debug mode it seems to take 0.15 second, my timer print 0.45 s
i don t know why this difference?
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50 UPDATE
utilisateur SET `Dispo`=%s WHERE utilisateur.`IdU
tilisateur` = %s
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50 [0, 1L]
2009-10-01 17:00:38,743 INFO sqlalchemy.engine.base.Engine.0x...7f50 COMMIT
With ORM force update 0.45368665
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Michael Bayer wrote:
Christian Démolis wrote:
Hello,
I tried all the method to compare the different methods :
Here's a decorator I would advise using:
http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
Also I would advise testing this as well. Session.execute() creates a
text() construct which does some regular expression matching that you
don't need:
engine.execute(update table set foo=bar)
*TEST CODE*
xref = time.time()
self.UtilisateurCourant.Dispo = 1
session.merge(self.UtilisateurCourant, dont_load=True)
session.flush()
print With ORM dont_load, time.time()-xref
xref = time.time()
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==self.UtilisateurCourant.IdUtilisateur).update({'Dispo':0},
False)
print With ORM force update, time.time()-xref
xref = time.time()
if self.UtilisateurCourant.IdUtilisateur:
session.execute(UPDATE utilisateur SET Dispo=0 WHERE
IdUtilisateur=+str(self.UtilisateurCourant.IdUtilisateur))
print With ORM pure SQL, time.time()-xref
import MySQLdb
db = MySQLdb.connection(host=192.168.45.28, user=apm,
passwd=apm, db=test_christian)
xref = time.time()
db.query(UPDATE utilisateur SET Dispo=1 WHERE
IdUtilisateur=1)
r = db.store_result()
print With MySQLdb without ORM, time.time()-xref
*TEST RESULTS*
With ORM dont_load 0.45368665
With ORM force update 0.29631335
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0939998626709
With ORM dont_load 0.452999830246
With ORM force update 0.297000169754
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0939998626709
With ORM dont_load 0.45368665
With ORM force update 0.29631335
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0940001010895
please use the profile module. The call chain from
Session.execute(somestring) down to connection.execute() is definitely
not
three times slower than connection.execute() itself. On my workstation
here I can execute hundreds of ORM queries per second.
2009/9/30 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
Thx for your answer.
MakeReleased is a method of com object windows agent (self.agent =
DispatchWithEvents('CosmoAgent.clsCCAgent', Evenement))
It takes 0 second to execute as we can see in the execute print
yes I realized later there were two blocks of timer calls. The
inclusion
of that code made the example harder to read.
In particular using session.merge() with an object will issue a SELECT
first to locate the current row. this is likely the cause of the
slowness in this specific case since you seem to have a slow network
(profiling would reveal this too). Passing dont_load=True to
merge()
will skip the SELECT step and trust that the state you are passing it
is
the state that is within the database.
Orm does additional steps
is it possible to force him update only one attribute of the object?
you may say:
session.query(MyObject).filter(some_criterion).update({'attrname':somenewvalue},
False)
which will issue an UPDATE statement matching the criterion. this is
the
fastest way by far using the ORM only.
Changing the False to evaluate or expire will also update or
expire
the state of your ORM instance - but that will add some overhead.
--~--~-~--~~~---~--~~
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: Orm slow to update why?
Maybe because the database is in Tunisia and my Computer in France.
I don t use sqlite, i use MySQL.
I just did a test on internet in Tunisia, 39kbits/sec upload and
417kbits/sec
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
With debug mode it seems to take 0.15 second, my timer print 0.45 s
i don t know why this difference?
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50
UPDATE
utilisateur SET `Dispo`=%s WHERE utilisateur.`IdU
tilisateur` = %s
2009-10-01 17:00:38,586 INFO sqlalchemy.engine.base.Engine.0x...7f50 [0,
1L]
2009-10-01 17:00:38,743 INFO sqlalchemy.engine.base.Engine.0x...7f50
COMMIT
With ORM force update 0.45368665
On my workstation which is an intel mac, I can run 1000
Session.execute(update...) against a sqlite memory db statements in .15
seconds, using the simple time.time() approach to measure. The sqlite
cursor directly, which is against an empty table so is insanely fast, can
run 1000 in .01 seconds, so that is .14 seconds of overhead within SQLA.
In your case, you're getting .2-.3 seconds of overhead just for *one*
statement, subtracting what your database call takes natively. Meaning
that while I can execute around 8000 Session.execute() statements per
second, you can execute less than five. That seems very strange.
2009/10/1 Michael Bayer mike...@zzzcomputing.com
Michael Bayer wrote:
Christian Démolis wrote:
Hello,
I tried all the method to compare the different methods :
Here's a decorator I would advise using:
http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677
Also I would advise testing this as well. Session.execute() creates a
text() construct which does some regular expression matching that you
don't need:
engine.execute(update table set foo=bar)
*TEST CODE*
xref = time.time()
self.UtilisateurCourant.Dispo = 1
session.merge(self.UtilisateurCourant, dont_load=True)
session.flush()
print With ORM dont_load, time.time()-xref
xref = time.time()
session.query(Utilisateur).filter(Utilisateur.IdUtilisateur==self.UtilisateurCourant.IdUtilisateur).update({'Dispo':0},
False)
print With ORM force update, time.time()-xref
xref = time.time()
if self.UtilisateurCourant.IdUtilisateur:
session.execute(UPDATE utilisateur SET Dispo=0 WHERE
IdUtilisateur=+str(self.UtilisateurCourant.IdUtilisateur))
print With ORM pure SQL, time.time()-xref
import MySQLdb
db = MySQLdb.connection(host=192.168.45.28, user=apm,
passwd=apm, db=test_christian)
xref = time.time()
db.query(UPDATE utilisateur SET Dispo=1 WHERE
IdUtilisateur=1)
r = db.store_result()
print With MySQLdb without ORM, time.time()-xref
*TEST RESULTS*
With ORM dont_load 0.45368665
With ORM force update 0.29631335
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0939998626709
With ORM dont_load 0.452999830246
With ORM force update 0.297000169754
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0939998626709
With ORM dont_load 0.45368665
With ORM force update 0.29631335
With ORM pure SQL 0.3123624
With MySQLdb without ORM 0.0940001010895
please use the profile module. The call chain from
Session.execute(somestring) down to connection.execute() is definitely
not
three times slower than connection.execute() itself. On my
workstation
here I can execute hundreds of ORM queries per second.
2009/9/30 Michael Bayer mike...@zzzcomputing.com
Christian Démolis wrote:
Thx for your answer.
MakeReleased is a method of com object windows agent (self.agent =
DispatchWithEvents('CosmoAgent.clsCCAgent', Evenement))
It takes 0 second to execute as we can see in the execute print
yes I realized later there were two blocks of timer calls. The
inclusion
of that code made the example harder to read.
In particular using session.merge() with an object will issue a
SELECT
first to locate the current row. this is likely the cause of the
slowness in this specific case since you seem to have a slow network
(profiling would reveal this too). Passing dont_load=True to
merge()
will skip the SELECT step and trust that the state you are passing
it
is
the state that is within the database.
Orm does additional steps
is it possible to force him update only one attribute of the
object?
you may say:
session.query(MyObject).filter(some_criterion).update({'attrname':somenewvalue},
False)
which will issue an UPDATE statement
[sqlalchemy] Orm slow to update why?
i made a test i did that without sql alchemy orm: import MySQLdb import time # Establich a connection db = MySQLdb.connection(host=192.168.45.28, user=apm, passwd=apm, db=test_christian) # Run a MySQL query from Python and get the result set xref = time.time() db.query(UPDATE utilisateur SET Dispo=1 WHERE IdUtilisateur=1) r = db.store_result() print time.time()-xref # Iterate through the result set # Example calls back up to 100 rows # for row in r.fetch_row(100): # print row *EXECUTE* Z:\python TestSql.py 0.10867575 It takes 0.1 s (the database is far away from the code) -- And then i test this (just a part of my application) def SeRendreIndisponible(self,event): # FONCTION COSMOCOM xref = time.time() if self.app.connec[CouplageCosmocom]==0 : return if hasattr(self, 'agent') : try : self.agent.MakeReleased() except : self.app.ReconnexionAgent() self.agent.MakeReleased() print SeRendreIndisponible PARTIE AGENT, time.time()-xref xref = time.time() self.UtilisateurCourant.Dispo = 0 if self.UtilisateurCourant.IdUtilisateur: x = self.ModifBase(self.UtilisateurCourant) # on réactualise l'état de la bdd print , x print SeRendreIndisponible PARTIE ECRITURE ETAT, time.time()-xref def ModifBase(self, objet): if int(self.app.param[Debug]) : print M, objet # try: x = session.merge(objet) session.flush() *EXECUTE* SeRendreIndisponible PARTIE AGENT 0.0 M Declaration.Utilisateur object at 0x032C0DD0 None SeRendreIndisponible PARTIE ECRITURE ETAT 0.9373624 It takes 0.9 seconds with SqlAlchemy (0.1) Why? --~--~-~--~~~---~--~~ 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] session.query object instead rowtuple
Hi everybody, I have a little problem with session.query. I try to optimize my queries with only attributes that i need. When we impose attribute, sqlalchemy return a rowtuple s = session.query(IdFile, NameFile) When we don't impose attribute, the return is the object s = session.query(File) Is it possible to obtain an sql alchemy object instead of rowtuple when we impose attributes??? I need it to avoid write this in my code for e in s: print e[0] it's more difficult to read than for e in s: print e.NameFile --~--~-~--~~~---~--~~ 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: session.query object instead rowtuple
Ok thanks 2009/9/28 Mike Conley mconl...@gmail.com The column is available as e.Namefile, no need to subscript with numbers. On Mon, Sep 28, 2009 at 6:07 AM, Christian Démolis christiandemo...@gmail.com wrote: Hi everybody, I have a little problem with session.query. I try to optimize my queries with only attributes that i need. When we impose attribute, sqlalchemy return a rowtuple s = session.query(IdFile, NameFile) When we don't impose attribute, the return is the object s = session.query(File) Is it possible to obtain an sql alchemy object instead of rowtuple when we impose attributes??? I need it to avoid write this in my code for e in s: print e[0] it's more difficult to read than for e in s: print e.NameFile --~--~-~--~~~---~--~~ 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 ALCHEMY instantly refresh
Hello, Thx for the answer, thx to Alexandre to translate my mail. Sorry, i continue in english, i tried to do that at the end of my declaration file : Base.metadata.create_all(engine) import sqlalchemy.orm.query class MyQuery(sqlalchemy.orm.query.Query): def __init__(*arg, **kw): self._populate_existing = True super(MyQuery, self).__init__(*arg, **kw) Session = scoped_session(sessionmaker(autocommit=True, bind=engine)) session = Session() print Le temps de déclaration SQL ALCHEMY, time.time()-xref Is it correct? i m not very good in subclassing :S It seems to not work. When i add a print in the __init__, i never see it during the execution of my program so MyQuery is not used i think. Can u tell me where should i subclass Query? In the declaration.py? in module sqlalchemy? I tried to add myquery here Session = scoped_session(sessionmaker(autocommit=True, bind=engine, query_cls=MyQuery)) but it does error File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg\sqlalchemy\orm\ session.py, line 899, in query return self._query_cls(entities, self, **kwargs) File Z:\Declaration.py, line 1451, in __init__ self._populate_existing = True NameError: global name 'self' is not defined I can t touch to the sqlalchemy module because the interpreter and libraries are installed on multiple computers which execute one unique code on a shared network path. So it's more easy to change my source code than sqlalchemy code... i just want to change session.query behavior without change the code of sqlalchemy itself, please help me. 2009/9/17 Michael Bayer mike...@zzzcomputing.com Alexandre Conrad wrote: Christian, 2009/9/17 Christian Démolis christiandemo...@gmail.com: Bonjour, Tu es français je pense au vu de ton prénom. Je continue donc en français. Nice guess. I understand it feels more comfortable writing in French rather than in English, but many people are reading this list (or is only Mike doing support? ;) ) and may be interested at the topic. And more eyes and brains may answer your question. So please keep conversations on this list to its native language - English. If you really want to switch to a non-English language with someone particular, please exchange off-list, but I believe you'll dramatically reduce your chances of solving your problem. So for the record, you were explaining that you have 25000 lines of code and you'd like to avoid to add refresh or session.query().populate_existing() all around the place. You have attempted to override the query method but couldn't make it. You have pointed out the PreFilteredQuery http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery but was unsuccessful achieving what you wanted. Sorry, maybe someone else can help you there. I haven't played with Query overriding myself. a Query subclass which just says def __init__(*arg, **kw): self._populate_existing = True super(MyQuery, self).__init__(*arg, **kw) should do it Alex --~--~-~--~~~---~--~~ 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 ALCHEMY instantly refresh
^^ I m so shameful It works very well now. I post the subclass complete if anyone need it in future Base.metadata.create_all(engine) import sqlalchemy.orm.query from sqlalchemy.orm.query import Query class Query(Query): def __init__(self, *arg, **kw): print I pass here self._populate_existing = True super(Query, self).__init__(*arg, **kw) Session = scoped_session(sessionmaker(autocommit=True, bind=engine, query_cls=Query)) session = Session() The new query class must be passed to the session call. thx for all 2009/9/18 Alexandre Conrad alexandre.con...@gmail.com In Python, you have to pass self as first argument to all methods of a class: class MyQuery(sqlalchemy.orm.query.Query): def __init__(self, *arg, **kw): ... Alex 2009/9/18 Christian Démolis christiandemo...@gmail.com: Hello, Thx for the answer, thx to Alexandre to translate my mail. Sorry, i continue in english, i tried to do that at the end of my declaration file : Base.metadata.create_all(engine) import sqlalchemy.orm.query class MyQuery(sqlalchemy.orm.query.Query): def __init__(*arg, **kw): self._populate_existing = True super(MyQuery, self).__init__(*arg, **kw) Session = scoped_session(sessionmaker(autocommit=True, bind=engine)) session = Session() print Le temps de déclaration SQL ALCHEMY, time.time()-xref Is it correct? i m not very good in subclassing :S It seems to not work. When i add a print in the __init__, i never see it during the execution of my program so MyQuery is not used i think. Can u tell me where should i subclass Query? In the declaration.py? in module sqlalchemy? I tried to add myquery here Session = scoped_session(sessionmaker(autocommit=True, bind=engine, query_cls=MyQuery)) but it does error File C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg\sqlalchemy\orm\ session.py, line 899, in query return self._query_cls(entities, self, **kwargs) File Z:\Declaration.py, line 1451, in __init__ self._populate_existing = True NameError: global name 'self' is not defined I can t touch to the sqlalchemy module because the interpreter and libraries are installed on multiple computers which execute one unique code on a shared network path. So it's more easy to change my source code than sqlalchemy code... i just want to change session.query behavior without change the code of sqlalchemy itself, please help me. 2009/9/17 Michael Bayer mike...@zzzcomputing.com Alexandre Conrad wrote: Christian, 2009/9/17 Christian Démolis christiandemo...@gmail.com: Bonjour, Tu es français je pense au vu de ton prénom. Je continue donc en français. Nice guess. I understand it feels more comfortable writing in French rather than in English, but many people are reading this list (or is only Mike doing support? ;) ) and may be interested at the topic. And more eyes and brains may answer your question. So please keep conversations on this list to its native language - English. If you really want to switch to a non-English language with someone particular, please exchange off-list, but I believe you'll dramatically reduce your chances of solving your problem. So for the record, you were explaining that you have 25000 lines of code and you'd like to avoid to add refresh or session.query().populate_existing() all around the place. You have attempted to override the query method but couldn't make it. You have pointed out the PreFilteredQuery http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery but was unsuccessful achieving what you wanted. Sorry, maybe someone else can help you there. I haven't played with Query overriding myself. a Query subclass which just says def __init__(*arg, **kw): self._populate_existing = True super(MyQuery, self).__init__(*arg, **kw) should do it Alex --~--~-~--~~~---~--~~ 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] SQL ALCHEMY instantly refresh
How can i force sqlalchemy to refresh an object when i did a session.query??? Sqlalchemy seems to work with a cache, i want to deal with it. Nota : i use sqlalchemy in non transactional mode Session = scoped_session(sessionmaker(autocommit=True, bind=engine)) --~--~-~--~~~---~--~~ 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 ALCHEMY instantly refresh
Bonjour, Tu es français je pense au vu de ton prénom. Je continue donc en français. En fait j'ai 25000 lignes de codes derrière moi et j'aimerai éviter d'avoir à ajouter tous les refresh ou les session.query().populate_existing() partout dans mon code J'ai tenté en vain de surcharger la méthode query de sqlalchemy mais je n'ai pas réussi... J'ai trouvé ce lien qui est intéressant mais je n'ai pas réussi à l'exploiter : http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery 2009/9/17 Alexandre Conrad alexandre.con...@gmail.com 2009/9/17 Christian Démolis christiandemo...@gmail.com: How can i force sqlalchemy to refresh an object when i did a session.query??? You may want look at this: http://www.sqlalchemy.org/docs/05/session.html#refreshing-expiring You may, as well, look at expunging. Alex --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
