[sqlalchemy] Re: SessionExtension and Transactions: how to coordinate all SessionExtension funcs
Stefano Bartaletti wrote: I need to gather IDs in after_commit because theID is a serial Postgres value that is available only after flush() Not really... in postgres, you can ask to consume the next sequence value with SELECT NEXTVAL('sequence_name') and explicitly set that as primary key value. -- This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: column_property() caching
Vladimir Iliev wrote: hi, is it possible to add a non-caching column_property() to my mapping? You can use expire() on a single column, so you could proxy that column with a @property that also expires it. -- This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: features: database drivers and ssl
Rick Morrison wrote: Hi Marco, [...] Thank you. Very helpful. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] features: database drivers and ssl
I need to evaluate a third-party application, that will be integrated with others by one of my programs. My integration app runs on linux with SA 0.4, and I will strive to use the latest stable release. I need to write a couple of requirements to choose the third-party application. Will I be able to support DB2, if that is what the vendor supports? I see a driver is in the works, and I don't need fancy features. Or should I stick with Postgres, MSSQL, Oracle? Which driver is more mature, Oracle or MSSQL (with pymssql*)? Is the SSL layer supported by all the drivers? *) I reckon pyodbc is recommended by SA over pymssql, but the former only runs on windows. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mapper
lur ibargutxi wrote: 'idindicatorgroupcontainer' : relation(IndicatorGroups, primaryjoin=sql.and_(IndicatorGroups.idindicatorgroup==GroupGroups.idindicatorgroupcontainer)),'idindicatorgroupcontained' : relation(IndicatorGroups, primaryjoin=sql.and_(IndicatorGroups.idindicatorgroup==GroupGroups.idindicatorgroupcontained)), },allow_column_override=True) when i do: g=IndicatorGroups() I have this error: Module sqlalchemy.sql.visitors, line 56, in traverse AttributeError: 'bool' object has no attribute 'get_children' I don't know how to fix this. Does anyone knows about this problem?? You are using and_() over a single parameter. Moreover, you're probably using SA 0.4 and you need to use IndicatorGroups.c.indicatorgroup Note the '.c.' -- that goes away with the 0.4 API overhaul. So either upgrade SA, or use the .c attribute You don't usually need to keep a reference to the mappers, you can always get them from the mapped classes. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mapper
lur ibargutxi wrote: I forgot. Try using tables instead of classes that are not mapped yet.. mappers['groupgroups'] = mapper(GroupGroups, tables['groupgroups'], properties = { 'idindicatorgroupcontainer' : relation(IndicatorGroups, primaryjoin=sql.and_(tables['indicatorgroups'].c.idindicatorgroup==tables['groupgroups'].c.idindicatorgroupcontainer)),'idindicatorgroupcontained' : relation(IndicatorGroups, primaryjoin=sql.and_(tables['indicatorgroups'].c.idindicatorgroup==tables['groupgroups'].c.idindicatorgroupcontained)), },allow_column_override=True) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: turbogears, sqlalchemy and utf8
Lukasz Szybalski wrote: dev.cfg has sqlalchemy.dburi=mysql://user:[EMAIL PROTECTED]:3306/dbname sqlalchemy.convert_unicode = True --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: TurboGears: best practices for SELECTing
James Brady ha scritto: Hi Marco, assign_mapper did help - the relations are now accessible through my models, and the query syntax is nicer than pure SA as well in my opinion. However, I'm using the identity framework (part of TurboGears) Me too. which unfortunately doesn't play nicely with assign_mapper... shame! What do you mean? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: TurboGears: best practices for SELECTing
James Brady ha scritto: The problem I'm hitting at the moment is how to properly select simple objects... There seems to be two main approaches, for example: session.query(Ownership).select() or ownership_table.select().execute() With the first approach, I get InvalidRequestError: Parent instance class 'tnf.model.Ownership' is not bound to a Session, and no contextual session is established; ... which I'm not sure how tackle - there's no mention I can find of when and where create_session should be called... Did you call assign_mapper on Ownership? Can anyone answer these specific questions or point me in the direction of some further documentation? You should be able to call Ownership.query() just fine, if you have a working mapper. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: insert() on compound key-how to auto number second key
Lukasz Szybalski ha scritto: I am trying to insert a record with a compound primary key that consists of Team_Sid, User_Sid I can either do i = users_table.insert() i.execute(TEAM_SID=343, USER_SID=1,TEAM_NAME='the team') i.execute(TEAM_SID=343, USER_SID=2,TEAM_NAME='the team') i.execute(TEAM_SID=343, USER_SID=3,TEAM_NAME='the team') But that means I need to find out what is the next USER_SID available. Is there an insert save or other function that will save the record and auto number the second primary key TEAM_SID=343 + autonumber Just to be picky, there is no such thing as a _second_ primary key, it's a part of the compund PK. User_sid ?? i.execute(TEAM_SID=343,TEAM_NAME='the team') Your post begs for a question, which is why? :-) You are fighthing the system. Having an autonumber column as part of a compound key does not make a lot of sense. I don't think you have a really, really, really strange use case that justifies 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: creating a database through SQLAlchemy
Travis Kriplean ha scritto: However, this seems a bit ugly. Is there a way to obtain a non- transactional connection from an engine in 0.3.10? If not, is it possible in 0.4? I use this with SA 0.3.10 and Postgres: engine.raw_connection().set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) engine.text(CREATE DATABASE %s ENCODING = 'utf8' % dbname).execute() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part2 connect and select
Lukasz Szybalski ha scritto: Got another error here. but I guess its fixed in a newer version of sqlalchemy via ticket 482 Yes. I wasn't aware of 482 because I usually try to avoid table names that _must_ be escaped (mixed caps, reserved words, etc). put assign_mapper() in place of mapper() Are these two the same? assign_mapper in TG, is the same as mapper in SA? No, SA provides both mapper and assign_mapper. Assign mapper does the work of the former, plus attached several methods to your mapped classes and binds a default session context. SA 0.4 deprecates this and uses a scoped_session, but you should not worry about it now. How can I iterate through myuser fields? It's columns, btw. If you call them fields, baby Jesus cries for column in myuser.c.keys(): print getattr(myuser,column) Thanks for the help. I have moved ahead in these few emails more then I was able in a week. I know how it feels ;-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: strange serial problem in postgres
Glauco ha scritto: Thank you kevin , i've worked a lot over PG and this tecnique is consolidated in our model.. but here the problem is that the column is a simplycolumn_name INTEGER NOT NULL references other_table(id) and i cannot understand why sqlalchemy whant to use it as a column_name SERIAL Make that foreign key primary as well: INTEGER REFERENCES ... PRIMARY KEY Also, like Mike said, the autoload mechanism (or something below) infers that it's a serial column, you should turn that off with autoincrement=False in the Table constructor, where you autoload the schema. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part2 connect and select
Lukasz Szybalski ha scritto: bind_meta_data() users_table = Table('users', metadata, autoload=True) class Users(object): pass usersmapper=mapper(Users,users_table) assign_mapper() in place of mapper() mysession=session.query(Users) 1. What would be the code from now on to query all Users? Does 'mysession' have a connection to a database already? Yes, if you used assign_mapper instead, your model's classes will be implicitly bound to the session context. So... 2. How do I select a user where User_Sid=100? What is the schema of the table? If User_Sid is the primary key: User.get(100) If it's not: User.select_by(User_Sid=100) Why doesn't this work? jj=[] for users in session.query(Users): jj.append(users.Users_Sid) ehm, weird use of plural for a loop variable. You are using an explicit session here, you don't need to do that in TurboGears since the classes have an implicit session bound to the request, that it cleaned after each served page. Anyway, it should work more or less (I note Users_Sid in place of User_sid) What is the simples way to query my database to get user with user_sid=100? What is the simples way to query my database to get user last_name='Smith' with user_sid=100? Again, are filtering by both last_name and user_sid? So I suppose user_sid is not the primary key after all. User.select_by(User_sid=100, last_name='Smith') --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Hierachical data
Alexandre CONRAD ha scritto: Maybe this should need some attention to implement in SA some API to handle nodes (insert, move, remove) of herachical trees in SA the Nested Set way. There are several ways to implement schema and rules (and therefore APIs) just by looking at Celko's book, and there are different approaches by other people too. Since SQLAlchemy does not want to enforce any specific way of working in other areas, I don't see we it should provide an API that would be best handled by an upper-layer extension. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4 MERGED TO TRUNK
Michael Bayer ha scritto: can i have an example http://trac.turbogears.org/browser/branches/1.0/turbogears/database.py?rev=2320 281 [run_with_transaction.when(_use_sa())] 282 def sa_rwt(func, *args, **kw): 283 log.debug(New SA transaction) 284 transaction = session.create_transaction() 285 try: [...] --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: a renaming proposal
svilen ha scritto: Anyway it may depend which audience are u targeting with these names - those who never seen an API or those for which names are important only to associate them with a library/version/use-case... both extremes are equaly uninteresting imo. I think targeting those who read Fowler's book (or really should) is good enough :-) MetaData is not ambiguous. One of the things I liked at first about SA is that, having just bought the book, it had several of the book's patterns with the same names. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload'ing metadata
[EMAIL PROTECTED] ha scritto: here some theory on comparing data trees, in order to produce the changeset edit scripts. http://www.pri.univie.ac.at/Publications/2005/Eder_DAWAK2005_A_Tree_Comparison_Approach_to_Detect.pdf The complete title of the paper is A Tree Comparison Approach To Detect Changes in Data Warehouse Structures. data warehouse is the key concept. of course full automation is not possible and not needed - but why not do maximum effect/help with minimum resources? I've not read it, but what is working for data warehouse could fail miserably in a normalized database. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Consistency with DB while modifying metadata
Anton V. Belyaev ha scritto: again, im not opposed to this feature and ill patch in an adequate (and fully unit-tested) implementation. but have you actually ever *had* this problem? or is it just hypothetical ? For example, a developer modifies the metadata and checks in. Another developer updates and finds strange problems, having the old database. Then, a SQL script to migrate the database schema should be provided with the check-in... Please, don't believe SQLAlchemy will help you win the Viet Nam war of computer science (*). It's supposed to help us survive. And it's a lot. I'm happily using autoload since the beginning, I've never used the Table() construct if not to replicate a couple of bugs and submit them. And I think my applications are simpler because of it, not in spite of it :-) http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx http://spyced.blogspot.com/2006/02/why-schema-definition-belongs-in.html (with comments from Mike Bayer, Ian Bicking and others) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.get with unordered multiple-column-primary-key
Roger Demetrescu ha scritto: query.get(dict(columnB='foo', columnA='bar') Lazy programmers are the best ones... :) That's the reason lazy programmers share a superclass for all their domain objects... hint, hint :-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Generative style on SQL-API layer
svilen ha scritto: on a side note, here or for the query(), once i add .order_by() and similar, will be a possibility to remove/cancel them? e.g. .order_by(None) - similar to .join(None)? or should i keep a copy at the point before adding .order_by()? e.g. i want: q1 = query.filter( ...).order_by(z)#the final most used query ... q2 = q1.order_by(None) #used sometimes e.g. for count vs i could do: q0 = query.filter( ...) #saved q1 = q0.clone().order_by(z) #the final most used query ... q2 = q0 #used sometimes e.g. for count and why not q = query.filter(...) q1 = q.order_by(z) ... q2 = q ?? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Generative style on SQL-API layer
svilen ha scritto: because q1 with the order is _the_ query, made at point A somewhen, and stored there as a construct; much later at some point B i need to use that query but without the ordering - now i have to keep 2 copies of the query, w/ and w/out order. And this strip-the-ordering could be generic, applicable to any query... Basically you're asking for that to gain some performance on q2.execute(), and at the same time you want to avoid littering your function's namespace. Ok, I understand. I find the API is cleaner without that feature, but I am nobody here :-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: In Turbogears, using the same transaction for mapped objects and direct operations
Sanjay ha scritto: BTW, the session transaction is stored in cherrypy.request.sa_transaction. Yes, but it's been added recently. Does this help simplify the statements? Transaction instances have a connection() property. So, cherrypy.request.sa_transaction.connection should work. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: In Turbogears, using the same transaction for mapped objects and direct operations
Sanjay ha scritto: Need help on how to do it. Being a turbogears application where db entities like metadata and session are imported rather than created by me, I am confused and could not successfully implement the pattern provided in the docs. I'm sure there are cleaner ways, but this worked for me conn = session.context.get_current().connection(SomeMappedClass) conn.execute(...) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: winpdb and sqlalchemy
Edin Salkovic ha scritto: Currently, SQLAlchemy's setup(...) hasn't set this arg, meaning that setuptools uses its own algorithm to determine if SQLAlchemy can be installed as a zipped egg. You can switch off zip for all newly installed eggs, in ~/.pydistutils.cfg [easy_install] zip_ok = false --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: query().filter_by(boolean)
Michael Bayer wrote: please file a ticket for this. done, #535 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] query().filter_by(boolean)
I'm not trying the trunk, and it's the first time I use filter_by, but I guess: MappedClass.query().filter_by( MappedClass.column_name == 'Foo' ) equates to filter_by(False), because the .c is missing and it's comparing an UOWProperty to a string, instead of a Column object to a string Actually, in my case it gives me MappedClass.column_name 'foo' == True and MappedClass.column_name 'foo' == False In SA 0.3.6, the query runs and returns all rows, possibly spoiling something, somewhere If we do the same with MappedClass.select(MappedClass.column_name=='Foo') it raises AttributeError: 'bool' object has no attribute 'get_children' which, if not really explicit, let us know where to look Since instrumented attributes cannot be used in filter_by and friends (http://www.mail-archive.com/[EMAIL PROTECTED]/msg03470.html) can an exception be raised? tnx --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] parameter binding in connection.execute()
I know I can have bound parameters in engine.text(): t = engine.text(select foo from mytable where lala=:hoho) r = t.execute(hoho=7) ...but I need the same for connection.execute(), since I am using temporary tables and they are not accessible via engine.text Should I resort to manual quoting? tnx --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Dealing with uncommited data
Andreas Jung wrote: In a traditional application you can insert a new row and read the row within the same transaction. What is the typical usage pattern to deal with this in SA? In our particular setup (Zope) a new session is created for each new HTTP request and flushed automatically at the end of request when Zope commits the transaction. A solution might be to flush the session explicitly in such a case however I don't think that this is a smart idea. Any better idea? You can call session.flush() in between. This writes to the transaction but does not commit anything yet If you need to directly query the tables afterwards (triggers, text query, whatever), but inside the same transaction: session.flush() conn = session.context.get_current().connection(SomeMappedClass) conn.execute(sa.select(...)...) (at least, works for me with turbogears that wraps requests with transactions the same way as zope) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Help get Wikipedia entry through review
Paul Johnston wrote: Hi, Well, more than improving the quality of the article, we need to make a case for it being notable. The main criteria for this is: Software is notable if it has been the subject of multiple non-trivial published works whose source is independent of the software's author(s). So, can we pick out two such examples? Off the top of my head, perhaps the Rapid Web Application with TurboGears is one (need to check). Can someone think of a second? On O'Reilly Safari I can only find a pair of pages in Core Python Programming (2nd ed.) and the TG book. Books take time to publish... I'm sure this year we will see more Academic papers anyone? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Object-oriented engine?
svilen wrote: how much OO u want? He's not using a DBMS. He has more OO-ness than SA could give him :-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] just how lazy are backrefs?
I'm working inside TurboGears, autoload everything. Tested with 0.3.3 and Trunk. I find this strange behaviour: when I declare a relation between a Person and a Contract: assign_mapper(context, Contract, tbl['contracts'], properties = { 'responsible' : relation(Person, backref='contracts_responsible', # ambiguous... do an explicit join primaryjoin=(tbl['contracts'].c.uid_responsible==Person.c.uid)), [...] ) Now, if I start the console, then run... john = Person.get('johndoe') john.contracts_responsible gives me AttributeError: 'Person' object has no attribute 'contracts_responsabile' _but_ the very moment I load any contract: cont = Contract.get('somecode') _then_ john.contracts_responsible starts working and returns the list of contracts that john is responsible for. The same happens with any backref... i'm puzzled O_o If this is not a known feature/bug, I can try and reproduce it in a standalone project 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: just how lazy are backrefs?
Marco Mariani wrote: john.contracts_responsible gives me AttributeError: 'Person' object has no attribute 'contracts_responsabile' this is a cut-n-translate-n-paste typo, should read contracts_responsible of course --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: count(*) function
milena wrote: I have tried select([func.count(*)], from_obj=[table_name]).execute() but it didn't work I suppore you're not using mappers, so this is the fastest method: number_of_rows = table.count().execute().fetchone()[0] where table is the table object --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column aliases
Michael Bayer wrote: to have aliases of properties that are usable with get_by(), use the synonym function, described in: http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_overriding Thank you. Altough the docs are very well done, there are many APIs and that sometimes may be confusing. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Column aliases
Hi there This relates to Turbogears, but is really a SA question. I've customized TG authentication authorization to use my autloaded tables in Postgres and SqlAlchemy 0.3.3. In my schema, I have User.c.uid, the login name of the users, as a primary key TG uses a User mapper with two distinct columns: User.c.user_id (the primary key) and User.c.user_name (the logname). Since I am an avid fan of meaningful primary keys (and have a legacy db to support) I want to keep things my way, but TG does some user handling that I have to fix. So, to avoid patches to the TG source or useless sub-classing, I'd like to access the same column by any of the three names. I cannot do that with a python property on the mapper because TG should be able to use get_by and friends. I've come up with: assign_mapper(context, User, tbl['users'], properties = { 'user_id': tbl['users'].c.uid, # alias for SqlAlchemyIdentity 'user_name': tbl['users'].c.uid, # alias for SqlAlchemyIdentity 'uid': tbl['users'].c.uid, }) This seems to work (I added the third property to make 'uid' reappear!) , but makes it impossible, for instance, to create new users: In [1]: user = User(uid='xxx') In [2]: session.flush() [...] SQLError: (IntegrityError) null value in column uid violates not-null constraint 'INSERT INTO users (uid, nome, cognome, codice_fiscale) VALUES (%(uid)s, %(name)s, %(surname)s)' {'surname': None, 'uid': None, 'name': None} In [3]: user = User(user_id='xxx') In [4]: session.flush() [...] SQLError: (IntegrityError) null value in column uid violates not-null constraint 'INSERT INTO users (uid, nome, cognome, codice_fiscale) VALUES (%(uid)s, %(name)s, %(surname)s)' {'surname': None, 'uid': None, 'name': None} I reckon I should probably go ahead and patch TG, but maybe there is a clean way to do what I have in mind? Thank you. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: unicode
Lee McFadden wrote: Going from the line number in your exception that would be because your columns are of type String. Change the columns to Unicode and it should solve your problem. foo_table = Table('foo', metadata, Column('id', Integer, primary_key=True), Column('bar', Unicode(255)) ) In case you're using reflection: Overriding Reflected Columns Individual columns can be overridden with explicit values when reflecting tables; this is handy for specifying custom datatypes, constraints such as primary keys that may not be configured within the database, etc. mytable = Table('mytable', meta, ... Column('id', Integer, primary_key=True), # override reflected 'id' to have primary key ... Column('mydata', Unicode(50)),# override reflected 'mydata' to be Unicode ... autoload=True) http://www.sqlalchemy.org/docs/metadata.myt#metadata or use convert_unicode=True in create_engine, as I do, it should help. In my case, I have UTF8 as default encoding for postgres, and SA reflects columns as PGString. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] autoload, composite foreign keys
Does sqlalchemy have some limit in handling composite primary keys with autoload? I've tried postgres 8.1 and SA 0.3 or trunk. I have a 'tree' of four tables, and SA does not create the full join to follow composite foreign keys. The schema and test model is in the attachment. As I've seen, the last query executed by in model.py is: SELECT factories.cod_fctry AS factories_cod_fctry, factories.cod_company AS factories_cod_company, factories.cod_bl AS factories_cod_bl, factories.cod_practice AS factories_cod_practice FROM factories WHERE factories.cod_company = %(lazy_7eca)s AND factories.cod_fctry = %(lazy_eaaf)s ORDER BY factories.cod_company This means only two of the four columns composing the foreign key are used. When I explicitly set the join, with the following patch to model.py mapper(User, tbl['users'], properties = { -'factory' : relation(Factory, backref='users') +'factory' : relation(Factory, backref='users', + primaryjoin = and_( +tbl['factories'].c.cod_company == tbl['users'].c.cod_company, +tbl['factories'].c.cod_practice == tbl['users'].c.cod_practice, +tbl['factories'].c.cod_bl == tbl['users'].c.cod_bl, +tbl['factories'].c.cod_fctry == tbl['users'].c.cod_fctry + ) +) I have the correct query, i.e. SELECT factories.cod_fctry AS factories_cod_fctry, factories.cod_company AS factories_cod_company, factories.cod_bl AS factories_cod_bl, factories.cod_practice AS factories_cod_practice FROM factories WHERE factories.cod_company = %(factories_cod_company)s AND factories.cod_practice = %(factories_cod_practice)s AND factories.cod_bl = %(factories_cod_bl)s AND factories.cod_fctry = %(factories_cod_fctry)s ORDER BY factories.cod_company From the FAQ: ... Lots of scenarios simply are not possible in certain scenarios, such as case-sensitive schemas, foreign key reflection, etc. ... this makes me think there has to be some (possibly old) issue. Any idea? 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- CREATE TABLE companies ( cod_company VARCHAR(8) PRIMARY KEY ); CREATE TABLE practices ( cod_company VARCHAR(8) NOT NULL REFERENCES companies, cod_practice VARCHAR(8) NOT NULL, PRIMARY KEY (cod_company, cod_practice) ); CREATE TABLE businesslines ( cod_company VARCHAR(8) NOT NULL, cod_practice VARCHAR(8) NOT NULL, cod_bl VARCHAR(8) NOT NULL, FOREIGN KEY (cod_company, cod_practice) REFERENCES practices (cod_company, cod_practice) MATCH FULL, PRIMARY KEY (cod_company, cod_practice, cod_bl) ); CREATE TABLE factories ( cod_company VARCHAR(8) NOT NULL, cod_practice VARCHAR(8) NOT NULL, cod_bl VARCHAR(8) NOT NULL, cod_fctry VARCHAR(8) NOT NULL, FOREIGN KEY (cod_company, cod_practice, cod_bl) REFERENCES businesslines (cod_company, cod_practice, cod_bl) MATCH FULL, PRIMARY KEY (cod_company, cod_practice, cod_bl, cod_fctry) ); CREATE TABLE users ( uid VARCHAR(32) PRIMARY KEY, cod_company VARCHAR(8) NOT NULL, cod_practice VARCHAR(8) NOT NULL, cod_bl VARCHAR(8) NOT NULL, cod_fctry VARCHAR(8) NOT NULL, FOREIGN KEY (cod_company, cod_practice, cod_bl, cod_fctry) REFERENCES factories (cod_company, cod_practice, cod_bl, cod_fctry) MATCH FULL ); #!/usr/bin/env python from sqlalchemy import Table, relation, create_engine, create_session, BoundMetaData, mapper, and_ pgeng = create_engine('postgres://user:[EMAIL PROTECTED]:5432/dbname') pgmetadata = BoundMetaData(pgeng) pgeng.echo = False session = create_session() tbl = {} for name, in pgeng.execute(SELECT tablename FROM pg_tables WHERE schemaname='public').fetchall(): tbl[name] = Table(name, pgmetadata, autoload=True) class Company(object): pass mapper(Company, tbl['companies']) class Practice(object): pass mapper(Practice, tbl['practices'], properties = { 'company' : relation(Company, backref='practices') }) class BusinessLine(object): pass mapper(BusinessLine, tbl['businesslines'], properties = { 'practice' : relation(Practice, backref='businesslines') }) class Factory(object): pass mapper(Factory, tbl['factories'], properties = { 'businessline' : relation(BusinessLine, backref='factories') }) class User(object): pass mapper(User, tbl['users'], properties = { 'factory' : relation(Factory, backref='users') }) com = Company() com.cod_company = 'COM' pra = Practice() pra.cod_company='COM' pra.cod_practice='PRA' bl = BusinessLine() bl.cod_company='COM' bl.cod_practice='PRA'