[sqlalchemy] Flushing all sessions
Hello. I'm using SQL Alchemy as a persistence engine for my multi-threaded desktop application with sqlite backend. ScopedSession acts very well, but there is a problem: 1) I have 2 tables: categories_table = Table('category', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(50)), ) keywords_table = Table('keyword', metadata, Column('id', Integer, primary_key=True), Column('category_id', Integer, ForeignKey('category.id')), Column('text', Unicode(200)), ) mapper(Category, categories_table,properties={ 'keywords': relation(Keyword, backref='category',order_by=keywords_table.c.text,cascade=all, delete- orphan) }) mapper(Keyword, keywords_table) 2) Thread A creates Keyword instance with something like this: new_keyword = Keyword(u'Test',category=category) Newly created `new_keyword` automagically become attached to `category` session manager. But not being automagically commited! `category` may be created in other thread, so calling session.flush () does nothing because new object doesn't belong to current session. 3) It will remain uncommited when i'm closing my application. And if finally get lost after application exit. So the question is: How to flush *ALL* sessions, regardless their threads? --~--~-~--~~~---~--~~ 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: Flushing all sessions
Dnia 2009-10-07, Śr o godzinie 04:30 -0700, Alexey pisze: Hello. I'm using SQL Alchemy as a persistence engine for my multi-threaded desktop application with sqlite backend. ScopedSession acts very well, but there is a problem: 1) I have 2 tables: categories_table = Table('category', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(50)), ) keywords_table = Table('keyword', metadata, Column('id', Integer, primary_key=True), Column('category_id', Integer, ForeignKey('category.id')), Column('text', Unicode(200)), ) mapper(Category, categories_table,properties={ 'keywords': relation(Keyword, backref='category',order_by=keywords_table.c.text,cascade=all, delete- orphan) }) mapper(Keyword, keywords_table) 2) Thread A creates Keyword instance with something like this: new_keyword = Keyword(u'Test',category=category) Newly created `new_keyword` automagically become attached to `category` session manager. But not being automagically commited! `category` may be created in other thread, so calling session.flush () does nothing because new object doesn't belong to current session. 3) It will remain uncommited when i'm closing my application. And if finally get lost after application exit. So the question is: How to flush *ALL* sessions, regardless their threads? Do you really want to flush session in other threads not knowing in which state they are? Maybe try/finally will be enough for you? Something like --program/thread begin-- try: createsession somestuff finally: session.flush() --program/thread end-- --- Tomasz Jezierski Tefnet www.tefnet.pl --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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: Portable Enum Columns
Yannick Gingras wrote: On October 6, 2009, Adrian von Bidder wrote: On Tuesday 06 October 2009 14.45:33 Yannick Gingras wrote: [...] Is there another way to do it? Something that would be portable and to both MySQL and Postgres would be great. Since both pg and mysql hava a native enum type, it's only a matter of writing the appropriate code in the SQL dialects. I still use sqlite for tests but it would not be to hard to do without. Still, having a solution that has a workaround for sqlite is always a plus. This came up just recently, search the list archive. I can't remember if the answer last time was it would be quite easy to do or somebody is working on it, though. I simply can't find it. Would you have a link or a few keywords that I could search for by any chance? the ticket is here: http://www.sqlalchemy.org/trac/ticket/1109 you'll see from the discussion the issue is not nearly so straightforward. -- Yannick Gingras http://ygingras.net --~--~-~--~~~---~--~~ 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: Flushing all sessions
Do you really want to flush session in other threads not knowing in which state they are? Uhm, that's a good point... I just want to flush created objects wich created in my function,but belongs to other session because references to objects in other session. For example: def my_function(foo_object): for _ in xrage(10): bar = Bar(foo=foo_object) seomething_to_write_all_bar_objects_to_db() foo_object = Foo() Thread(target=my_function).start() -- Alexey Y Uzhva Everything will be all right in the end. If it's not all right, it's not the end. 2009/10/7 Tomasz Jezierski - Tefnet develop...@tefnet.pl Dnia 2009-10-07, Śr o godzinie 04:30 -0700, Alexey pisze: Hello. I'm using SQL Alchemy as a persistence engine for my multi-threaded desktop application with sqlite backend. ScopedSession acts very well, but there is a problem: 1) I have 2 tables: categories_table = Table('category', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(50)), ) keywords_table = Table('keyword', metadata, Column('id', Integer, primary_key=True), Column('category_id', Integer, ForeignKey('category.id')), Column('text', Unicode(200)), ) mapper(Category, categories_table,properties={ 'keywords': relation(Keyword, backref='category',order_by=keywords_table.c.text,cascade=all, delete- orphan) }) mapper(Keyword, keywords_table) 2) Thread A creates Keyword instance with something like this: new_keyword = Keyword(u'Test',category=category) Newly created `new_keyword` automagically become attached to `category` session manager. But not being automagically commited! `category` may be created in other thread, so calling session.flush () does nothing because new object doesn't belong to current session. 3) It will remain uncommited when i'm closing my application. And if finally get lost after application exit. So the question is: How to flush *ALL* sessions, regardless their threads? Do you really want to flush session in other threads not knowing in which state they are? Maybe try/finally will be enough for you? Something like --program/thread begin-- try: createsession somestuff finally: session.flush() --program/thread end-- --- Tomasz Jezierski Tefnet www.tefnet.pl --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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] Very odd utf8 problem
Hey! I'm using SA in a TurboGears website for quite a long time now, and it was working great for a long time, until now! In my website users can upload articles, edit them, etc... The problem is with the search function - for some reason, searching for a certain word in my language (Hebrew) kills SA. The second time you're searching for it, it works. See what I mean here (it's me running the same query twice in IPython): http://paste2.org/p/457059 What can cause this behavior?! I can't think of anything! I guess that one of my users has uploaded some article with some invalid utf8 code, but should that kill the query? and how come it doesn't kill the second one? and what can I do to avoid it? I'm using SQ 0.4.8 which is quite old, but actually it was released just a year ago. I can't always catch up with everything... :p Thank you! Yo'av. --~--~-~--~~~---~--~~ 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: How to get SA column object from table.col, declarative attribute, or elixir entity attribute
Can anyone give me an idea of what I need to do here? If I can just get a high level overview of what I need to do, I am happy to read the documentation and source to fill in the details. Thanks. On Oct 1, 1:56 pm, Randy Syring ra...@rcs-comp.com wrote: Mike, Thank you for the prompt reply: On Oct 1, 1:11 pm, Michael Bayer mike...@zzzcomputing.com wrote: RandySyring wrote: I'm not sure of the context here. are you generating code or just executing SQL ? Ok, maybe a small example. Here is a declarative object and the how the datagrid gets defined: http://paste.pocoo.org/show/142448/ Note that the datagrid currently accepts the column from the table or the declarative attribute. It would also accept an elixir attribute. Then, inside the datagrid library, I have references to those objects that were passed in. Use them to construct SQL based on options selected by the user. So, a user might request something like: /foo?filteron=firstnamefilteronop=nefilterfor=test* and we would generate something like: SELECT persons.id AS persons_id, persons.firstname AS persons_firstname, persons.last_name AS persons_last_name FROM persons WHERE persons.firstname NOT LIKE 'test' run that against the DB and then return the results in an HTML table. The way we generate the SQL is by using the SA/Elixir column/ attributes in a query, here is a small snippet of that: http://paste.pocoo.org/show/142453/ So, my problem currently is that in the above snippet, ffor might be an empty string. That's fine if the column is a text type, but I have to handle it differently if the column is a time stamp. I am also assuming I will run into other problems related to the type of column being used that I will need to handle. But, I can't currently handle those situations b/c I don't know enough about the SA objects to figure what type they are. if the latter wouldn't you be using TypeEngine subclasses to handle coersion of type values ? And that's where you lose me, sorry. All I know is that I have an SA table column, SA declarative attribute, or elixir entity attribute and I need to figure out what type of SA column they represent (i.e. DateTime, Time, Date, etc.). I really have no idea what it will take to bridge the gap, hence this post. :) --~--~-~--~~~---~--~~ 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: Very odd utf8 problem
Yo'av Moshe wrote: Hey! I'm using SA in a TurboGears website for quite a long time now, and it was working great for a long time, until now! In my website users can upload articles, edit them, etc... The problem is with the search function - for some reason, searching for a certain word in my language (Hebrew) kills SA. The second time you're searching for it, it works. See what I mean here (it's me running the same query twice in IPython): http://paste2.org/p/457059 this code: Article.query.filter(Article.c.content.like('%#1502;#1496;#1512;#1493;#1514;%')).all() should read: Article.query.filter(Article.c.content.like(u'%#1502;#1496;#1512;#1493;#1514;%')).all() as far as why it works the second time, I'd guess it's some quirk of the REPL you're using. --~--~-~--~~~---~--~~ 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: Very odd utf8 problem
Um, why? It's true that it doesn't fail when I add that 'u', but it returns nothing (an empty list). What's that REPL? Yo'av 2009/10/7 Michael Bayer mike...@zzzcomputing.com Yo'av Moshe wrote: Hey! I'm using SA in a TurboGears website for quite a long time now, and it was working great for a long time, until now! In my website users can upload articles, edit them, etc... The problem is with the search function - for some reason, searching for a certain word in my language (Hebrew) kills SA. The second time you're searching for it, it works. See what I mean here (it's me running the same query twice in IPython): http://paste2.org/p/457059 this code: Article.query.filter(Article.c.content.like('%#1502;#1496;#1512;#1493;#1514;%')).all() should read: Article.query.filter(Article.c.content.like(u'%#1502;#1496;#1512;#1493;#1514;%')).all() as far as why it works the second time, I'd guess it's some quirk of the REPL you're using. -- Yo'av Moshe --~--~-~--~~~---~--~~ 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] Difference between result_processor and process_result_value?
I had some (working) code with a typeadaptor that used the process_result_value style. I changed this to use the result_processor style and returned a calleable and some tests started to fail. It seems that with the process_result_value form, value is a datetime object. When using result_processor, value is a unicode string. pjjH -def result_processor(self, dialect): -def process(value): -if value is None: -return None -return Date(value) -return process +def process_result_value(self, value, dialect): +if value is None: +return None +return Date(value) --~--~-~--~~~---~--~~ 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] Simple join
Hi, I have this simple model: class DHCPServer(DeclarativeBase): __tablename__ = 'dhcp_server' id = Column(Integer, primary_key = True) hostname = Column(Unicode(80), nullable = False) ip = Column(Unicode(16), nullable = False) label = Column(Unicode(80)) class WorkStation(DeclarativeBase): __tablename__ = 'work_stations' id = Column(Integer, primary_key = True) hostname = Column(Unicode(255), nullable = False) ip = Column(Unicode(16), nullable = False) mac = Column(Unicode(17), nullable = False) timestamp = Column(DateTime, nullable = False) server_id = Column(Integer, ForeignKey('dhcp_server.id')) server = relation(DHCPServer) Now I want to build a query to get all Workstations which are related to server 'foo'. This works: ws = DBSession.query(WorkStation).select_from(join(WorkStation, DHCPServer)).filter(DHCPServer.label == 'foo').all() but It's too complex. Is there an easier way? Something like: DBSession.query(WorkStation).filter(Workstation.server.label == 'foo').all() Thanks! -- Diego Woitasen XTECH --~--~-~--~~~---~--~~ 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] postgresql CREATE SCHEMA statement does not create schema
Hi, When running this function with postgresql 8.4 and sqla 0.5.5, def test(dbstring): from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() from sqlalchemy.sql import text gq = text( SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; ) conn.execute(gq) conn.close() the schema foo is not created. However, removing the SET search_path TO public; line makes it work. This is not the case when issuing these commands directly via psql. Any idea what might be going on here? Regards, Faheem. --~--~-~--~~~---~--~~ 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: Very odd utf8 problem
On Wed, Oct 07, 2009 at 07:55 -0700, Yo'av Moshe wrote: See what I mean here (it's me running the same query twice in IPython): http://paste2.org/p/457059 What can cause this behavior?! I can't think of anything! I guess that one of my users has uploaded some article with some invalid utf8 code, but should that kill the query? and how come it doesn't kill the second one? and what can I do to avoid it? In addition to the bug Mike pointed out to you I want to introduce you to my favourite bug this year: https://bugs.launchpad.net/ipython/+bug/339642 If you run into unicode issues with IPython it is wise to check the 'python' behaviour before development code against this bug. kind regards Wolodja Wentland signature.asc Description: Digital signature
[sqlalchemy] Re: Portable Enum Columns
On October 7, 2009, Michael Bayer wrote: the ticket is here: http://www.sqlalchemy.org/trac/ticket/1109 you'll see from the discussion the issue is not nearly so straightforward. Right, looks like I should either go with PGEnum or stick to varchar FKs if I need something that is portable. Thanks for the info. -- Yannick Gingras http://ygingras.net signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Very odd utf8 problem
Thanks, I didn't know about that awful IPython bug... I checked, and apparently my website is already doing the SA query with a unicode object and not with a string one, so I think that it's not the u'' thing (it's true that I forgot it in my console testing, though). What you showed about IPython explains why it didn't give me any result when running in IPython with the unicode object - since it wasn't really a unicode object. So again - I *am* querying SA with a unicode object, and still, it fails the first time and works the second time. Yo'av. 2009/10/7 Wolodja Wentland wentl...@cl.uni-heidelberg.de On Wed, Oct 07, 2009 at 07:55 -0700, Yo'av Moshe wrote: See what I mean here (it's me running the same query twice in IPython): http://paste2.org/p/457059 What can cause this behavior?! I can't think of anything! I guess that one of my users has uploaded some article with some invalid utf8 code, but should that kill the query? and how come it doesn't kill the second one? and what can I do to avoid it? In addition to the bug Mike pointed out to you I want to introduce you to my favourite bug this year: https://bugs.launchpad.net/ipython/+bug/339642 If you run into unicode issues with IPython it is wise to check the 'python' behaviour before development code against this bug. kind regards Wolodja Wentland -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iQIcBAEBCAAGBQJKzMesAAoJEIt/fTDK8U78OTsP/jLC/OHMy7SqyM4T1OswUsfL 7V4JXjvxk7xSRUaUwWSqbi4FHYPUDVQ3iFD4czVxmqBXeClW8gxJBXCLpYjisXNR yXiDurakbeHG5FxrJEstYK9S2ZCM5uAx/aFy8PdT6rf7UO6XAi6nJ7xxQaMx4JMX XoA4oU1HsyOh8a0eg8NkmpMVJxeeZxr4DjlfLmXosMEpysG3d+mdq9SkKfKXGEsS t8PQqJDw8uLS+XdMmVLuwK6RtHV+ojNkH/FBQ6qfMGJEFWleeh2cKxiBoNTqOKlg sf9PznO/63HrswpeUJb8gfPs3tq7Mxa9DJzhgBc0U3toRg2VPjQTASXDc4PYqsJd K+WT/vbhpy34VDTABEPdD1DAxgit5H7AI+4DP6l5610qgWn1eNG6/jUi3mRIbojI S24/3udaFhOY/0NNDcI5mMijr77sjMbTSizO8ITabef/o9IiYkob32+0pW3j3+aO 0kK4SwWtoJ4qWwFsOD4ANcg5QjC9KcL2NlYe2gtWQhk3f9Fz9FbdfNzAptNvs94v qic2JONG9aa/CWnqO6RjF0JUCXIcUyr3jr5eKsBh9mli6wd3RYJbRZXHAXBD7ypA 3MPd2gX72zl6lCM+gJWgedK7c1YB6YbDcie+hGrj4m/0oHZeZdThbZJLymxvFRul 0gr9vxE99ggO3sTq9XLr =2y73 -END PGP SIGNATURE- -- Yo'av Moshe --~--~-~--~~~---~--~~ 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: Simple join
Now I want to build a query to get all Workstations which are related to server 'foo'. This works: ws = DBSession.query(WorkStation).select_from(join(WorkStation, DHCPServer)).filter(DHCPServer.label == 'foo').all() but It's too complex. Is there an easier way? Something like: DBSession.query(WorkStation).filter(Workstation.server.label == 'foo').all() Thanks! ws = DBSession.query(WorkStation,DHCPServer).join(DHCPServer).filter(DHCPServer.label == 'foo') Should give the same result. You shouldn't need a select_from() for a simple query like this when all the join conditions are obvious. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---