[sqlalchemy] Re: SQLAlchemy 0.5.5 MySQL Issue
Thank you. That was exactly the problem. MyISAM engine was selected as default, changed to InnoDB and now the autoflush behavior is as expected. On Nov 28, 4:36 am, Alexandre Conrad alexandre.con...@gmail.com wrote: You may be using MyISAM storage engine which doesn't support transactions and may make all flushes persistent. The other storage engine widely used is InnoDB which does support transactions. Find out in the MySQL docs how to figure out which storage engine you're using. Sent from my fantastic HTC Hero On Nov 28, 2009 7:27 AM, gizli mehm...@gmail.com wrote: I am not sure about the storage engine but here goes: mysql status -- mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2 the mysql-python version is MySQL_python-1.2.3c1 On Nov 26, 11:47 pm, Alexandre Conrad alexandre.con...@gmail.com wrote: 2009/11/27 gizli mehm...@gmail.com: With mysql however, the insert done by the query() actually does a commit.. i turned on ec... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To... -- 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: SQLAlchemy 0.5.5 MySQL Issue
I am not sure about the storage engine but here goes: mysql status -- mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2 the mysql-python version is MySQL_python-1.2.3c1 On Nov 26, 11:47 pm, Alexandre Conrad alexandre.con...@gmail.com wrote: 2009/11/27 gizli mehm...@gmail.com: With mysql however, the insert done by the query() actually does a commit.. i turned on echo, and i dont see an explicit commit but this is what seems to happen.. After the program is done, I can see Obj(5) in the mysql database. Is this a known problem? Or am I doing something wrong here? Which MySQL storage engine are you using? Alex -- 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] Concurrent Update Errors
Hi guys, I have a couple of questions about a recent concurrency test I did on SA. Someone in our team ran into a very intermittent issue where a certain operation he tried doing failed by ConcurrentUpdateError. I wrote a simple thread that can do query/update/insert/commit/rollback operations on demand and created two of those to create specific race conditions manually.. I found out some interesting stuff: If I use sqlite, two threads cannot operate on the same ORM object. E.g.: t1.update(obj) t2.update(obj) t1.commit() t2.commit() t2.update(obj) would fail with an error saying Database is locked. If I use mysql to do the same thing it goes through with no problems. The only time I could reproduce the concurrent update error is like this: t1.update(obj) t2.delete(obj) t2.commit() t1.commit() The last line will throw the error saying num updated rows is 0 where it was expecting 1. My first question is: Can this concurrent update error arise in any other situation? I also tried the following: t1.delete(obj) t2.delete(obj) t2.commit() t1.commit() This also fails with the same error saying the num deleted rows is 0 when it was expecting 1. My second question is: Should t1's transaction go through? I would think so. I understand why an update to a deleted row fails the entire transaction but I do not understand the motivation for failing an entire transaction because a row I wanted to delete is already deleted. This behavior is, as far as I can see, embedded in SA logic. So no matter which database I use, I will see this right? (unless something happens more fundamentally at the db level, like in sqlite, getting the database locked error) The final question is a design question. In a typical web application that can have 100's of simultaneous users submitting a handful of tasks, how do we avoid this kind of concurrency issues? Each request would be handled with a separate thread meaning if any of these threads try modifying the same db object, we have a potential problem. Do we need exclusive locks for this kind of situation? Does SA allow us to do that? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Concurrent Update Errors
Thanks for the excellent information Michael. Will need to think about this and post again if I have further questions. As for the delete/rm discussion, I guess it depends on your use case. I usually run rm with -f flag which fails silently if no file is found. But I get your point and you also provided a way to turn the feature off. Thanks a lot. On Sep 17, 3:59 pm, Michael Bayer mike...@zzzcomputing.com wrote: gizli wrote: t1.delete(obj) t2.delete(obj) t2.commit() t1.commit() I also forgot to mention that you can always use query.delete() or table.delete() to get a standard relational delete, though it does not offer in-python cascading. This delete translates directly to DELETE with the given criterion and doesn't care if the rows matching its condition exist or not. --~--~-~--~~~---~--~~ 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] Setting language for a db session
Hi all, I am just starting to think about this topic so please dont shoot me if I missed the obvious :). There are many varieties of database errors. So, sometimes in our code, we need to let these errors be returned to the user. E.g. we would like a french guy using this application to have a french translation of these error messages. Ideally, a single application should be able to return errors in multiple languages depending on which language the database session was setup with. From my understanding, some databases like MySQL does not support setting language for a particular DB session. Some others, such as Oracle, allows this. Is this ever considered in SA? or is our only option to write SQL code to explicitly pass the language parameter to a session? Do you guys have a better answer? Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatic Polymorphic Identity Assignment
Found two ways of doing this: 1. use metaclass: from sqlalchemy.ext.declarative import DeclarativeMeta class PolymorphicSetter(DeclarativeMeta): def __new__(cls, name, bases, dictionary): if '__mapper_args__' in dictionary.keys(): dictionary['__mapper_args__']['polymorphic_identity'] = name else: dictionary['__mapper_args__'] = dict (polymorphic_identity=name) return DeclarativeMeta.__new__(cls, name, bases, dictionary) and in my base class Task: class Task(DeclarativeBase): __metaclass__ = PolymorphicSetter ii. Use class decorators.. write a function similar to the polymorphicsetter class above and return the modified cls object.. then for every subclass of Task, use the decorator.. The advantage of the first approach is that, there is no need to do anything once the Task class is extended.. On Sep 9, 7:51 pm, gizli mehm...@gmail.com wrote: Thanks for the suggestion Wolodja but I dont think that would work. The self object is not defined at the time of class loading in python: class Test: ... qq = self.__class__.__name__ ... def fu(self): ... print fu ... Traceback (most recent call last): File stdin, line 1, in module File stdin, line 2, in Test NameError: name 'self' is not defined I will post the solution here if I find any. --~--~-~--~~~---~--~~ 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] Automatic Polymorphic Identity Assignment
Hi all,
This list has been very helpful so far, thanks a lot. I was just
wondering if there is a transparent way to assign polymorphic
identities to ORM classes using single table inheritance. Let's say we
have a base Task class:
class Task(DeclarativeBase):
__tablename__ = 'Tasks'
id = Column(Integer)
name = Column(String)
type = Column(String) etc...
__mapper_args__ = {'polymorphic_on' : type}
now I want to declare multiple Task types like this:
class MyTask(Task):
__mapper_args__ = {'polymorphic_identity': 'MyTask'}
class YourTask(Task):
__mapper_args__ = {'polymorphic_identity': 'YourTask'}
This is the recommended way in the documentation for declarative
style. I was wondering if I could get rid of the explicit
polymorphic_identity setup in the subclasses by some clever
programming trick. I want to assign the polymorphic identity to be the
class __name__ automatically if that class extends Task class. I am
not very well versed in advanced python programming like decorators or
function/class wrappers, so I wanted to seek your opinion.
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Automatic Polymorphic Identity Assignment
Thanks for the suggestion Wolodja but I dont think that would work. The self object is not defined at the time of class loading in python: class Test: ... qq = self.__class__.__name__ ... def fu(self): ... print fu ... Traceback (most recent call last): File stdin, line 1, in module File stdin, line 2, in Test NameError: name 'self' is not defined I will post the solution here if I find any. --~--~-~--~~~---~--~~ 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] Curious Problem
Hi all,
I just discovered something weird when doing concurrency testing with
my program. Before writing a simplified test case for it and really
figuring out whether its a bug with sqlalchemy (I am using 0.5.5), I
wanted to write the scenario here. Basically I was getting the
infamous Set size changed during iteration error during commit.
Multiple threads are not accessing the same session (as was in some of
the posts I have read online). Here's what I am doing:
There is a task dispatcher that queries the database, gets a list of
tasks to be done, dispatches them to separate threads. Each thread
creates its own session using the session_maker and updates the status
of a task. There are three kinds of objects in question: Task,
TaskSchedule and TaskResult. There is a one-to-many relation between
Task and TaskSchedule. There is also a one-to-many relation between
Task and TaskResult. Pseudo code:
task_dispatcher:
for task_schedule in sess.query(TaskSchedule).filter
(next_execution_time = current_time):
do_task(task_schedule.task) -- using relation
task_schedule.next_execution_time = some_value
sess.commit()
(in a new thread)
do_task(task):
sess := get_session()
sess.add(TaskResult(task.id, task_started))
sess.commit()
task.perform()
sess.merge(TaskResult(task.id, task_finished))
sess.commit()
sess.close()
Basically, I get the mysterious error for the commit in
task_dispatcher intermittently (probably 10 times out of 1000
executions). This brings me to my first question: Is it bad practice
to commit a session from within the sess.query() loop? I wanted each
task execution time update to be independent of other tasks. If this
is bad practice, then should I use sub-transactions for this purpose?
After realizing this possible pitfall, I changed the code to commit
only after the loop has finished. This lead to another interesting
result: None of my TaskResult objects got committed. Instead I saw a
lot of ROLLBACK's in the log with no errors associated with them.
I tried yet another variation. This is the one that puzzled me the
most. I tried eager loading the task relation in the TaskScheduleItem
and it worked like a charm:
task_dispatcher:
for task_schedule in sess.query(TaskScheduleItem).options(eagerload
('task')).filter(next_execution_time = current_time):
do_task(task_schedule.task) -- using relation
task_schedule.next_execution_time = some_value
sess.commit()
This time, no errors, the TaskResults appeared in database with each
commit in the do_task function.
Does anyone have an insight into this?
Thanks,
--~--~-~--~~~---~--~~
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] Autoflush setting
Hi all, I have a pretty basic question about autoflush that I could not find an answer to. By default, in sessionmaker, this is set to true. However, I have this basic scenario where my transaction involves inserting objects into session and querying for some others: add(o1) query(SomeObject) add(o2) commit() While this transaction is going on, I dont want any of the other threads to see o1. Only after commit, both objects should be visible. Does that mean i need to set autoflush to false? Is there any disadvantage of doing so since this is the default setting? Thanks. On a related note, the reason why I do not want o1 to appear in the database without o2 is because they are dependent from the UI point of view (the rendering page needs an o2 if an o1 is present). Does that mean they should have been modeled using relations and inserted using relations as well? That would mean doing the query before o1 and building o1,o2 together and that would avoid this issue. Is this why I am running into these problems that seem trivial w.r.t. autoflush? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Autoflush setting
Okay. I believe this is my mistake. I just wrote a simple test program and saw that the other threads should not see o1 or o2 until commit since I am using scoped_session. I guess there is another reason for the behavior I am seeing. On Aug 28, 10:05 pm, gizli mehm...@gmail.com wrote: Hi all, I have a pretty basic question about autoflush that I could not find an answer to. By default, in sessionmaker, this is set to true. However, I have this basic scenario where my transaction involves inserting objects into session and querying for some others: add(o1) query(SomeObject) add(o2) commit() While this transaction is going on, I dont want any of the other threads to see o1. Only after commit, both objects should be visible. Does that mean i need to set autoflush to false? Is there any disadvantage of doing so since this is the default setting? Thanks. On a related note, the reason why I do not want o1 to appear in the database without o2 is because they are dependent from the UI point of view (the rendering page needs an o2 if an o1 is present). Does that mean they should have been modeled using relations and inserted using relations as well? That would mean doing the query before o1 and building o1,o2 together and that would avoid this issue. Is this why I am running into these problems that seem trivial w.r.t. autoflush? --~--~-~--~~~---~--~~ 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] Performance Analysis
Hi all, This is not really a sqlalchemy question but I was wondering if anyone developing with sqlalchemy knows the answer. Turning on echo=True spits out all the queries that the application generates. Currently I am directing this output to a file and then looking through to derive statistics like what kind of tables are most frequently accessed. I use this to see where my program can be optimized. Are you aware of any tool built on top of sqlalchemy to do this kind of analysis and give out a performance report? Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many-to-many relations
Never mind. Using primaryjoin and secondaryjoin helped quite a bit :) --~--~-~--~~~---~--~~ 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] Many-to-many relations
Hi all,
Please help me model this scenario with many-to-many relations:
I am trying to model a directed graph. My objects are nodes: n1...nk,
and I have a bunch of directed edges: n1 -- n3, n5 -- n6, n6 -- n2
and so on. From reading the documentation, I know that the nodes will
probably be one table, and edges will probably be defined as a
secondary table in a relation. The examples in the documentation
however assume that the relationship is between two tables (left and
right) whereas all I have is nodes. My intuition was to define an edge
table like this:
edges = Table(.. Column(source, Integer, ForeignKey('Nodes.id')),
Column(target, Integer, ForeignKey('Nodes.id')))
but I could not figure out how to turn this into a relation. How can I
design a Nodes table which would give me the following two relations:
n.incoming == The list of nodes such that there exists an edge where
n = target
n.outgoing == The list of nodes such that there exists an edge where
n = source
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Reentrant data population script using sql alchemy
Thanks a lot Michael. The merge functionality is what I was looking for. This also exposed a flaw in our model I think. We keep using the auto-increment primary keys which would make merge() impossible to use in the scenario I was describing. Right? The easiest way to do auto insert or update given an object that you've loaded from a file is to use merge(): myobj = session.merge(myobj) which will do the SELECT for you. If the object is already loaded, no SELECT is emitted. So it follows then that you can SELECT all the rows of the table ahead of time, keep them referenced in a collection: all_objects = session.query(MyStuff).all() then you can merge() each element you pull from the file as needed, and no additional SQL should be emitted to fetch any of it. --~--~-~--~~~---~--~~ 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] Reentrant data population script using sql alchemy
Hi all, Forgive me if this is a very basic question but I could not find the answer anywhere. I am trying to write a data population script to setup a few tables and insert some data in them. I need to make this script reentrant.. The problem is that currently the script looks like this: get a session use metadata.create_all() to create all the tables call add(obj) on all objects that need to be inserted flush() commit() create_all api is reentrant.. However when I add() existing objects, the script fails when flush() happens with integrity error. I thought of wrapping the add() function and doing a flush after each add, catch the integrity exception and pass. However that requires me to create a new session object after each flush (since the transaction is rolled back if a failure happens).. Is there another way of doing this in sqlalchemy w/o creating a session object for every single add() call? One elegant way would be to be able to query the seed db for the obj.. if it exists, pass.. my script does not know about the structure of the objects being inserted, therefore i cannot write a generic query based on its attributes.. it would be nice if one could do something like: try: session.query(obj) except NotFound: session.add(obj) does such a thing exist in sql alchemy? if not, what do you suggest I do? Please bear the following in mind: 1. I cannot remove everything in the tables at start time since there is more than one script and there is only one seed db. 2. The solution has to be generic and not require me to know what kind of attributes/primary_keys obj has 3. I would like to do this using one session instance if possible --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
