[sqlalchemy] Re: sAsync
In case anyone is interested, I've thrown up the code and what I could find in the way of docs at http://sasync.org/. sAsync is a package that wraps SQLAlchemy to provide asynchronous database access using SA's SQL Builder. It was originally announced to the Twisted mailing list in 2006 (http://twistedmatrix.com/pipermail/ twisted-python/2006-May/013121.html) and was actively maintained by Ed Suominen (is he still around?) until March of 2008. I hope someone will find this package useful. Note that it is released under GPL. Matthew -- 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] The correct usage of use_alt to avoid circular dependency
I want to create a table, say:
employee_table = Table(
'employee',metadata,
Column('id',Integer,primary_key=True),
Column('name',String(255))
)
staffGroup_Table = Table(
'role',metadata,
Column('manager',None,ForeignKey('employee.id')),
Column('worker',None,ForeignKey('employee.id')),
Column('janitorr',None,ForeignKey('employee.id'))
)
metadata.create_all()
however this will generate circular dependency, I tried to use use_alt
= True with ForeignKey constraint , but no luck.
What is the correct way of creating table for this kind of situation :
"a table refers to another table with a composite foreign keys on the
same column?"
Thank you!
--
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: (Fixed) error connecting to remote mysql database
You were all right. I created some functions and one of them creates a connect string from parameters read from a config file. One of these is the database port. When I checked the function in the external library I saw that I didn't include it in the return. mysql://user:passw...@127.0.0.1/database Because I use it for a few months now without problems I assumed there were no problems with it because I always used default ports. Fixed it and it works like sunshine :-) Thanks for all your input. I couldn't see the trees through the forest anymore, as we say in my country. On Mar 25, 8:29 pm, "Michael Bayer" wrote: > > > Is this a known issue or should I report this as a bug? > > SQLAlchemy calls MySQLdb.connect directly with the parameters you give it, > so nothing on SQLA end. you would have to share with us the exact > parameters you pass to MySQLdb.connect, versus those you pass to > create_engine(), and the difference will be apparent. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: want to suppress automatic refresh
keith cascio wrote: > Michael > > On Mar 25, 1:50 pm, "Michael Bayer" wrote: >> SQLA uses the DBAPI in its default mode of "autocommit=False" and is >> always going to issue flushes followed by a COMMIT or ROLLBACK. >> There's also a ROLLBACK which occurs automatically via the connection >> pool and you can turn that one off with a connection pool flag. But >> these operations are practically free with MyISAM so there's no overhead >> consideration. > > Thank you for the informative explanation. However, BEGIN/COMMIT/ > ROLLBACK are absolutely not free in my case. I'm dealing with > significant network latency because the database server is on another > continent and also the server itself is beleaguered. It is very > expensive to touch the database at all, even for what amounts to a "no- > op". I want to turn off the issuing of transaction statements. I am > able to do so with other database abstraction layer software. SQLAlchemy also doesn't issue BEGIN.You might want to look at setting autocommit to false on your MySQLdb connection, since that's the layer that would be sending out BEGIN. > > Thank you for all your help, > Keith > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] sAsync
Is anyone here familiar with sAsync, developed some years ago by Ed Suominen? It used to be hosted at http://foss.eepatents.com/sAsync/, but that site is no longer active. If no one is actively maintaining this project, would there be any objections to me resurrecting the project? On first tests, it seems to work fine with SA 0.6 with only a tweak to get it to recognize the version. Matthew -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: want to suppress automatic refresh
keith cascio wrote: > Michael > > On Mar 25, 1:50 pm, "Michael Bayer" wrote: >> SQLA uses the DBAPI in its default mode of "autocommit=False" and is >> always going to issue flushes followed by a COMMIT or ROLLBACK. >> There's also a ROLLBACK which occurs automatically via the connection >> pool and you can turn that one off with a connection pool flag. But >> these operations are practically free with MyISAM so there's no overhead >> consideration. > > Thank you for the informative explanation. However, BEGIN/COMMIT/ > ROLLBACK are absolutely not free in my case. I'm dealing with > significant network latency because the database server is on another > continent and also the server itself is beleaguered. It is very > expensive to touch the database at all, even for what amounts to a "no- > op". I want to turn off the issuing of transaction statements. I am > able to do so with other database abstraction layer software. its not an option in SQLAlchemy unless you want to subclass/monkeypatch the MySQL dialect. > > Thank you for all your help, > Keith > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: want to suppress automatic refresh
Michael On Mar 25, 1:50 pm, "Michael Bayer" wrote: > SQLA uses the DBAPI in its default mode of "autocommit=False" and is always > going to issue flushes followed by a COMMIT or ROLLBACK. There's also a > ROLLBACK which occurs automatically via the connection pool and you can turn > that one off with a connection pool flag. But these operations are > practically free with MyISAM so there's no overhead consideration. Thank you for the informative explanation. However, BEGIN/COMMIT/ ROLLBACK are absolutely not free in my case. I'm dealing with significant network latency because the database server is on another continent and also the server itself is beleaguered. It is very expensive to touch the database at all, even for what amounts to a "no- op". I want to turn off the issuing of transaction statements. I am able to do so with other database abstraction layer software. Thank you for all your help, Keith -- 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: want to suppress automatic refresh
Michael On Mar 25, 1:27 pm, keith cascio wrote: > However, now that I did, things are more complicated, and SQLAlchemy 0.5 > complains. > > 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 BEGIN > 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 UPDATE > xxx SET yyy=now() WHERE xxx.z = %s AND xxx.a = %s > 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 [193302, > None] > 2010-03-25 13:19:02,050 INFO sqlalchemy.engine.base.Engine.0x...f054 ROLLBACK > Traceback (most recent call last): > . > . > . > File "sqlalchemy/orm/mapper.py", line 1401, in _save_obj > sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not > match number of objects updated 1 > > I will begin to investigate how to avoid that error. I'd appreciate any > advice or hints. Perhaps I found the right solution for my case. Whenever I construct a new mapped object o, and session.add(o), and session.commit(o), I also call session.expire(o). Now my program runs to completion without fatal errors AND produces correct output AND issues the minimal number of SELECT statements to the poor database. That is my goal. -- Keith -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: want to suppress automatic refresh
keith cascio wrote: > > Also, I would like to know how I prevent > SQLAlchemy from issuing BEGIN and COMMIT statements to the database. > This is a MySQL database with MyISAM tables that have no transaction > support. This is an overloaded, remote database, and touching it is > expensive. SQLA uses the DBAPI in its default mode of "autocommit=False" and is always going to issue flushes followed by a COMMIT or ROLLBACK. There's also a ROLLBACK which occurs automatically via the connection pool and you can turn that one off with a connection pool flag. But these operations are practically free with MyISAM so there's no overhead consideration. -- 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: want to suppress automatic refresh
> 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 BEGIN > 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 UPDATE > xxx SET yyy=now() WHERE xxx.z = %s AND xxx.a = %s > 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 [193302, > None] > 2010-03-25 13:19:02,050 INFO sqlalchemy.engine.base.Engine.0x...f054 ROLLBACK > Traceback (most recent call last): > . > . > . > File "sqlalchemy/orm/mapper.py", line 1401, in _save_obj > sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not > match number of objects updated 1 -- 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: want to suppress automatic refresh
Michael On Mar 25, 12:33 pm, "Michael Bayer" wrote: > nothing ever "refreshes" automatically. only things that have been > "expired", or were never loaded in the first place, are loaded when requested. Good to know. > to reduce expirations, > readhttp://www.sqlalchemy.org/docs/session.html#committing. This is exactly what I was looking for, specifically "To disable this behavior, configure sessionmaker() with expire_on_commit=False." However, now that I did, things are more complicated, and SQLAlchemy 0.5 complains. 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 BEGIN 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 UPDATE xxx SET yyy=now() WHERE xxx.z = %s AND xxx.a = %s 2010-03-25 13:19:02,049 INFO sqlalchemy.engine.base.Engine.0x...f054 [193302, None] 2010-03-25 13:19:02,050 INFO sqlalchemy.engine.base.Engine.0x...f054 ROLLBACK Traceback (most recent call last): . . . File "sqlalchemy/orm/mapper.py", line 1401, in _save_obj sqlalchemy.orm.exc.ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 I will begin to investigate how to avoid that error. I'd appreciate any advice or hints. Also, I would like to know how I prevent SQLAlchemy from issuing BEGIN and COMMIT statements to the database. This is a MySQL database with MyISAM tables that have no transaction support. This is an overloaded, remote database, and touching it is expensive. > Also, if applicable, consider using fewer database-level defaults to populate > columns on flush - these values are necessarily read in when you request to > see them. Good to know, but this was not my specific problem. > To increase what's loaded in a single query is a more elaborate topic which > involves the specifics of the mappings you are using. Usually "eager > loading" is the first place to start. If you're using joined table > inheritance, read the mapper docs on configuring "with polymorphic". Also not my specific problem. Thanks, Keith -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: insert defaults
patrick wrote:
> Yes, but then my descriptor is washed away. I'm trying to make it
> like a column property on 'get' and a descriptor enabled property on
> 'set'.
here's an example of a full round trip of data going in through a func.()
and out through a func.() - use this recipe:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite://', echo=True)
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
data = Column(String)
_some_uppercase_thing = column_property(func.lower(data))
def _get_some_uppercase_thing(self):
return self._some_uppercase_thing
def _set_some_uppercase_thing(self, data):
self.data = func.upper(data)
some_uppercase_thing = synonym(
'_some_uppercase_thing',
descriptor=property(
_get_some_uppercase_thing,
_set_some_uppercase_thing
)
)
Base.metadata.create_all(engine)
sess = sessionmaker(engine)()
sess.add_all([
Foo(some_uppercase_thing="value 1"),
Foo(some_uppercase_thing="value 2"),
Foo(some_uppercase_thing="value 3"),
Foo(some_uppercase_thing="value 4"),
])
sess.commit()
assert sess.query(Foo.some_uppercase_thing).all() == [
("value 1", ),
("value 2", ),
("value 3", ),
("value 4", ),
]
assert
sess.query(Foo.some_uppercase_thing).filter(Foo.some_uppercase_thing ==
'value 2').all() == [
("value 2", ),
]
assert sess.execute(select([Foo.__table__.c.data])).fetchall() == [
("VALUE 1", ),
("VALUE 2", ),
("VALUE 3", ),
("VALUE 4", ),
]
>
> On Mar 25, 3:27 pm, "Michael Bayer" wrote:
>> patrick wrote:
>> > In the past I assigned
>> > Matrix.text=column_property(select(["uncompress(compressed)"]),deferred=True)
>> > I could probably write a little SqlSoup to do this select statement,
>> > but is there a way I can integrate this all well? The way I'm doing
>> > this feels very sloppy. SqlAlchemy is powerful, but can be very
>> > complicated.
>>
>> you should map an attribute directly to
>> column_property(uncompress(table.c.compressed)). if you want it only
>> to
>> fire when you read it, use deferred() instead of column_property().
>>
>>
>>
>> > On Mar 5, 8:30 pm, Michael Bayer wrote:
>> >> On Mar 5, 2010, at 6:44 PM, patrick wrote:
>>
>> >> > Well it's something between the two. The instance variable
>> >> > "compressed" will always be NULL when adding or updating an
>> instance,
>> >> > but I want it to infer a value from another instance variable. When
>> >> > inserting... the value of 'compressed' in the sql statement needs
>> to
>> >> > be the raw SQL string "COMPRESS('%s')" % instance.text. It isn't
>> >> > simply running it through a function... it's dynamically creating
>> the
>> >> > column value from the object's instance variables upon insert or
>> >> > update. MySQL has some funky compression function so the insert
>> value
>> >> > for the column has to be raw sql.
>>
>> >> so do a before_insert() mapper extension and set the attribute as
>> needed
>> >> to "func.compressed(instance.text)". Or do the same at the object
>> >> level, i.e user sets myobject.foo, "foo" is a descriptor-enabled
>> method
>> >> which then sets myobject.bar = func.compressed(foo) or whatever.
>>
>> >> the technique here
>> >> ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda
>>
>> >> > On Mar 4, 3:30 pm, "Michael Bayer"
>> wrote:
>> >> >> patrick wrote:
>> >> >>> Hey,
>> >> >>> I'm trying to create dynamic defaults for columns ala "http://
>> >> >>>www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct...".
>> >> >>> MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to
>> >> >>> leverage. I don't want to compress with python's zlib because I
>> >> have
>> >> >>> legacy tables that were compressed using MySQL (which has a weird
>> >> non-
>> >> >>> standard zip header and body), and I need to interface with them.
>> >> >>> Anyway, during an insert or update, I want to grab the 'text'
>> >> variable
>> >> >>> from the instance object and insert it into the database like:
>> >> >>> COMPRESS("the text value"). Obviously context.current_parameters
>> is
>> >> >>> not the appropriate object, but I can't figure out if it's
>> possible
>> >> to
>> >> >>> access the instance being inserted/updated.
>>
>> >> >> are you trying to create a *default* value for an INSERT/UPDATE
>> when
>> >> NULL
>> >> >> would otherwise be passed, or are you trying to run all
>> >> incoming/outgoing
>> >> >> data through a SQL function ? those are two completely separate
>> >> topics.
>>
>> >> >>> def compress_text(context):
>> >> >>> return "COMPRESS('%s')" % context.current_parameters['text']
>>
>> >> >>> c
Re: [sqlalchemy] Re: (Workaround found and SA bug with localhost?) error connecting to remote mysql database
Richard de Koning wrote:
> I've done some troubleshooting and these are my preliminary
> conclusions.
>
> A ssh-tunnel is used to reach the remote MySQL database server that
> only runs on 127.0.0.1 (localhost).
> Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
> port 3306.
>
> When I connect with MySQLdb.connect and create an normal SQL-
> statement from a Python-script it works as it should.
> sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
> lname)
>
> So the problem must be somewhere in Sqlalchemy. Maybe it has anything
> to do with 'localhost' somewhere in the code . When I initially ran
> the query on port 3307 the data was inserted in the local development-
> database that was running on the standard MySQL 3306 port. I'll use
> the normal SQL as a workaround, but I would prefer to do it in SA.
>
> Is this a known issue or should I report this as a bug?
>
>
My experience has been that the MySQL client library interprets
'localhost' to use the local UNIX socket (e.g. /var/run/mysql/...)
instead of TCP/IP. I would recommend using '127.0.0.1' or equivalent as
the host.
-Conor
--
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: insert defaults
Yes, but then my descriptor is washed away. I'm trying to make it
like a column property on 'get' and a descriptor enabled property on
'set'.
On Mar 25, 3:27 pm, "Michael Bayer" wrote:
> patrick wrote:
> > In the past I assigned
> > Matrix.text=column_property(select(["uncompress(compressed)"]),deferred=True)
> > I could probably write a little SqlSoup to do this select statement,
> > but is there a way I can integrate this all well? The way I'm doing
> > this feels very sloppy. SqlAlchemy is powerful, but can be very
> > complicated.
>
> you should map an attribute directly to
> column_property(uncompress(table.c.compressed)). if you want it only to
> fire when you read it, use deferred() instead of column_property().
>
>
>
> > On Mar 5, 8:30 pm, Michael Bayer wrote:
> >> On Mar 5, 2010, at 6:44 PM, patrick wrote:
>
> >> > Well it's something between the two. The instance variable
> >> > "compressed" will always be NULL when adding or updating an instance,
> >> > but I want it to infer a value from another instance variable. When
> >> > inserting... the value of 'compressed' in the sql statement needs to
> >> > be the raw SQL string "COMPRESS('%s')" % instance.text. It isn't
> >> > simply running it through a function... it's dynamically creating the
> >> > column value from the object's instance variables upon insert or
> >> > update. MySQL has some funky compression function so the insert value
> >> > for the column has to be raw sql.
>
> >> so do a before_insert() mapper extension and set the attribute as needed
> >> to "func.compressed(instance.text)". Or do the same at the object
> >> level, i.e user sets myobject.foo, "foo" is a descriptor-enabled method
> >> which then sets myobject.bar = func.compressed(foo) or whatever.
>
> >> the technique here
> >> ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda
>
> >> > On Mar 4, 3:30 pm, "Michael Bayer" wrote:
> >> >> patrick wrote:
> >> >>> Hey,
> >> >>> I'm trying to create dynamic defaults for columns ala "http://
> >> >>>www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct...".
> >> >>> MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to
> >> >>> leverage. I don't want to compress with python's zlib because I
> >> have
> >> >>> legacy tables that were compressed using MySQL (which has a weird
> >> non-
> >> >>> standard zip header and body), and I need to interface with them.
> >> >>> Anyway, during an insert or update, I want to grab the 'text'
> >> variable
> >> >>> from the instance object and insert it into the database like:
> >> >>> COMPRESS("the text value"). Obviously context.current_parameters is
> >> >>> not the appropriate object, but I can't figure out if it's possible
> >> to
> >> >>> access the instance being inserted/updated.
>
> >> >> are you trying to create a *default* value for an INSERT/UPDATE when
> >> NULL
> >> >> would otherwise be passed, or are you trying to run all
> >> incoming/outgoing
> >> >> data through a SQL function ? those are two completely separate
> >> topics.
>
> >> >>> def compress_text(context):
> >> >>> return "COMPRESS('%s')" % context.current_parameters['text']
>
> >> >>> class Tree(BaseStruct, Base):
> >> >>> __tablename__ = 'tree'
> >> >>> __table_args__ = (
> >> >>> {'autoload':True}
> >> >>> )
>
> >> >>> compressed =
> >> >>> deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text))
> >> >>> text =
> >> >>> column_property(select(["UNCOMPRESS(compressed)"]),deferred=True)
>
> >> >>> Is this possible with 0.5.7?
>
> >> >>> --
> >> >>> You received this message because you are subscribed to the Google
> >> Groups
> >> >>> "sqlalchemy" group.
> >> >>> To post to this group, send email to sqlalch...@googlegroups.com.
> >> >>> To unsubscribe from this group, send email to
> >> >>> sqlalchemy+unsubscr...@googlegroups.com.
> >> >>> For more options, visit this group at
> >> >>>http://groups.google.com/group/sqlalchemy?hl=en.
>
> >> > --
> >> > You received this message because you are subscribed to the Google
> >> Groups "sqlalchemy" group.
> >> > To post to this group, send email to sqlalch...@googlegroups.com.
> >> > To unsubscribe from this group, send email to
> >> sqlalchemy+unsubscr...@googlegroups.com.
> >> > For more options, visit this group
> >> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group at
> >http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
Re: [sqlalchemy] want to suppress automatic refresh
keith cascio wrote: > Hi. I use declarative orm with session. With my current > configuration, SQLAlchemy refreshes more often than I'd like. In > other words, when I look at the echo log, I see more SELECT statements > than I'd like. It issues a SELECT when I read an attribute, but I > don't want it to do that. What is the best way to suppress these > automatic refreshes? nothing ever "refreshes" automatically. only things that have been "expired", or were never loaded in the first place, are loaded when requested. to reduce expirations, read http://www.sqlalchemy.org/docs/session.html#committing . Also, if applicable, consider using fewer database-level defaults to populate columns on flush - these values are necessarily read in when you request to see them. To increase what's loaded in a single query is a more elaborate topic which involves the specifics of the mappings you are using. Usually "eager loading" is the first place to start. If you're using joined table inheritance, read the mapper docs on configuring "with polymorphic". If your loads are specific to many-to-one relationships, use 0.6beta2. Fewer loads take place in the latest version. > > Thanks, > Keith > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: (Workaround found and SA bug with localhost?) error connecting to remote mysql database
Richard de Koning wrote:
> I've done some troubleshooting and these are my preliminary
> conclusions.
>
> A ssh-tunnel is used to reach the remote MySQL database server that
> only runs on 127.0.0.1 (localhost).
> Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
> port 3306.
>
> When I connect with MySQLdb.connect and create an normal SQL-
> statement from a Python-script it works as it should.
> sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
> lname)
>
> So the problem must be somewhere in Sqlalchemy. Maybe it has anything
> to do with 'localhost' somewhere in the code . When I initially ran
> the query on port 3307 the data was inserted in the local development-
> database that was running on the standard MySQL 3306 port. I'll use
> the normal SQL as a workaround, but I would prefer to do it in SA.
>
> Is this a known issue or should I report this as a bug?
SQLAlchemy calls MySQLdb.connect directly with the parameters you give it,
so nothing on SQLA end. you would have to share with us the exact
parameters you pass to MySQLdb.connect, versus those you pass to
create_engine(), and the difference will be apparent.
>
>
>
> On Mar 25, 5:24 pm, Richard de Koning wrote:
>> I'm pretty new using sqlalchemy and I will probably ask some smart and
>> a lot of silly questions.
>>
>> At the moment I'm having problems with connecting to remote MySQL
>> databases. If I run the updates on a local database everything works
>> fine.
>>
>> When I try to connect to a remote database I keep getting the
>> following errors:
>>
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in
>> connect
>> return _ConnectionFairy(self).checkout()
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in
>> __init__
>> rec = self._connection_record = pool.get()
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in
>> get
>> return self.do_get()
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 639, in
>> do_get
>> con = self.create_connection()
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 122, in
>> create_connection
>> return _ConnectionRecord(self)
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 198, in
>> __init__
>> self.connection = self.__connect()
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 261, in
>> __connect
>> connection = self.__pool._creator()
>> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py",
>> line 80, in connect
>> raise exc.DBAPIError.instance(None, None, e)
>> OperationalError: (OperationalError) (2003, "Can't connect to MySQL
>> server on 'xxx.xxx.xxx.xxx' (111)") None None
>>
>> I checked the userrights. Tried with root and installed sqlalchemy and
>> python-bindings for mysql on the server. I also tried on several
>> servers with different OS-versions, but I can't see anything wrong.
>> Manual logins with mysql-client to the servers work.
>>
>> Can anybody give any pointers how to solve this?
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: insert defaults
patrick wrote:
> In the past I assigned
> Matrix.text=column_property(select(["uncompress(compressed)"]),deferred=True)
> I could probably write a little SqlSoup to do this select statement,
> but is there a way I can integrate this all well? The way I'm doing
> this feels very sloppy. SqlAlchemy is powerful, but can be very
> complicated.
you should map an attribute directly to
column_property(uncompress(table.c.compressed)). if you want it only to
fire when you read it, use deferred() instead of column_property().
>
> On Mar 5, 8:30 pm, Michael Bayer wrote:
>> On Mar 5, 2010, at 6:44 PM, patrick wrote:
>>
>> > Well it's something between the two. The instance variable
>> > "compressed" will always be NULL when adding or updating an instance,
>> > but I want it to infer a value from another instance variable. When
>> > inserting... the value of 'compressed' in the sql statement needs to
>> > be the raw SQL string "COMPRESS('%s')" % instance.text. It isn't
>> > simply running it through a function... it's dynamically creating the
>> > column value from the object's instance variables upon insert or
>> > update. MySQL has some funky compression function so the insert value
>> > for the column has to be raw sql.
>>
>> so do a before_insert() mapper extension and set the attribute as needed
>> to "func.compressed(instance.text)". Or do the same at the object
>> level, i.e user sets myobject.foo, "foo" is a descriptor-enabled method
>> which then sets myobject.bar = func.compressed(foo) or whatever.
>>
>> the technique here
>> ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda
>>
>>
>>
>> > On Mar 4, 3:30 pm, "Michael Bayer" wrote:
>> >> patrick wrote:
>> >>> Hey,
>> >>> I'm trying to create dynamic defaults for columns ala "http://
>> >>>www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct...".
>> >>> MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to
>> >>> leverage. I don't want to compress with python's zlib because I
>> have
>> >>> legacy tables that were compressed using MySQL (which has a weird
>> non-
>> >>> standard zip header and body), and I need to interface with them.
>> >>> Anyway, during an insert or update, I want to grab the 'text'
>> variable
>> >>> from the instance object and insert it into the database like:
>> >>> COMPRESS("the text value"). Obviously context.current_parameters is
>> >>> not the appropriate object, but I can't figure out if it's possible
>> to
>> >>> access the instance being inserted/updated.
>>
>> >> are you trying to create a *default* value for an INSERT/UPDATE when
>> NULL
>> >> would otherwise be passed, or are you trying to run all
>> incoming/outgoing
>> >> data through a SQL function ? those are two completely separate
>> topics.
>>
>> >>> def compress_text(context):
>> >>> return "COMPRESS('%s')" % context.current_parameters['text']
>>
>> >>> class Tree(BaseStruct, Base):
>> >>> __tablename__ = 'tree'
>> >>> __table_args__ = (
>> >>> {'autoload':True}
>> >>> )
>>
>> >>> compressed =
>> >>> deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text))
>> >>> text =
>> >>> column_property(select(["UNCOMPRESS(compressed)"]),deferred=True)
>>
>> >>> Is this possible with 0.5.7?
>>
>> >>> --
>> >>> You received this message because you are subscribed to the Google
>> Groups
>> >>> "sqlalchemy" group.
>> >>> To post to this group, send email to sqlalch...@googlegroups.com.
>> >>> To unsubscribe from this group, send email to
>> >>> sqlalchemy+unsubscr...@googlegroups.com.
>> >>> For more options, visit this group at
>> >>>http://groups.google.com/group/sqlalchemy?hl=en.
>>
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> > To post to this group, send email to sqlalch...@googlegroups.com.
>> > To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> > For more options, visit this group
>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: (Workaround found and SA bug with localhost?) error connecting to remote mysql database
That's strange. I have the same connection-string, but for me it isn't working after that. In the original script I initially connect to another database, transform the data and then create a new session to insert it in another database. I close the 1th session before creating another. > > I'm developing a little pylons application and I'm using a mysql > database. Like you, I'm using a ssh tunnel with port forwarding. My > connection is working ok with SqlAlchemy. > > This is my connection string (I would start looking from there): > sqlalchemy.url = > mysql://user:passw...@127.0.0.1:6/database > > As you can see I'm using port 6 instead of 3307 as you mentioned. > > Mariano > > > On Mar 25, 5:24 pm, Richard de Koning wrote: > > > I'm pretty new using sqlalchemy and I will probably ask some smart and > > > a lot of silly questions. > > > > At the moment I'm having problems with connecting to remote MySQL > > > databases. If I run the updates on a local database everything works > > > fine. > > > > When I try to connect to a remote database I keep getting the > > > following errors: > > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in > > > connect > > > return _ConnectionFairy(self).checkout() > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in > > > __init__ > > > rec = self._connection_record = pool.get() > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in > > > get > > > return self.do_get() > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 639, in > > > do_get > > > con = self.create_connection() > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 122, in > > > create_connection > > > return _ConnectionRecord(self) > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 198, in > > > __init__ > > > self.connection = self.__connect() > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 261, in > > > __connect > > > connection = self.__pool._creator() > > > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py", > > > line 80, in connect > > > raise exc.DBAPIError.instance(None, None, e) > > > OperationalError: (OperationalError) (2003, "Can't connect to MySQL > > > server on 'xxx.xxx.xxx.xxx' (111)") None None > > > > I checked the userrights. Tried with root and installed sqlalchemy and > > > python-bindings for mysql on the server. I also tried on several > > > servers with different OS-versions, but I can't see anything wrong. > > > Manual logins with mysql-client to the servers work. > > > > Can anybody give any pointers how to solve this? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: (Workaround found and SA bug with localhost?) error connecting to remote mysql database
Excerpts from Richard de Koning's message of Thu Mar 25 15:50:45 -0300 2010:
> I've done some troubleshooting and these are my preliminary
> conclusions.
>
> A ssh-tunnel is used to reach the remote MySQL database server that
> only runs on 127.0.0.1 (localhost).
> Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
> port 3306.
>
> When I connect with MySQLdb.connect and create an normal SQL-
> statement from a Python-script it works as it should.
> sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
> lname)
>
> So the problem must be somewhere in Sqlalchemy. Maybe it has anything
> to do with 'localhost' somewhere in the code . When I initially ran
> the query on port 3307 the data was inserted in the local development-
> database that was running on the standard MySQL 3306 port. I'll use
> the normal SQL as a workaround, but I would prefer to do it in SA.
>
> Is this a known issue or should I report this as a bug?
>
I'm developing a little pylons application and I'm using a mysql
database. Like you, I'm using a ssh tunnel with port forwarding. My
connection is working ok with SqlAlchemy.
This is my connection string (I would start looking from there):
sqlalchemy.url =
mysql://user:passw...@127.0.0.1:6/database
As you can see I'm using port 6 instead of 3307 as you mentioned.
Mariano
> On Mar 25, 5:24 pm, Richard de Koning wrote:
> > I'm pretty new using sqlalchemy and I will probably ask some smart and
> > a lot of silly questions.
> >
> > At the moment I'm having problems with connecting to remote MySQL
> > databases. If I run the updates on a local database everything works
> > fine.
> >
> > When I try to connect to a remote database I keep getting the
> > following errors:
> >
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in
> > connect
> > return _ConnectionFairy(self).checkout()
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in
> > __init__
> > rec = self._connection_record = pool.get()
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in
> > get
> > return self.do_get()
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 639, in
> > do_get
> > con = self.create_connection()
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 122, in
> > create_connection
> > return _ConnectionRecord(self)
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 198, in
> > __init__
> > self.connection = self.__connect()
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 261, in
> > __connect
> > connection = self.__pool._creator()
> > File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py",
> > line 80, in connect
> > raise exc.DBAPIError.instance(None, None, e)
> > OperationalError: (OperationalError) (2003, "Can't connect to MySQL
> > server on 'xxx.xxx.xxx.xxx' (111)") None None
> >
> > I checked the userrights. Tried with root and installed sqlalchemy and
> > python-bindings for mysql on the server. I also tried on several
> > servers with different OS-versions, but I can't see anything wrong.
> > Manual logins with mysql-client to the servers work.
> >
> > Can anybody give any pointers how to solve this?
>
--
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] want to suppress automatic refresh
Hi. I use declarative orm with session. With my current configuration, SQLAlchemy refreshes more often than I'd like. In other words, when I look at the echo log, I see more SELECT statements than I'd like. It issues a SELECT when I read an attribute, but I don't want it to do that. What is the best way to suppress these automatic refreshes? Thanks, Keith -- 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: (Workaround found and SA bug with localhost?) error connecting to remote mysql database
I've done some troubleshooting and these are my preliminary
conclusions.
A ssh-tunnel is used to reach the remote MySQL database server that
only runs on 127.0.0.1 (localhost).
Commands sent to 127.0.0.1 port 3307 are forwarded to remote server on
port 3306.
When I connect with MySQLdb.connect and create an normal SQL-
statement from a Python-script it works as it should.
sql = "INSERT INTO data (fname, lname) VALUES ('%s', '%s') " % (fname,
lname)
So the problem must be somewhere in Sqlalchemy. Maybe it has anything
to do with 'localhost' somewhere in the code . When I initially ran
the query on port 3307 the data was inserted in the local development-
database that was running on the standard MySQL 3306 port. I'll use
the normal SQL as a workaround, but I would prefer to do it in SA.
Is this a known issue or should I report this as a bug?
On Mar 25, 5:24 pm, Richard de Koning wrote:
> I'm pretty new using sqlalchemy and I will probably ask some smart and
> a lot of silly questions.
>
> At the moment I'm having problems with connecting to remote MySQL
> databases. If I run the updates on a local database everything works
> fine.
>
> When I try to connect to a remote database I keep getting the
> following errors:
>
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in
> connect
> return _ConnectionFairy(self).checkout()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in
> __init__
> rec = self._connection_record = pool.get()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in
> get
> return self.do_get()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 639, in
> do_get
> con = self.create_connection()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 122, in
> create_connection
> return _ConnectionRecord(self)
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 198, in
> __init__
> self.connection = self.__connect()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 261, in
> __connect
> connection = self.__pool._creator()
> File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py",
> line 80, in connect
> raise exc.DBAPIError.instance(None, None, e)
> OperationalError: (OperationalError) (2003, "Can't connect to MySQL
> server on 'xxx.xxx.xxx.xxx' (111)") None None
>
> I checked the userrights. Tried with root and installed sqlalchemy and
> python-bindings for mysql on the server. I also tried on several
> servers with different OS-versions, but I can't see anything wrong.
> Manual logins with mysql-client to the servers work.
>
> Can anybody give any pointers how to solve this?
--
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: insert defaults
Thanks. I might have figured out a solution. I can get it to assign
func.compressed to the column attribute, but I can't get it to load
the text uncompressed now.
class BaseStruct(object):
def _set_text(self, text):
self.compressed = func.compress(text)
def _get_text(self):
return select(["uncompress(compressed)"])
text = property(_get_text, _set_text)
class Matrix(BaseStruct, Base):
__tablename__ = 'matrix'
__table_args__ = (
{'autoload':True}
)
compressed = deferred(Column(Binary()))
text = BaseStruct.text
>>> s = Session()
>>> m = s.query(Matrix).get(1)
>>> m.compressed
>>> m.text='Blah'
>>> m.compressed
So after assigning the text... I get what I want. The column mapped
attribute is assigned func.compress(text). But I also need to
uncompress the field to read it sometimes.
>>> s = Session()
>>> m = s.query(Matrix).get(7)
>>> m.text
In the past I assigned
Matrix.text=column_property(select(["uncompress(compressed)"]),deferred=True)
I could probably write a little SqlSoup to do this select statement,
but is there a way I can integrate this all well? The way I'm doing
this feels very sloppy. SqlAlchemy is powerful, but can be very
complicated.
On Mar 5, 8:30 pm, Michael Bayer wrote:
> On Mar 5, 2010, at 6:44 PM, patrick wrote:
>
> > Well it's something between the two. The instance variable
> > "compressed" will always be NULL when adding or updating an instance,
> > but I want it to infer a value from another instance variable. When
> > inserting... the value of 'compressed' in the sql statement needs to
> > be the raw SQL string "COMPRESS('%s')" % instance.text. It isn't
> > simply running it through a function... it's dynamically creating the
> > column value from the object's instance variables upon insert or
> > update. MySQL has some funky compression function so the insert value
> > for the column has to be raw sql.
>
> so do a before_insert() mapper extension and set the attribute as needed to
> "func.compressed(instance.text)". Or do the same at the object level, i.e
> user sets myobject.foo, "foo" is a descriptor-enabled method which then sets
> myobject.bar = func.compressed(foo) or whatever.
>
> the technique here
> ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda
>
>
>
> > On Mar 4, 3:30 pm, "Michael Bayer" wrote:
> >> patrick wrote:
> >>> Hey,
> >>> I'm trying to create dynamic defaults for columns ala "http://
> >>>www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct...".
> >>> MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to
> >>> leverage. I don't want to compress with python's zlib because I have
> >>> legacy tables that were compressed using MySQL (which has a weird non-
> >>> standard zip header and body), and I need to interface with them.
> >>> Anyway, during an insert or update, I want to grab the 'text' variable
> >>> from the instance object and insert it into the database like:
> >>> COMPRESS("the text value"). Obviously context.current_parameters is
> >>> not the appropriate object, but I can't figure out if it's possible to
> >>> access the instance being inserted/updated.
>
> >> are you trying to create a *default* value for an INSERT/UPDATE when NULL
> >> would otherwise be passed, or are you trying to run all incoming/outgoing
> >> data through a SQL function ? those are two completely separate topics.
>
> >>> def compress_text(context):
> >>> return "COMPRESS('%s')" % context.current_parameters['text']
>
> >>> class Tree(BaseStruct, Base):
> >>> __tablename__ = 'tree'
> >>> __table_args__ = (
> >>> {'autoload':True}
> >>> )
>
> >>> compressed =
> >>> deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text))
> >>> text =
> >>> column_property(select(["UNCOMPRESS(compressed)"]),deferred=True)
>
> >>> Is this possible with 0.5.7?
>
> >>> --
> >>> You received this message because you are subscribed to the Google Groups
> >>> "sqlalchemy" group.
> >>> To post to this group, send email to sqlalch...@googlegroups.com.
> >>> To unsubscribe from this group, send email to
> >>> sqlalchemy+unsubscr...@googlegroups.com.
> >>> For more options, visit this group at
> >>>http://groups.google.com/group/sqlalchemy?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, v
[sqlalchemy] error connecting to remote mysql database
I'm pretty new using sqlalchemy and I will probably ask some smart and a lot of silly questions. At the moment I'm having problems with connecting to remote MySQL databases. If I run the updates on a local database everything works fine. When I try to connect to a remote database I keep getting the following errors: File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 142, in connect return _ConnectionFairy(self).checkout() File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 304, in __init__ rec = self._connection_record = pool.get() File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 161, in get return self.do_get() File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 639, in do_get con = self.create_connection() File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 122, in create_connection return _ConnectionRecord(self) File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 198, in __init__ self.connection = self.__connect() File "/usr/lib/pymodules/python2.6/sqlalchemy/pool.py", line 261, in __connect connection = self.__pool._creator() File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/strategies.py", line 80, in connect raise exc.DBAPIError.instance(None, None, e) OperationalError: (OperationalError) (2003, "Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (111)") None None I checked the userrights. Tried with root and installed sqlalchemy and python-bindings for mysql on the server. I also tried on several servers with different OS-versions, but I can't see anything wrong. Manual logins with mysql-client to the servers work. Can anybody give any pointers how to solve this? -- 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] Clarification about performance and relation()
the relationship between two tables requires both the ForeignKey to be
present as well as the relationship() (relation() in 0.5) function to be
present in the mapping.
masetto wrote:
> From 30 mins to 2mins... shame :P
>
> Thanks Micheal !
>
> Forgive me, what about the other question about foreign keys?
>
>
> On Thu, Mar 25, 2010 at 3:43 PM, Michael Bayer
> wrote:
>
>> masetto wrote:
>> > Hi all,
>> >
>> > i am writing a python script which parse an xml file (python lxml) and
>> > import it into a sqlite db, and it works.
>> > The xml file size is about 30Mb and the import operation takes about
>> 15
>> > minutes (do you think is too much? is there something i can do to
>> speed
>> up
>> > the process?)
>> >
>> > This is a piece of the import function:
>> >
>> > ...
>> > for definition in definitions.getchildren(): #iterate for every xml
>> > children
>> > node
>> > defInst = SQLTableBuilder_Definition.DefinitionClass(definition)
>> #read
>> > and write on db some attribute of the node
>> > ...
>> > if subbaElem1.tag == mainNS + "platform": #another loop iterate
>> for
>> > every sub-node of the definition node
>> > platf = SQLTableBuilder_Platform.PlatformClass()
>> > platf.setPlatform(str(subbaElem1))
>> > platf.platformId_fk = defInst.defId
>> >
>> > session.add(platf)
>> > session.commit()
>> > ...
>> > session.add(defInst)
>> > session.commit()
>>
>>
>> don't commit on every node and on every sub-node. Just commit once
>> every
>> 1000 new objects or so. will save a ton of processing.
>>
>>
>>
>> >
>> > where DefinitionClass contains the attributes declaration
>> (primary_key,
>> > column(string), etc.) and a Foreign Key.
>> > There is a relation between the definition table and the platform
>> table
>> > (one
>> > or more platforms - Operating System - can be associated to a single
>> > definition) so,
>> > in the platform table, i've added the following: platformId_fk =
>> > Column('definitionId_fk', Integer, ForeignKey('definitions.defId'))
>> >
>> > All my ORM-Classes are declared within n different classes within n
>> > different python modules so, i've included the needed imports
>> everytime i
>> > needed it.
>> > And i suppose this is a problem, at least for me, sometime, because
>> when
>> i
>> > try to add: PlatformRel =
>> > relation(SQLTableBuilder_Definition.DefinitionClass,
>> backref="platform")
>> > within my platformClass, i got: 'list' object has no attribute
>> > '_sa_instance_state' :/
>> >
>> > So, i've tried to "manually" set the foreign key, as you can see
>> above.
>> In
>> > the documentation (http://www.sqlalchemy.org/docs/ormtutorial.html) i
>> > read:
>> > " SQLAlchemy is automatically aware of many-to-one/one-to-many based
>> on
>> > foreign keys." Does this mean that what i've done is correct or i'm a
>> > little
>> > confused? If i "manually" set a foreign key value, does sqlalchemy
>> > understand that a relation between two tables exists?
>> >
>> > Thanks for your attention.
>> > ---
>> > Masetto
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups
>> > "sqlalchemy" group.
>> > To post to this group, send email to sqlalch...@googlegroups.com.
>> > To unsubscribe from this group, send email to
>> > sqlalchemy+unsubscr...@googlegroups.com
>> .
>> > For more options, visit this group at
>> > http://groups.google.com/group/sqlalchemy?hl=en.
>> >
>> >
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com
>> .
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Clarification about performance and relation()
>From 30 mins to 2mins... shame :P
Thanks Micheal !
Forgive me, what about the other question about foreign keys?
On Thu, Mar 25, 2010 at 3:43 PM, Michael Bayer wrote:
> masetto wrote:
> > Hi all,
> >
> > i am writing a python script which parse an xml file (python lxml) and
> > import it into a sqlite db, and it works.
> > The xml file size is about 30Mb and the import operation takes about 15
> > minutes (do you think is too much? is there something i can do to speed
> up
> > the process?)
> >
> > This is a piece of the import function:
> >
> > ...
> > for definition in definitions.getchildren(): #iterate for every xml
> > children
> > node
> > defInst = SQLTableBuilder_Definition.DefinitionClass(definition)
> #read
> > and write on db some attribute of the node
> > ...
> > if subbaElem1.tag == mainNS + "platform": #another loop iterate for
> > every sub-node of the definition node
> > platf = SQLTableBuilder_Platform.PlatformClass()
> > platf.setPlatform(str(subbaElem1))
> > platf.platformId_fk = defInst.defId
> >
> > session.add(platf)
> > session.commit()
> > ...
> > session.add(defInst)
> > session.commit()
>
>
> don't commit on every node and on every sub-node. Just commit once every
> 1000 new objects or so. will save a ton of processing.
>
>
>
> >
> > where DefinitionClass contains the attributes declaration (primary_key,
> > column(string), etc.) and a Foreign Key.
> > There is a relation between the definition table and the platform table
> > (one
> > or more platforms - Operating System - can be associated to a single
> > definition) so,
> > in the platform table, i've added the following: platformId_fk =
> > Column('definitionId_fk', Integer, ForeignKey('definitions.defId'))
> >
> > All my ORM-Classes are declared within n different classes within n
> > different python modules so, i've included the needed imports everytime i
> > needed it.
> > And i suppose this is a problem, at least for me, sometime, because when
> i
> > try to add: PlatformRel =
> > relation(SQLTableBuilder_Definition.DefinitionClass, backref="platform")
> > within my platformClass, i got: 'list' object has no attribute
> > '_sa_instance_state' :/
> >
> > So, i've tried to "manually" set the foreign key, as you can see above.
> In
> > the documentation (http://www.sqlalchemy.org/docs/ormtutorial.html) i
> > read:
> > " SQLAlchemy is automatically aware of many-to-one/one-to-many based on
> > foreign keys." Does this mean that what i've done is correct or i'm a
> > little
> > confused? If i "manually" set a foreign key value, does sqlalchemy
> > understand that a relation between two tables exists?
> >
> > Thanks for your attention.
> > ---
> > Masetto
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com
> .
> > For more options, visit this group at
> > http://groups.google.com/group/sqlalchemy?hl=en.
> >
> >
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
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] Clarification about performance and relation()
masetto wrote:
> Hi all,
>
> i am writing a python script which parse an xml file (python lxml) and
> import it into a sqlite db, and it works.
> The xml file size is about 30Mb and the import operation takes about 15
> minutes (do you think is too much? is there something i can do to speed up
> the process?)
>
> This is a piece of the import function:
>
> ...
> for definition in definitions.getchildren(): #iterate for every xml
> children
> node
> defInst = SQLTableBuilder_Definition.DefinitionClass(definition) #read
> and write on db some attribute of the node
> ...
> if subbaElem1.tag == mainNS + "platform": #another loop iterate for
> every sub-node of the definition node
> platf = SQLTableBuilder_Platform.PlatformClass()
> platf.setPlatform(str(subbaElem1))
> platf.platformId_fk = defInst.defId
>
> session.add(platf)
> session.commit()
> ...
> session.add(defInst)
> session.commit()
don't commit on every node and on every sub-node. Just commit once every
1000 new objects or so. will save a ton of processing.
>
> where DefinitionClass contains the attributes declaration (primary_key,
> column(string), etc.) and a Foreign Key.
> There is a relation between the definition table and the platform table
> (one
> or more platforms - Operating System - can be associated to a single
> definition) so,
> in the platform table, i've added the following: platformId_fk =
> Column('definitionId_fk', Integer, ForeignKey('definitions.defId'))
>
> All my ORM-Classes are declared within n different classes within n
> different python modules so, i've included the needed imports everytime i
> needed it.
> And i suppose this is a problem, at least for me, sometime, because when i
> try to add: PlatformRel =
> relation(SQLTableBuilder_Definition.DefinitionClass, backref="platform")
> within my platformClass, i got: 'list' object has no attribute
> '_sa_instance_state' :/
>
> So, i've tried to "manually" set the foreign key, as you can see above. In
> the documentation (http://www.sqlalchemy.org/docs/ormtutorial.html) i
> read:
> " SQLAlchemy is automatically aware of many-to-one/one-to-many based on
> foreign keys." Does this mean that what i've done is correct or i'm a
> little
> confused? If i "manually" set a foreign key value, does sqlalchemy
> understand that a relation between two tables exists?
>
> Thanks for your attention.
> ---
> Masetto
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Clarification about performance and relation()
Hi all,
i am writing a python script which parse an xml file (python lxml) and
import it into a sqlite db, and it works.
The xml file size is about 30Mb and the import operation takes about 15
minutes (do you think is too much? is there something i can do to speed up
the process?)
This is a piece of the import function:
...
for definition in definitions.getchildren(): #iterate for every xml children
node
defInst = SQLTableBuilder_Definition.DefinitionClass(definition) #read
and write on db some attribute of the node
...
if subbaElem1.tag == mainNS + "platform": #another loop iterate for
every sub-node of the definition node
platf = SQLTableBuilder_Platform.PlatformClass()
platf.setPlatform(str(subbaElem1))
platf.platformId_fk = defInst.defId
session.add(platf)
session.commit()
...
session.add(defInst)
session.commit()
where DefinitionClass contains the attributes declaration (primary_key,
column(string), etc.) and a Foreign Key.
There is a relation between the definition table and the platform table (one
or more platforms - Operating System - can be associated to a single
definition) so,
in the platform table, i've added the following: platformId_fk =
Column('definitionId_fk', Integer, ForeignKey('definitions.defId'))
All my ORM-Classes are declared within n different classes within n
different python modules so, i've included the needed imports everytime i
needed it.
And i suppose this is a problem, at least for me, sometime, because when i
try to add: PlatformRel =
relation(SQLTableBuilder_Definition.DefinitionClass, backref="platform")
within my platformClass, i got: 'list' object has no attribute
'_sa_instance_state' :/
So, i've tried to "manually" set the foreign key, as you can see above. In
the documentation (http://www.sqlalchemy.org/docs/ormtutorial.html) i read:
" SQLAlchemy is automatically aware of many-to-one/one-to-many based on
foreign keys." Does this mean that what i've done is correct or i'm a little
confused? If i "manually" set a foreign key value, does sqlalchemy
understand that a relation between two tables exists?
Thanks for your attention.
---
Masetto
--
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.
