Re: [sqlalchemy] SAWarning: Unicode type received non-unicode bind param value
Hey,
you dan do:
import warnings
warnings.simplefilter('|error|')
This will raise an exception. and give you a stacktrace on where the
Unicode warnign happened.
On 04/22/2015 09:48 AM, Pavel S wrote:
Hi,
it happened to me many times during development, mainly when used
custom column types, that I passed wrong type of value to the query.
Then the the following warning was emitted:|
||
SAWarning: Unicodetype received non-unicode bindparam value
|
The problem with such warning is it does not say
1) were the problem occurred (the line number points to somewhere in
sqlalchemy and not to my application)
2) what was the value which caused the problem ( repr() of that value
would be nice to have)
I always had to hack sqlalchemy/sql/sqltypes.py and add print value
before the warning is emitted to actually see what was wrong.
Is there any convenient way how to solve such issue?
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
_*check out pointcloud9.com*_ <http://pointcloud9.com/>
**Sebastian Elsner - Pipeline Techincal Director - RISE*
t: +49 30 20180300 _sebast...@risefx.com _ <mailto:sebast...@risefx.com>
f: +49 30 61651074 _www.risefx.com_ <http://www.risefx.com/>*
*RISE FX GmbH*
*Schlesische Strasse 28, 10997 Berlin
An der Schanz 1A, 50735 Köln
Büchsenstraße 20, 70174 Stuttgart
Gumpendorferstrasse 55, 1060 Wien
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B*
--
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/d/optout.
Re: [sqlalchemy] mysql double issue
Amazing! Thank you!
On 11/20/2013 04:44 PM, Michael Bayer wrote:
the short answer is that DOUBLE is doing a Decimal conversion here and that’s
where the digits are being lost.
So fix the problem immediately using asdecimal=False, then also make sure you
use repr() to print out your type as str() for Python floats also truncates:
class SomeClass(Base):
__tablename__ = 'someclass'
id = Column(Integer, primary_key=True)
value = Column(DOUBLE(asdecimal=False))
x = s.query(SomeClass).get(1)
print repr(x.value)
longer answer, I was a bit surprised that DOUBLE has asdecimal=True by default,
but OK, but in any case I was surprised to see that SQLAlchemy’s Float type
hardcodes the number of digits to 10 when it converts from float to Decimal,
this will be fixed in 0.9 using the patch up at
http://www.sqlalchemy.org/trac/attachment/ticket/2867/.
If you do want Decimal objects back in this case with DOUBLE, I’d subclass
mysql.DOUBLE and specify a new result_processor() method.
On Nov 20, 2013, at 7:00 AM, Sebastian Elsner wrote:
Hello,
I am inserting float data into a MySQL column of type DOUBLE. The data gets
inserted properly (verified via mysql terminal). Querying for the object
returns a Decimal object, but the number of digits after the decimal point if
always limited to 11 (while DOUBLE supports 15 by default). So, what can I do
to get the correct value? I have played with the precision and scale arguments
for DOUBLE without effect.
Here's an example:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer
from sqlalchemy.dialects.mysql.base import DOUBLE
Base = declarative_base()
class SomeClass(Base):
__tablename__ = 'someclass'
id = Column(Integer, primary_key=True)
value = Column(DOUBLE)
engine = create_engine('mysql://user:pass@localhost/test', echo=True)
Base.metadata.create_all(engine)
s = sessionmaker(engine)()
r = SomeClass(value=0.1234567891234567)
s.add(r)
s.commit()
x = s.query(SomeClass).get(1)
print x.value
Regards
Sebastian
--
check out www.pointcloud9.com
Sebastian Elsner - Pipeline Technical Director - RISE
t: +49 30 20180300 flor...@risefx.com
f: +49 30 61651074 www.risefx.com
RISE FX GmbH
Schlesische Strasse 28, Aufgang B, 10997 Berlin
c/o action concept, An der Hasenkaule 1-7, 50354 Hürth
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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.
--
check out www.pointcloud9.com
Sebastian Elsner - Pipeline Technical Director - RISE
t: +49 30 20180300 flor...@risefx.com
f: +49 30 61651074 www.risefx.com
RISE FX GmbH
Schlesische Strasse 28, Aufgang B, 10997 Berlin
c/o action concept, An der Hasenkaule 1-7, 50354 Hürth
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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] mysql double issue
Hello,
I am inserting float data into a MySQL column of type DOUBLE. The data
gets inserted properly (verified via mysql terminal). Querying for the
object returns a Decimal object, but the number of digits after the
decimal point if always limited to 11 (while DOUBLE supports 15 by
default). So, what can I do to get the correct value? I have played with
the precision and scale arguments for DOUBLE without effect.
Here's an example:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer
from sqlalchemy.dialects.mysql.base import DOUBLE
Base = declarative_base()
class SomeClass(Base):
__tablename__ = 'someclass'
id = Column(Integer, primary_key=True)
value = Column(DOUBLE)
engine = create_engine('mysql://user:pass@localhost/test', echo=True)
Base.metadata.create_all(engine)
s = sessionmaker(engine)()
r = SomeClass(value=0.1234567891234567)
s.add(r)
s.commit()
x = s.query(SomeClass).get(1)
print x.value
Regards
Sebastian
--
check out www.pointcloud9.com
Sebastian Elsner - Pipeline Technical Director - RISE
t: +49 30 20180300 flor...@risefx.com
f: +49 30 61651074 www.risefx.com
RISE FX GmbH
Schlesische Strasse 28, Aufgang B, 10997 Berlin
c/o action concept, An der Hasenkaule 1-7, 50354 Hürth
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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.
Re: [sqlalchemy] newest address for each user
This is just anazing! I was tinkering with the select statement, but
using the correlate_except would never have come to my mind. Thank you!
Am 22.10.2013 00:16, schrieb Michael Bayer:
> if we're talking about just the timestamp, then that would be a column
> property and if you don't want it to load normally it would be under a
> deferred().
>
> An analogue of the subquery example using count() is here:
> http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#using-column-property
>
> again, you can adapt this to look like your max() + association table:
>
>
> class User(Base):
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
>
> address_created = column_property(
> select([func.max(Address.created)]).\
> where(Address.id == UserAddresses.address_id).\
> where(UserAddresses.user_id == id).\
> correlate_except(Address, UserAddresses)
> )
>
> On Oct 21, 2013, at 3:12 AM, Sebastian Elsner wrote:
>
>> Thank you for the help! Additionally, I was wondering if it would be
>> able to make "newest_address" an attribute on the user class, which can
>> then be used in a query with ".options(joinedload('newest_address'))".
>> My goal would be that I get an attribute that returns the newest'
>> address date time on normal access and is also able to be eagerly
>> loaded/joined in a query object. I read up in the docs and either
>> column_property or Correlated Subquery Relatonship Hybrid seems to be
>> made for this. Which one should I use?
>>
>>
>>
>> ButAm 20.10.2013 04:41, schrieb Michael Bayer:
>>> On Oct 19, 2013, at 4:24 PM, Sebastian Elsner wrote:
>>>
>>>> Hello,
>>>>
>>>> using the Address and User example, where the Address is connected to
>>>> the User via a many-to-many relationship, I want to get all users with
>>>> the date of their newest address. This is what I have now:
>>>>
>>>> s.query(User, s.query(func.max(Address.created)).\
>>>> filter(Address.users.any()).correlate(User).as_scalar()).\
>>>> outerjoin(User.addresses).all()
>>>>
>>>> But this is giving me all users with the newest address in the whole
>>>> address table. I think the error is in the subquery's filter, but I fail
>>>> to see how I can fix it. I am also not tied to this query, so if you
>>>> know a better way to get a list of all Users and their newest address
>>>> date, shoot!
>>> the format for this is the "select user rows + an aggregate of a related
>>> table", this format is illustrated here:
>>> http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-subqueries
>>> where we illustrate the count of address rows per user.
>>>
>>> I see here though you have an association table in between them so that
>>> just has to be added to the subquery to create a row that goes across
>>> Address and UserAddresses, same idea though, use subquery with aggregate +
>>> group_by, (outer) join to that:
>>>
>>> subq = session.query(
>>>func.max(Address.created).label("created"),
>>>UserAddresses.user_id).join(UserAddresses).\
>>>group_by(UserAddresses.user_id).subquery()
>>>
>>> q = session.query(User, subq.c.created).outerjoin(subq)
>>> print q.all()
>>>
>>>
>>>
>>>
>>>> Here is a working example. As you can see if you run it, even Users with
>>>> no Addresses assigned will get the newest address date in the query.
>>>>
>>>> import datetime
>>>> from sqlalchemy.ext.declarative import declarative_base
>>>> from sqlalchemy.engine import create_engine
>>>> from sqlalchemy.orm.session import sessionmaker
>>>> from sqlalchemy.schema import Column, ForeignKey
>>>> from sqlalchemy.types import Integer, DateTime, String
>>>> from sqlalchemy.orm import relationship
>>>> from sqlalchemy.sql.expression import func
>>>>
>>>> Base = declarative_base()
>>>>
>>>>
>>>> class Address(Base):
>>>> __tablename__ = 'address'
>>>> id = Column(Integer, primary_key=True)
>>>> created = Column(DateTime)
>>>> users = relationship('User', back_populates='addresses',
>>>> secondary='useraddress')
>>>&
Re: [sqlalchemy] newest address for each user
Thank you for the help! Additionally, I was wondering if it would be
able to make "newest_address" an attribute on the user class, which can
then be used in a query with ".options(joinedload('newest_address'))".
My goal would be that I get an attribute that returns the newest'
address date time on normal access and is also able to be eagerly
loaded/joined in a query object. I read up in the docs and either
column_property or Correlated Subquery Relatonship Hybrid seems to be
made for this. Which one should I use?
ButAm 20.10.2013 04:41, schrieb Michael Bayer:
> On Oct 19, 2013, at 4:24 PM, Sebastian Elsner wrote:
>
>> Hello,
>>
>> using the Address and User example, where the Address is connected to
>> the User via a many-to-many relationship, I want to get all users with
>> the date of their newest address. This is what I have now:
>>
>> s.query(User, s.query(func.max(Address.created)).\
>> filter(Address.users.any()).correlate(User).as_scalar()).\
>>outerjoin(User.addresses).all()
>>
>> But this is giving me all users with the newest address in the whole
>> address table. I think the error is in the subquery's filter, but I fail
>> to see how I can fix it. I am also not tied to this query, so if you
>> know a better way to get a list of all Users and their newest address
>> date, shoot!
> the format for this is the "select user rows + an aggregate of a related
> table", this format is illustrated here:
> http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-subqueries
> where we illustrate the count of address rows per user.
>
> I see here though you have an association table in between them so that just
> has to be added to the subquery to create a row that goes across Address and
> UserAddresses, same idea though, use subquery with aggregate + group_by,
> (outer) join to that:
>
> subq = session.query(
> func.max(Address.created).label("created"),
> UserAddresses.user_id).join(UserAddresses).\
> group_by(UserAddresses.user_id).subquery()
>
> q = session.query(User, subq.c.created).outerjoin(subq)
> print q.all()
>
>
>
>
>> Here is a working example. As you can see if you run it, even Users with
>> no Addresses assigned will get the newest address date in the query.
>>
>> import datetime
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.engine import create_engine
>> from sqlalchemy.orm.session import sessionmaker
>> from sqlalchemy.schema import Column, ForeignKey
>> from sqlalchemy.types import Integer, DateTime, String
>> from sqlalchemy.orm import relationship
>> from sqlalchemy.sql.expression import func
>>
>> Base = declarative_base()
>>
>>
>> class Address(Base):
>>__tablename__ = 'address'
>>id = Column(Integer, primary_key=True)
>>created = Column(DateTime)
>>users = relationship('User', back_populates='addresses',
>> secondary='useraddress')
>>
>>def __repr__(self):
>>return "Address: %s, %s" % (self.id, self.created)
>>
>>
>> class User(Base):
>>__tablename__ = 'user'
>>id = Column(Integer, primary_key=True)
>>name = Column(String)
>>addresses = relationship('Address', back_populates='users',
>> secondary='useraddress')
>>
>>def __repr__(self):
>>return "User: " + self.name
>>
>>
>> class UserAddresses(Base):
>>__tablename__ = 'useraddress'
>>user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
>>address_id = Column(Integer, ForeignKey('address.id'), primary_key=True)
>>
>> engine = create_engine('sqlite://')
>> Base.metadata.create_all(engine)
>> session = sessionmaker(engine)()
>>
>> u1 = User(name="Foo")
>> u2 = User(name="Bar")
>> u1.addresses.append(Address(created=datetime.datetime.now()))
>> u1.addresses.append(Address(created=datetime.datetime.now() -
>> datetime.timedelta(days=1)))
>> session.add(u1)
>> session.add(u2)
>> session.commit()
>> print u1, u1.addresses
>> print u2, u2.addresses
>> print session.query(User, print session.query(User,
>> session.query(func.max(Address.created)).filter(Address.users.any()).correlate(User).as_scalar()).outerjoin(User.addresses).all()
>>
>> Cheers
>>
>> Sebastian
>>
>> --
>> You received thi
[sqlalchemy] newest address for each user
Hello,
using the Address and User example, where the Address is connected to
the User via a many-to-many relationship, I want to get all users with
the date of their newest address. This is what I have now:
s.query(User, s.query(func.max(Address.created)).\
filter(Address.users.any()).correlate(User).as_scalar()).\
outerjoin(User.addresses).all()
But this is giving me all users with the newest address in the whole
address table. I think the error is in the subquery's filter, but I fail
to see how I can fix it. I am also not tied to this query, so if you
know a better way to get a list of all Users and their newest address
date, shoot!
Here is a working example. As you can see if you run it, even Users with
no Addresses assigned will get the newest address date in the query.
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, DateTime, String
from sqlalchemy.orm import relationship
from sqlalchemy.sql.expression import func
Base = declarative_base()
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
created = Column(DateTime)
users = relationship('User', back_populates='addresses',
secondary='useraddress')
def __repr__(self):
return "Address: %s, %s" % (self.id, self.created)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship('Address', back_populates='users',
secondary='useraddress')
def __repr__(self):
return "User: " + self.name
class UserAddresses(Base):
__tablename__ = 'useraddress'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
address_id = Column(Integer, ForeignKey('address.id'), primary_key=True)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
u1 = User(name="Foo")
u2 = User(name="Bar")
u1.addresses.append(Address(created=datetime.datetime.now()))
u1.addresses.append(Address(created=datetime.datetime.now() -
datetime.timedelta(days=1)))
session.add(u1)
session.add(u2)
session.commit()
print u1, u1.addresses
print u2, u2.addresses
print session.query(User, print session.query(User,
session.query(func.max(Address.created)).filter(Address.users.any()).correlate(User).as_scalar()).outerjoin(User.addresses).all()
Cheers
Sebastian
--
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.
Re: [sqlalchemy] update with custom Type
After much thought and playing around I think I may have
over-complicated the problem and found an easier way to do what I wanted:
s.query(Foo).update({"timcode_column":
func.concat(func.left(Foo.timcode_column, 11), ":", "24") },False)
Thanks for the help anyway :)
On 08/28/2013 08:08 PM, Michael Bayer wrote:
On Aug 28, 2013, at 9:59 AM, Sebastian Elsner wrote:
Now I would like to be able to do the following:
s.query(Foo).update({"some_timecode": Foo.some_timecode.add_hours(5)}) # Adds 5
hours to every Foo's timecode
I have seen this should be possible with a Comparator factory in 0.8 but I am
stuck with 0.7 for now. How can I do this with 0.7?
just move out your function:
update({"sometimecode": add_hours(Foo.some_timecode, 5)})
Something like this would also be OK for me:
s.query(Foo).update({"some_timecode": Foo.some_timecode + TC("01:00:00:00:00")})
I have tried implementing the __add__ for both the TC and Timecode class and read the
"Augmenting Existing Types" help, but failed to put the puzzle together.
Thank you for helping!
Sebastian
--
check out www.pointcloud9.com
Sebastian Elsner - Pipeline Technical Director - RISE
t: +49 30 20180300 flor...@risefx.com
f: +49 30 61651074 www.risefx.com
RISE FX GmbH
Schlesische Strasse 28, Aufgang B, 10997 Berlin
c/o action concept, An der Hasenkaule 1-7, 50354 Hürth
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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.
--
check out www.pointcloud9.com
Sebastian Elsner - Pipeline Technical Director - RISE
t: +49 30 20180300 flor...@risefx.com
f: +49 30 61651074 www.risefx.com
RISE FX GmbH
Schlesische Strasse 28, Aufgang B, 10997 Berlin
c/o action concept, An der Hasenkaule 1-7, 50354 Hürth
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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] update with custom Type
Hello,
I have an augmented type that holds data for a movie's timecode, which
has the format "00:00:00:00" (hours:minutes:seconds:frame) and is
serialized as a CHAR:
class Timecode(TypeDecorator):
impl = CHAR
def load_dialect_impl(self, dialect):
return dialect.type_descriptor(CHAR(11))
def process_bind_param(self, value, dialect):
if value:
if isinstance(value, TC):
return str(value)
else:
raise TypeError
else:
return None
def process_result_value(self, value, dialect):
if value:
return TC(tcString=value)
else:
return None
This type works together with the TC class which allows to do
computations on timecodes returned from the database. To keep this
question simple lets just assume all it does is pass through a string:
class TC(object):
def __init__(self, tcString):
self.tcString = tcString
#parse string here and tokenize into hrs:min:sec:frm
def __str__(self):
return self.tc
#other methods here
Now I would like to be able to do the following:
s.query(Foo).update({"some_timecode": Foo.some_timecode.add_hours(5)}) #
Adds 5 hours to every Foo's timecode
I have seen this should be possible with a Comparator factory in 0.8 but
I am stuck with 0.7 for now. How can I do this with 0.7?
Something like this would also be OK for me:
s.query(Foo).update({"some_timecode": Foo.some_timecode +
TC("01:00:00:00:00")})
I have tried implementing the __add__ for both the TC and Timecode class
and read the "Augmenting Existing Types" help, but failed to put the
puzzle together.
Thank you for helping!
Sebastian
--
check out www.pointcloud9.com
Sebastian Elsner - Pipeline Technical Director - RISE
t: +49 30 20180300 flor...@risefx.com
f: +49 30 61651074 www.risefx.com
RISE FX GmbH
Schlesische Strasse 28, Aufgang B, 10997 Berlin
c/o action concept, An der Hasenkaule 1-7, 50354 Hürth
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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.
Re: [sqlalchemy] Do not add entities automatically to the session?
Are you doing any relationship assignments in the User constructor? Because if the assigned object is in a session the User will also be put in the same one automatically. Otherwise the default behaviour is exactly what you expect. If you do a: print session.new, you should not see anything in there. Also, you might want to read up on the object states in the docs. A standard object is first in "detached state". Cheers Sebastian On 07/22/2013 04:22 PM, Michel Albert wrote: Hi, I realised that SA adds an instance to the session as soon as I instantiate it. How/Where can I disable this? For example: currently I have this behaviour: >>> session = get_session() >>> my_user = User(email='f...@example.com') >>> len(session.query(User)) 1 but instead I would like to have the following: >>> session = get_session() >>> my_user = User(email='f...@example.com') >>> len(session.query(User)) 0 >>> session.add(my_user) >>> len(session.query(User)) 1 -- mich. -- 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. -- check out www.pointcloud9.com Sebastian Elsner - Pipeline Technical Director - RISE t: +49 30 20180300 flor...@risefx.com f: +49 30 61651074 www.risefx.com RISE FX GmbH Schlesische Strasse 28, Aufgang B, 10997 Berlin c/o action concept, An der Hasenkaule 1-7, 50354 Hürth Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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.
Re: [sqlalchemy] count subquery
Hm I'm lost. This is where I am now: session.query(User.id, Assignment.id, session.query(func.count(Assignment.id)).filter(Assignment.user_id == User.id).as_scalar()).join(Assignment).all() On 06/24/2013 04:26 PM, Michael Bayer wrote: it's a scalar subquery. you want to make the select() and then call as_scalar() on it so that it behaves like a column in a SQL expression. On Jun 24, 2013, at 9:34 AM, Sebastian Elsner wrote: Hello, I am trying to translate this SQL to a SQLAlchemy query, but failed so far: select `users`.`name`, `assignments`.`id`, ( select count(*) from `assignments` where `assignments`.`user_id` = `users`.`id` ) as `num_assignments` from `users` join `assignments` on `assignments`.`user_id` = `users`.`id` I would like to get results of (user_id, assignment_id, total_assignments_per_user_id). I have found a similar question on the list (https://groups.google.com/forum/#!topic/sqlalchemy/LBEyRe3w-8Q), and tried to assemble a query like so, but I am missing something... It would also be nice if there were a faster way to do this. Maybe someone has a good idea. And a question on terminology: is it really called subquery if the "subquery" is in the "select block" (like above). Many thanks Sebastian -- 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. -- check out www.pointcloud9.com Sebastian Elsner - Pipeline Technical Director - RISE t: +49 30 20180300 flor...@risefx.com f: +49 30 61651074 www.risefx.com RISE FX GmbH Schlesische Strasse 28, Aufgang B, 10997 Berlin c/o action concept, An der Hasenkaule 1-7, 50354 Hürth Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] count subquery
Hello, I am trying to translate this SQL to a SQLAlchemy query, but failed so far: select `users`.`name`, `assignments`.`id`, ( select count(*) from `assignments` where `assignments`.`user_id` = `users`.`id` ) as `num_assignments` from `users` join `assignments` on `assignments`.`user_id` = `users`.`id` I would like to get results of (user_id, assignment_id, total_assignments_per_user_id). I have found a similar question on the list (https://groups.google.com/forum/#!topic/sqlalchemy/LBEyRe3w-8Q), and tried to assemble a query like so, but I am missing something... It would also be nice if there were a faster way to do this. Maybe someone has a good idea. And a question on terminology: is it really called subquery if the "subquery" is in the "select block" (like above). Many thanks Sebastian -- 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.
Re: [sqlalchemy] query.whereclause
Thank you, this did the trick. And since only I use it offline the security issues are OK for me. On 02/28/2013 12:27 PM, Simon King wrote: On Thu, Feb 28, 2013 at 10:28 AM, Sebastian Elsner wrote: Hello, I want to feed a Query.whereclause to mysqldump. For this i will need the full where clause with all labels replaced with values, right now I get labels like :project_id_1. How can I do that? Many thanks, Sebastian One approach is suggested at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings. You will need to pay attention to the warning at the top of that page - by not using bound parameters, you open yourself up to SQL Injection holes, so if you don't trust the user of the system you need to be very careful about escaping. To get the compiled version of a query (the equivalent of "d" on that wiki page) I think you can use the query.selectable property. Hope that helps, Simon -- check out www.pointcloud9.com Sebastian Elsner - Pipeline Technical Director - RISE t: +49 30 20180300 flor...@risefx.com f: +49 30 61651074 www.risefx.com RISE FX GmbH Schlesische Strasse 28, Aufgang B, 10997 Berlin c/o action concept, An der Hasenkaule 1-7, 50354 Hürth Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] query.whereclause
Hello, I want to feed a Query.whereclause to mysqldump. For this i will need the full where clause with all labels replaced with values, right now I get labels like :project_id_1. How can I do that? Many thanks, Sebastian -- check out www.pointcloud9.com Sebastian Elsner - Pipeline Technical Director - RISE t: +49 30 20180300 flor...@risefx.com f: +49 30 61651074 www.risefx.com RISE FX GmbH Schlesische Strasse 28, Aufgang B, 10997 Berlin c/o action concept, An der Hasenkaule 1-7, 50354 Hürth Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] serialize list
Hello, I want to serialize a python list, which by convention can only contain strings and save it to mysql database in on column. I was thinking about an unicode column and an attribute event which just does a str(list) for saving and a eval(string) for access. I am not sure though if this is a good method, or if there even is a buildin method. Thanks for your suggestions! Sebastian -- 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] Read-Only Database
Hey, I have been using the approaches described here: http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg21801.html Cheers Sebastian On 06/21/2011 05:06 PM, Mark Erbaugh wrote: My program accesses a sqlite database. It only extracts data from the database, it never writes anything to it. It can also be assumed that the database is not updated by other processes. In reality, the database is completely replaced periodically by a new version, but the program can be shut down and re-started whenever that happens. Is there a way to tell SQLAlchemy that the database is read-only, and would that simplify the work that SA does behind the scenes? Thanks, Mark -- Sebastian Elsner - Pipeline TD - r i s e | fx t: +49 30 201 803 00 sebast...@risefx.com c: +49 175 336 5739 7548 www.risefx.com r i s e | fx GmbH Schlesische Strasse 28, Aufgang B 10997 Berlin Richard-Byrd-Strasse 12, 50829 Cologne Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] get_history not returning as expected
Hello,
using Python 2.6 and SA 0.6.6. Please see the example below. I want to
get the History of a relationship attribute, but whatever I pass to the
"passive" argument, I never get the "deleted version" of the object,
only the PASSIVE_NO_RESULT symbol (if I understand correctly, I would
not need to pass anything). Where is the error in my reasoning with this?
Cheers
Sebastian
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm.attributes import get_history
Base = declarative_base()
engine = create_engine('sqlite:///:memory:')
Base.metadata.bind = engine
Session = sessionmaker(bind = engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key = True)
name = Column(String)
def __init__(self, name):
self.name = name
def __repr__(self):
return "" % (self.name)
class Address(Base):
__tablename__ = "addresses"
id = Column(Integer, primary_key = True)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', backref = 'addresses')
street = Column(String)
def __init__(self, street):
self.street = street
def __repr__(self):
return "" % (self.street)
Base.metadata.create_all(engine)
session = Session()
u = User("joe")
a = Address("Downing Street")
u.addresses.append(a)
session.add(u)
session.commit()
u2 = User("jack")
a.user = u2
print get_history(a, "user")
#([], (), [])
--
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] logging user changes
Hello, I want to introduce logging to my SA application, so the users with elevated privileges can control what "normal" users change in the database. The log should go to the database not to a file. I have been thinking about a logger table like: table action: - column: datetime_of_action - relationship: user_who_changed_something - relationship: affected_record - column: custom_message Actually I have several different tables I need to establish a relationship with for the affected_record. I need a "mutable relationship" column, so I can assign instances of multiple types. Is that possible? Can you give me a hint where to read up? Being in the design phase I am also open to other approaches on how to tackle logging of user changes in a db. Thanks Sebastian -- Sebastian Elsner - Pipeline TD - r i s e | fx t: +49 30 201 803 00 sebast...@risefx.com c: +49 175 336 5739 7548 www.risefx.com r i s e | fx GmbH Schlesische Strasse 28, Aufgang B 10997 Berlin Richard-Byrd-Strasse 12, 50829 Cologne Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] understanding connection pooling
So, using MySQL with max_connections = 100 I quickly run out of connections, because every client is using about 6 connections, one for each dialog window, which has its own session instance. seems like an architecture that could use some trimming Yes, you are probably right. But what if I have two dialogs open, change data in both and click apply on the first (so it commits the changes). If both had the same session instance, which was passed from the main window, the second dialogs changes would also be committed, although the user might have wanted to discard them/rollback . Is there an alternate way of doing this? -- 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] understanding connection pooling
Hello, I wanted to make sure, I get this right: every session creates its own connection on demand (only if I actually do query with the session), right? So, using MySQL with max_connections = 100 I quickly run out of connections, because every client is using about 6 connections, one for each dialog window, which has its own session instance. Now I am playing with poolclass = StaticPool instead of QueuePool, which seems to give me the the same speed per query. Or is there any difference to expect, which I just haven't encountered? Also, is there something about StaticPool which prohibits me to do certain things a QueuePool could (except the reconnection note in the docs)? I guess having multiple threads with their own session querying would not work?! Thank you, Sebastian -- 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] self_group on Query
Hello, wanting to create the following query: (SELECT * from tbl ORDER BY b LIMIT 5) UNION ALL (SELECT * from tbl ORDER BY a LIMIT 5) The important thing are the (), which I cant get to work with the examples in the union() docs . I came across a solution involving self_group on Queries in an old mailing list post (using SA 0.4) This is what I figured: q=session.query(Table) querylist=[q.order_by(Table.b).limit(5), q.order_by(Table.a).limit(5)] sel = q.union_all(*[q.self_group() for q in querylist]) But I get an error, that Query has not self_group attribute. Seems self_group is not longer a Query member. So how would I create a query like this? cheers Sebastian -- 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] Newest records per category
You are actually right. There was a logical mistake in my request, so I have to rephrase it: Having a table Assets with columns category (String) and created (DateTime), I would like to get the newest n records for each category. On 10/11/2010 04:30 PM, Mark Erbaugh wrote: On Oct 11, 2010, at 7:50 AM, Sebastian Elsner wrote: have one table called 'Assets' with a 'category' (String) and 'created' (DateTime) column. Now I would like to find the records created since a given datetime for each category: This is what I thought would work (with a self-join): session.query(Asset).join(Asset, and_(Asset.category == Asset.category, Asset.created > specifiedDateTime) But it does error with 'Cant find any foreign key relationships...' How can I fix this? Or do you have a better idea how to accomplish the task? You probably don't need the self join? You can filter on multiple conditions. session.query(Asset).filter(Asset.category == spefiiedCateogory).filter(Asset.created > specifiedDateTime) or the equivalent using the and_ function session.query(Asset).filter(and_(Asset.category == specifiedCategory, Asset.created > specifiedDateTime)) Mark -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Sebastian Elsner - Pipeline TD - r i s e | fx t: +49 30 201 803 00 sebast...@risefx.com c: +49 175 336 5739 7548 www.risefx.com r i s e | fx GmbH Schlesische Strasse 28, Aufgang B 10997 Berlin Richard-Byrd-Strasse 12, 50829 Cologne Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] Newest records per category
Hello, I have one table called 'Assets' with a 'category' (String) and 'created' (DateTime) column. Now I would like to find the records created since a given datetime for each category: This is what I thought would work (with a self-join): session.query(Asset).join(Asset, and_(Asset.category == Asset.category, Asset.created > specifiedDateTime) But it does error with 'Cant find any foreign key relationships...' How can I fix this? Or do you have a better idea how to accomplish the task? Thank you, Sebastian -- 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] modify existing query with group_by and count
Thank you very much, from_self() works a treat! On 06/28/2010 04:47 PM, Michael Bayer wrote: On Jun 28, 2010, at 5:23 AM, Sebastian Elsner wrote: Hello, I build a query to filter a table depending on options a user can specify. So I have a working query to execute when all GUI elements (which provide the filter information) are evaluated. What I do is asking for the GUI elements value and depending on that I modify the query: class Project(Base): __tablename__="Project" id= Column(Integer,primary_keys=True) status_id=Column(Integer) q = sess.query(Project) if checkbox: q.filter(Project.id == 1) if checkbox2: q.filter(Project.id == 2) ... and so on. this works fine. Now I would like to provide the user with an overview on what was filtered: So basically I want to use a group_by and count to show how many Projects there are for each status_id. This is from the docs (ormtut) and does what I want, except I want to apply this to an existing query (or the result of one - don't care) session.query(func.count(User.name),User.name).group_by(User.name).all() How would this be possible? if from a SQL perspective you're looking for taking a query like "SELECT * FROM ..." and turning it into "SELECT count(*) FROM (SELECT * FROM ...) GROUP BY ...", i.e. turning it into a subquery, the from_self() method of Query is designed for that purpose, you might check the docs for that. if OTOH you just want some ad-hoc columns from a Query, you can use values() for that. http://www.sqlalchemy.org/docs/reference/orm/query.html?#sqlalchemy.orm.query.Query.from_self http://www.sqlalchemy.org/docs/reference/orm/query.html?#sqlalchemy.orm.query.Query.values It would be nice if all these methods had mini-examples at some point in the docs... -- Sebastian Elsner-pipeline td - r i s e | fx t: +49 30 20180300 sebast...@risefx.com c: +49 175 3365739 www.risefx.com r i s e | fx GmbH Schlesische Strasse 28 Aufgang B, 10997 Berlin Geschäftsführer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] modify existing query with group_by and count
Hello, I build a query to filter a table depending on options a user can specify. So I have a working query to execute when all GUI elements (which provide the filter information) are evaluated. What I do is asking for the GUI elements value and depending on that I modify the query: class Project(Base): __tablename__="Project" id= Column(Integer,primary_keys=True) status_id=Column(Integer) q = sess.query(Project) if checkbox: q.filter(Project.id == 1) if checkbox2: q.filter(Project.id == 2) ... and so on. this works fine. Now I would like to provide the user with an overview on what was filtered: So basically I want to use a group_by and count to show how many Projects there are for each status_id. This is from the docs (ormtut) and does what I want, except I want to apply this to an existing query (or the result of one - don't care) session.query(func.count(User.name),User.name).group_by(User.name).all() How would this be possible? Thank you Sebastian -- 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] deleting
Hello, I am unsure on how to delete correctly. Lets say I have a query's result, which is a list. del list[5] does not seem to do anything, right? I would always have to do session.delete(list[5])? But what about when I created a mapped object, which is not yet persistent, how would I delete that? Just del someobject? Kind Regards Sebastian -- 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] 'NoneType' object has no attribute '_sa_iterator' exception
Well seems like the project you want to access is just None. SA can't do anything with it. I had a similar problem recently, it was bacause the objects got garbage collected before being accessed. A testcase would be really helpful. On Sat, 27 Mar 2010 17:42:20 +0100, Fernando Takai wrote: Hi all! I'm running SQLAlchemy 0.6b2 for a while and i've seem some strange exception (AttributeError: 'NoneType' object has no attribute '_sa_iterator') happening one in a while. This is my stacktrace: Traceback (most recent call last): File "/usr/lib/python2.5/threading.py", line 486, in __bootstrap_inner self.run() File "/usr/lib/python2.5/threading.py", line 446, in run self.__target(*self.__args, **self.__kwargs) File "./db/models/job.py", line 109, in run func(self) File "./queue/queue.py", line 284, in job_finished job = session.merge(job) File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.6beta2-py2.5-linux-x86_64.egg/sqlalchemy/orm/session.py", line 1126, in merge load=load, _recursive=_recursive) File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.6beta2-py2.5-linux-x86_64.egg/sqlalchemy/orm/session.py", line 1188, in _merge prop.merge(self, state, state_dict, merged_state, merged_dict, load, _recursive) File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.6beta2-py2.5-linux-x86_64.egg/sqlalchemy/orm/properties.py", line 681, in merge obj = session._merge(current_state, current_dict, load=load, _recursive=_recursive) File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.6beta2-py2.5-linux-x86_64.egg/sqlalchemy/orm/session.py", line 1188, in _merge prop.merge(self, state, state_dict, merged_state, merged_dict, load, _recursive) File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.6beta2-py2.5-linux-x86_64.egg/sqlalchemy/orm/properties.py", line 661, in merge for current in instances: File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.6beta2-py2.5-linux-x86_64.egg/sqlalchemy/orm/collections.py", line 570, in __iter__ return iter(getattr(self._data(), '_sa_iterator')()) AttributeError: 'NoneType' object has no attribute '_sa_iterator' I don't have a good test for this, but i'm trying to create one. Does anyone knows why this is happening? Thanks! -- Sebastian Elsner-pipeline td - r i s e | fx t: +49 30 20180300 sebast...@risefx.com c: +49 175 3365739 www.risefx.com r i s e | fx GmbH Schlesische Strasse 28 Aufgang B, 10997 Berlin Geschäftsführer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] implications of weak_identity_map
Hello Michael, hello Conor, thanks for the detailed help, obviously this is a problem of PyQt. I tested Conors suggestion with the strong references this morning, no crashes, this is my solution for now. So its like you said, the objects pointed to get garbage collected prematurely. I would never have thought I would say this, but now I'd like to go back to c++ :) I am already using the newest build of PyQt, so I guess I will post on the mailing list there. Thank you, Sebastian On Wed, 24 Mar 2010 21:06:10 +0100, Michael Bayer wrote: Sebastian Elsner wrote: My first question is: What exactly is the commit doing to the list returned from the query so that pointers to other objects are "lost" (python.exe will crash on me then)? The commit expires all attributes by default, since the transaction is committed, and upon next access will be loaded again from the database. Feel free to turn this flag off if you don't want the reload. There's no reason why any of this would crash the interpreter, however. It only means your model will refresh its information from the database. The expiration was the problem. As soon as I turned it off, the errors and crashes went away I was getting: Attribute Error: "SomeClass" object has no attribute '_sa_instance_state' This happened when: list=session.query(SomeClass).all() list.somerelation.append(SomeRelatedClassInstance) session.commit() The docs state: expire_on_commit Defaults to True. When True, all instances will be fully expired after each commit(), so that all attribute/object access subsequent to a completed transaction will load from the most recent database state. This means, when I access an expired attribute it will issue another query creating a new instance of the attribute/relation I wanted to follow? Subsequently the memory address will change? Do I understand this right? I am asking this, because the Qt Tree i am using to display the data heavily relies on "internal pointers", so you would have a dangling pointer pointing to nowhere, which would explain the crashes. if QT is maintaining a "reference" to something using its "memory address", but is not actually recording a strong reference to the object within the python interpreter, that sure sounds like a bug to me. It would imply that to use that library, every object you generate in Python must have a strong reference maintained, or QT now references invalid ids. If I were using such a library, I'd probably do something to my classes such that any instance created automatically puts itself into a set() somewhere, using a metaclass. The object then can never be garbage collected unless you called a custom "dispose()" method that would remove it from the set. It sounds like a massive interpreter leak waiting to happen but QT seems to demand that such measures are taken. As far as the Session, as long as you have a strong reference to every object you care about, they don't go anywhere, and identity (a better term for "memory address" when we're in an interpreted language) doesn't change. The *connection* between A->B would be broken during an attribute expiration, but A and B themselves would still be present and become reattached. This is the basic idea of the identity map. So the Session is not intended to provide "strong references" to things. It's not a cache, and the fact that A points to B is only a representation of database state. If you prevent the session from expiring its representation of state, then the reference between A and B will remain. But its a little tenuous to rely upon this behavior to ensure that a third party library which requires strong references in order to keep from crashing. If the stability of your application is at stake I'd want to own that mechanism outside of my ORM. -- 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] implications of weak_identity_map
My first question is: What exactly is the commit doing to the list returned from the query so that pointers to other objects are "lost" (python.exe will crash on me then)? The commit expires all attributes by default, since the transaction is committed, and upon next access will be loaded again from the database. Feel free to turn this flag off if you don't want the reload. There's no reason why any of this would crash the interpreter, however. It only means your model will refresh its information from the database. The expiration was the problem. As soon as I turned it off, the errors and crashes went away I was getting: Attribute Error: "SomeClass" object has no attribute '_sa_instance_state' This happened when: list=session.query(SomeClass).all() list.somerelation.append(SomeRelatedClassInstance) session.commit() The docs state: expire_on_commit Defaults to True. When True, all instances will be fully expired after each commit(), so that all attribute/object access subsequent to a completed transaction will load from the most recent database state. This means, when I access an expired attribute it will issue another query creating a new instance of the attribute/relation I wanted to follow? Subsequently the memory address will change? Do I understand this right? I am asking this, because the Qt Tree i am using to display the data heavily relies on "internal pointers", so you would have a dangling pointer pointing to nowhere, which would explain the crashes. Can you please explain a bit more what the expiration does (if I did not get it right)? Or is there another solution? Is the design of my data structure (meaning having mapped classes simultaneously as tree nodes) crap? oh. What's this mean ? I can't imagine what you'd be doing there. If it were like: def foo(data): class Foo(object): data = data mapper(Foo, sometable) return Foo for x in (1, 2, 3): myobject.collection.append(foo(x)) that would be more or less insane, sure. But even then. myobject.collection is definitely not a SQLAlchemy instrumented attribute - because its contents would have to be instances of a mapped class. So even with the above (entirely unnecessary and crazy) pattern, I don't see how expiration is getting in the way. This means: I have three declarative classes: Category, Asset and Note. An Asset belongs to one category and a note belongs to one asset. This is basically a tree structure, and can be reproduced with a relational database and therefore sqlalchemy. The classes just have additional methods like: def child(self,row): return self.somerelation[row] to tell the Qt API, that i am using as a gui toolkit, which instance to use in the tree. As said I think the problem lies there, because the api keeps internal pointers to the individual items of a query result, but on a commit they are expired and then changed by another query when accessed again, so the toolkit can't find them any more. Turning off expiration is the key. if you want to know more about the crashed I can send you an test script. Thank you Sebastian -- 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] implications of weak_identity_map
Hello, This is the situation: I am using Qt with its tree view to display data coming from sqlalchemy. Mapped classes are used as nodes for the tree. Relations define the connections to children and parent. As for the QTreeViews model data structure a sqlalchemy list returned by a query is used. This works great for displaying. Appending a new instance to a relation of one of the objects in the list and committing the changes afterwards, leaves the tree structure somehow unstable, meaning the internal pointers to the instances in the list change. The problems seems to be caused by the session.commit(). If I leave it out all seems to be fine, the tree displays and populates correctly on adding instances, but of course the changes are not reflected in the DB. My first question is: What exactly is the commit doing to the list returned from the query so that pointers to other objects are "lost" (python.exe will crash on me then)? Looking for a solution I came across the sessions weak_identity_map argument, which I set to False and both committing and populating the tree seems to work fine (because strong references are kept?). But well, I do not understand the implications this will have on my application. Will it be faster/slower, will it need more memory, will I need to handle instances different? Or is it just an alternative way of doing things? Can you please enlighten me on this?! Or is there another solution? Is the design of my data structure (meaning having mapped classes simultaneously as tree nodes) crap? Thank you for helping Sebastian -- 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] distinct query
Hello, qry = session.query(AssetCategory).join(Asset).join(Shot).filter(Shot.id==1).distinct() this one already did the trick. qry = qry.filter(Shot.id==shot_id_of_interest) what did you add this for? The results seem to be identical... that generates SELECT DISTINCT "AssetCategory".id AS "AssetCategory_id" FROM "AssetCategory" JOIN "Asset" ON "AssetCategory".id = "Asset".category_id JOIN "Shot" ON "Shot".id = "Asset".shot_id WHERE "Shot".id = :id_1 Here it generates: SELECT DISTINCT "AssetCategory".id AS "AssetCategory_id", "AssetCategory".name AS "AssetCategory_name" FROM "AssetCategory" JOIN "Asset" ON "AssetCategory".id = "Asset".category_id JOIN "Shot" ON "Shot".id = "Asset".shot_id WHERE "Shot".id = :id_1 This leads to my next question: where does sqlalchemy know from on which column to use the distinct expression (apart from using the primary key?). I am using sqlalchemy 0.5.8, perhaps there is a difference?! Thanks Sebastian -- 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] distinct query
Hello,
I am stuck here with a query, it't not too complicated I think, but I dont
get it...
I have three Class/table mappings:
Shots, Assets, which belong to a Shot (1:n) and AssetCategories, which are
owned by Assets (n:1)
The objective is:
For a given shot instance get all distinct AssetCategories.
To be sure I articulate myself correct: I want all asset categories for
one shot so that there are not doublets within the categories result.
I thought I could achieve this with distinct. How do I use joins with
distinct, or is this the wrong approach?
Thanks for having a look at this!
Here are the definitions:
from sqlalchemy import create_engine, Column,Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relation, backref
engine = create_engine("sqlite:///:memory:",echo=True)
Base=declarative_base()
class Asset(Base):
__tablename__="Asset"
id=Column(Integer,primary_key=True)
shot_id = Column(Integer, ForeignKey('Shot.id'))
shot=relation(Shot,backref=backref('assets',order_by=id))
category_id = Column(Integer, ForeignKey('AssetCategory.id'))
category=relation(AssetCategory,backref=backref('assets',order_by=id))
class AssetCategory(Base):
__tablename__="AssetCategory"
id=Column(Integer,primary_key=True)
class Shot(Base):
__tablename__="Shot"
id=Column(Integer, primary_key=True)
Base.metadata.create_all(engine)
Session=sessionmaker(bind=engine)
session = Session()
Regards
Sebastian
--
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] three primary keys on association object
Oh, thanks for the pointer. There's so much to read and learn being new to
SQLAlchemy... Sorry for bugging.
On Fri, 05 Mar 2010 15:49:03 +0100, Michael Bayer
wrote:
Sebastian Elsner wrote:
Hello,
I have an association object declaratively with three primary keys, but
on
insert, the first (id) is not autoincremented. Please see test the code
below
known sqlite limitation, described at
http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html?highlight=sqlite#auto-incrementing-beahvior
from sqlalchemy import create_engine, Column,Integer, String,
ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relation, backref
engine = create_engine("sqlite:///:memory:",echo=False)
Base=declarative_base()
class Child(Base):
__tablename__="Child"
id=Column(Integer,primary_key=True)
class Association(Base):
__tablename__="Association"
id=Column(Integer,primary_key=True)
parent_id=Column(Integer,ForeignKey("Parent.id"),primary_key=True)
child_id=Column(Integer,ForeignKey("Child.id"),primary_key=True)
child=relation(Child,backref="parents")
data=Column(String(32))
def __init__(self):
self.data="some text"
class Parent(Base):
__tablename__="Parent"
id=Column(Integer, primary_key=True)
children=relation(Association,backref=backref("parent"))
Base.metadata.create_all(engine)
Session=sessionmaker(bind=engine)
session = Session()
p=Parent()
a=Association()
a.child=Child()
p.children.append(a)
session.add(p)
session.query(Parent).all()
I need the id for another 1:n realtion with the association object. What
am I missing here?
Thanks
Sebastian
--
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.
--
Sebastian Elsner-pipeline td - r i s e | fx
t: +49 30 20180300 sebast...@risefx.com
c: +49 175 3365739 www.risefx.com
r i s e | fx GmbH
Schlesische Strasse 28 Aufgang B, 10997 Berlin
Geschäftsführer: Sven Pannicke, Robert Pinnow
Handelsregister Berlin HRB 106667 B
--
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] three primary keys on association object
Hello,
I have an association object declaratively with three primary keys, but on
insert, the first (id) is not autoincremented. Please see test the code
below
from sqlalchemy import create_engine, Column,Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relation, backref
engine = create_engine("sqlite:///:memory:",echo=False)
Base=declarative_base()
class Child(Base):
__tablename__="Child"
id=Column(Integer,primary_key=True)
class Association(Base):
__tablename__="Association"
id=Column(Integer,primary_key=True)
parent_id=Column(Integer,ForeignKey("Parent.id"),primary_key=True)
child_id=Column(Integer,ForeignKey("Child.id"),primary_key=True)
child=relation(Child,backref="parents")
data=Column(String(32))
def __init__(self):
self.data="some text"
class Parent(Base):
__tablename__="Parent"
id=Column(Integer, primary_key=True)
children=relation(Association,backref=backref("parent"))
Base.metadata.create_all(engine)
Session=sessionmaker(bind=engine)
session = Session()
p=Parent()
a=Association()
a.child=Child()
p.children.append(a)
session.add(p)
session.query(Parent).all()
I need the id for another 1:n realtion with the association object. What
am I missing here?
Thanks
Sebastian
--
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.
