[sqlalchemy] Re: Textual SQL
Thank you so much guys!
Now I figured it out. Hope this will help someone else as well :)
On Feb 2, 9:53 pm, MikeCo mconl...@gmail.com wrote:
Commit behavior depends on how you configure the session's autocommit
property. Follow the log messages in this little test.
from sqlalchemy import MetaData, Table, Column, String
from sqlalchemy.orm import sessionmaker
meta = MetaData('sqlite:///')
Session = sessionmaker(bind=meta.bind)
t = Table('something',meta,
Column('stuff',String)
)
print '+ create_all()'
meta.create_all()
meta.bind.echo=True
session1 = Session(autocommit=False) # this is default behavior
print '+ execute() will not include commit'
session1.execute(insert into something(stuff) values('some stuff'))
print '+ commit() need to do it yourself'
session1.commit()
session2 = Session(autocommit=True) # commit is configurable
print '+ execute() will include commit'
session2.execute(insert into something(stuff) values('some more
stuff'))
On Feb 2, 1:15 pm, Bob Farrell robertanthonyfarr...@googlemail.com
wrote:
On Mon, Feb 02, 2009 at 09:56:15AM -0800, Pavel Skvazh wrote:
Session.execute('INSERT INTO SOMETHING SOMETHING / DELETE/ UPDATE')
Do I have to call Session.commit() after this or it's already taken
care of? In other words does the literal sql statements follow the
session transaction rules or they act on there own?
sess.execute() will execute whatever you pass it immediately.
And since this works and worked for me for a long time now, what's the
benefit of from sqlalchemy.sql import text that I noticed in the docs
lately?
Using text() creates a ClauseElement that you can whack together with other
constructs. See the docs here for more
info:http://www.sqlalchemy.org/docs/05/sqlexpression.html#using-text
Thanks!
--
--
Bob Farrell
pH, an Experian Companywww.phgroup.com
Office Line: 020 7598 0310
Fax: 020 7598 0311
--
--~--~-~--~~~---~--~~
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: Using count with distinct?
Ah, ok the join syntax makes sense now.. I've really just been trying things in the interactive python shell, and trying 'dir' to see which methods are available on what. My mapped fields are from sql soup, with a db from django. (although currently my queries are just in sql) Hm if the join method is not meant to be there it might explain my confusion... So, should i always be using a select when making a query? - is there a page that shows an example of using join with a select? Next time i'm in front of a computer i'll have look. Michael Bayer wrote: I'm not quite sure how you've arranged for query.join() to be on your mapped class, but the right side, onclause form of query.join() uses tuples. q.join((rgt, onclause), (rgt, onclause), ...) On Feb 2, 2009, at 8:40 PM, Stuart Axon wrote: Cheers... Hm, still @ quite the n00b stage... got this far Build.join(ExternalBuild).distinct().count() But when I try Build.join(ExternalBuild, ExternalBuild.build_id == Build.id) It says: Traceback (most recent call last): File console, line 1, in module File string, line 1, in lambda File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\util.py, line 212, in go return fn(*args, **kw) File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\orm\query.py, line 873, in join return self.__join(props, outerjoin=False, create_aliases=aliased, from_joinpoint=from_joinpoint) File string, line 1, in lambda File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\orm\query.py, line 52, in generate fn(self, *args[1:], **kw) File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\orm\query.py, line 1071, in __join clause = orm_join(clause, right_entity, onclause, isouter=outerjoin, join_to_left=join_to_left) File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\orm\util.py, line 432, in join return _ORMJoin(left, right, onclause, isouter, join_to_left) File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\orm\util.py, line 409, in __init__ expression.Join.__init__(self, left, right, onclause, isouter) File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\sql\expression.py, line 2479, in __init __ self.right = _selectable(right).self_group() File c:\usr\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg \sqlalchemy\sql\expression.py, line 968, in _select able raise exc.ArgumentError(Object %r is not a Selectable and does not implement `__selectable__()` % element) ArgumentError: Object sqlalchemy.sql.expression._BinaryExpression object at 0x01CB8B30 is not a Selectable and does no t implement `__selectable__()` - Original Message From: Michael Bayer mike...@zzzcomputing.com To: sqlalchemy@googlegroups.com Sent: Tuesday, February 3, 2009 12:58:03 AM Subject: [sqlalchemy] Re: Using count with distinct? use this: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.distinct On Feb 2, 2009, at 7:53 PM, Stuart Axon wrote: I've got a fairly simple query in postgres... any idea how I do this in sqlalchemy - I couldn't find any info about this select count(distinct device_id) from externalbuild join build on build.id = externalbuild.build_id where external_id = '1' and not is_known BTW, the recent work looks good - might even help me convert my other queries from sql... I thought this one was the simplest, but failed here too :-\ --~--~-~--~~~---~--~~ 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: reflection for indices, function-based indices
Index support for Postgres was recently added to the trunk and I merged
it into the reflection branch. You are welcome to check out the
reflection branch and give it a go. You could do a quick check like so:
import sqlalchemy as sa
from sqlalchemy.engine.reflection import Inspector
e = sa.create_engine('postgres:///yourdb')
inspector = Inspector(e)
print inspector.get_indexes('yourtable', schema='yourschema')
In fact, I'd love to get some feedback, especially for the Inspector
interface. Everything should work for Postgres.
Currently the Inspector supports these methods and attributes:
default_schema_name
get_schema_names()
get_table_names()
get_view_names()
get_columns()
get_primary_keys()
get_foreign_keys()
get_indexes()
Now is a good time for comments and requests for this interface. Keep
in mind that it's meant to be useful for non-ORM apps.
This is the commit log for the index support:
r5520 | zzzeek | 2008-12-22 22:47:52 -0600 (Mon, 22 Dec 2008) | 5 lines
- Added Index reflection support to Postgres, using a
great patch we long neglected, submitted by
Ken Kuhlman. [ticket:714]
Looks great. I've been sidetracked in the last few weeks, but will have a look
today see if things work for me.
Diez
--~--~-~--~~~---~--~~
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: cascade=all, delete, delete-orphan causes insert to fail
D'OH! (slaps head) Thanks for pointing this out, it makes perfect sense. I got it working by getting it from the DB, and merging it into the current session, then deleting: def DELETE(self,dmemberid): x = MemberInfo() memberProfile = x.GET(memberid=dmemberid,raw=True) merged_obj = self.session.merge(memberProfile[0]) self.session.delete(merged_obj) self.session.commit() Without merging into the same session, I got this error: sqlalchemy.exc.InvalidRequestError: Object 'MemberProfile at 0x-48935294' is already attached to session '-1217720980' (this is '-1217780404') I'm not sure why a merge into the current session was necessary, since I did the get() in the same session. Does each query flush the session, and create a new session key behind the scenes? So happy this is working, thank you! Gloria --~--~-~--~~~---~--~~ 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] Need SqlAlchemy Model Advice for a table with many foreign keys.
Hi all, thanks for this really helpful list. My question now involves trying to figure out the correct way to map this legacy database to SqlAlchemy objects using the declarative_base model. This database has two main tables, members, and member_profiles, where a memberID is the foreign key from members to member_profiles. It then has about thirty other tables such as this one, called member_gender. member_gender contains a genderID, and a string representing 'Male' or 'Female'. genderID is a foreign key in member_profiles, pointing to member_gender. So the member_profiles table contains many of these xxxID fields which are foreign keys into many other tables which act as descriptor tables, abstracting (to the point of overkill) just about every bit of data in this database. The Left Join on member_profiles, to get all of the data I need in one fell swoop, is over 50 lines long. I want to use the declarative_base class model to represent this database in SqlAlchemy, but here are problems I face: 1: Multiple inheritance is not allowed, so I cannot create a class for every table and inherit them all into one master class. 2: If I create three classes, for example, say Members, MemberProfiles, and Gender, and use Members as the base class for MemberProfiles, and MemberProfiles as the base class for Gender, the Members 'class members' are not visible via Gender. The inheritance seems to only go two objects deep. I want to be able to access the gender 'descriptor' field containing the string 'Female' from one master object, instead of accessing 'genderID'. I want to be able to do this for all foreign keys in this member_profiles table. Is there a way to properly make such a model using the declarative_base methodology, which allows for three or four levels of foreign key resolution in some 'master' object, containing all of the fields I want? Many many thanks in advance, Gloria --~--~-~--~~~---~--~~ 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: Need SqlAlchemy Model Advice for a table with many foreign keys.
so u have a member, pointing to member_profile, pointing to all its attributes into separate tables? one way IMO is to map all other 50 tables into simple classes, then have member profile reference each of them, i.e. relation( .. uselist=False). Then, if u need all of them at once, request a eagerload - by default in the relation(), or keep that lazy and only specify eagerload'ing at query time. it depends on the usage patterns. so far u'll have member.profile.gender.name or whatever. maybe u can make a property over gender.name to get that directly, i.e. member.profile.gender which is just shortcut to member.profile._gender.name (note _ in the start). i guess this can be slow - in worst case (all eagerloading) u'll be instantiating 50+objects on each member_profile. u can avoid all the object-per-attribute stuff via column_property() selectable-attributes, but i dont know if these can scale into eagerload. see if anyone has better suggestion. ciao svilen On Tuesday 03 February 2009 17:29:58 Gloria W wrote: Hi all, thanks for this really helpful list. My question now involves trying to figure out the correct way to map this legacy database to SqlAlchemy objects using the declarative_base model. This database has two main tables, members, and member_profiles, where a memberID is the foreign key from members to member_profiles. It then has about thirty other tables such as this one, called member_gender. member_gender contains a genderID, and a string representing 'Male' or 'Female'. genderID is a foreign key in member_profiles, pointing to member_gender. So the member_profiles table contains many of these xxxID fields which are foreign keys into many other tables which act as descriptor tables, abstracting (to the point of overkill) just about every bit of data in this database. The Left Join on member_profiles, to get all of the data I need in one fell swoop, is over 50 lines long. I want to use the declarative_base class model to represent this database in SqlAlchemy, but here are problems I face: 1: Multiple inheritance is not allowed, so I cannot create a class for every table and inherit them all into one master class. 2: If I create three classes, for example, say Members, MemberProfiles, and Gender, and use Members as the base class for MemberProfiles, and MemberProfiles as the base class for Gender, the Members 'class members' are not visible via Gender. The inheritance seems to only go two objects deep. I want to be able to access the gender 'descriptor' field containing the string 'Female' from one master object, instead of accessing 'genderID'. I want to be able to do this for all foreign keys in this member_profiles table. Is there a way to properly make such a model using the declarative_base methodology, which allows for three or four levels of foreign key resolution in some 'master' object, containing all of the fields I want? Many many thanks in advance, Gloria --~--~-~--~~~---~--~~ 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] combining single table and joined table inheritance
I have a base class/table with many common fields. Those fields are enough to handle several Child classes. So, I can use single table inheritance. But, I have a special case when another Child class has other fields so I should join new table in this case. Is this possible? Pseudo code example: Base: id type common_data Sub1(Base): # single table, type=1 pass Sub2(Base): # single table, type=2 pass Sub3(Base): # joined table, type=3 sub3_extra_data --~--~-~--~~~---~--~~ 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: combining single table and joined table inheritance
To answer my own question: it is working! On Feb 3, 2:10 pm, qvx qvx3...@gmail.com wrote: I have a base class/table with many common fields. Those fields are enough to handle several Child classes. So, I can use single table inheritance. But, I have a special case when another Child class has other fields so I should join new table in this case. Is this possible? Pseudo code example: Base: id type common_data Sub1(Base): # single table, type=1 pass Sub2(Base): # single table, type=2 pass Sub3(Base): # joined table, type=3 sub3_extra_data --~--~-~--~~~---~--~~ 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] print query with params
Is there anyway to print the query with the params in place? example: products = sa.query(entity_object).filter (entity_table.c.created_attime_object) print products Prints the query, but without the params. For debugging it would be nice if it could produce something i can paste and execute easily on my database interface. Is there anyway 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: 0.4 or 0.5 for new user, legacy mssql db
definitely use 0.5. We're already halfway through getting 0.6 ready and the release schedule of 0.4 is now very infrequent.There's very little in the book that doesn't work for 0.5 except for the signature for custom types. Other methods that are deprecated will issue descriptive warnings. Make sure you go through at least the ORM tutorial on the site to get a feel for the 0.5 way. In particular our support for MSSQL, a very tricky database in general, is loads better in 0.5 (0.5.2 especially) and will be even more so in 0.6. A full migration doc is at http://www.sqlalchemy.org/trac/wiki/05Migration . davidlmontgomery wrote: Hi Folks, I would like your recommendations for whether I should use sqlalchemy version 0.4.x or 0.5.x. I'm using it with django for talking to a secondary MS SQL server database. This is a legacy db where I'm be autoloading the schema. I'm just getting started with sqlalchemy and I probably won't be doing anything too sophisticated. I've been reading Copeland's Essential SQLAlchemy, which I believe covers 0.4. I'm not sure how much the basic interfaces have changed from 0.4 to 0.5. That, and concern about the support for mssql, were the main reasons I was thinking it might be better to stick with 0.4. Thanks for your input. David --~--~-~--~~~---~--~~ 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] Typo in synonym documentation?
Hi,
in the docs, here:
http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html#defining-synonyms
there is this code snippet:
class MyClass(Base):
__tablename__ = 'sometable'
_attr = Column('attr', String)
def _get_attr(self):
return self._some_attr
def _set_attr(self, attr):
self._some_attr = attr
attr = synonym('_attr', descriptor=property(_get_attr, _set_attr))
The references to self._some_attr should be self._attr, right? And
likewise for the next code sample?
Thanks,
- Gulli
--~--~-~--~~~---~--~~
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] 0.4 or 0.5 for new user, legacy mssql db
Hi Folks, I would like your recommendations for whether I should use sqlalchemy version 0.4.x or 0.5.x. I'm using it with django for talking to a secondary MS SQL server database. This is a legacy db where I'm be autoloading the schema. I'm just getting started with sqlalchemy and I probably won't be doing anything too sophisticated. I've been reading Copeland's Essential SQLAlchemy, which I believe covers 0.4. I'm not sure how much the basic interfaces have changed from 0.4 to 0.5. That, and concern about the support for mssql, were the main reasons I was thinking it might be better to stick with 0.4. Thanks for your input. David --~--~-~--~~~---~--~~ 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: print query with params
Is there anyway to print the query with the params in place? There's this Recipe (http://www.sqlalchemy.org/trac/wiki/DebugInlineParams ). -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.com/ --~--~-~--~~~---~--~~ 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 display table creation sql statement?
2009/2/3 Sergei Beilin sbei...@narod.ru: On 3 фев, 09:31, limodou limo...@gmail.com wrote: Thanks and I want to know if I must call meta.create_all(engine), because this code will create the tables, but I just want to see the SQL statements, but not create the tables. Note the additional properties: strategy='mock', executor=lambda s, p='': buf.write(s + p) As far as I understand, they will prevent creating the tables and would just write the create statements to 'buf'. Thank you very much, I'll test it. -- I like python! UliPad The Python Editor: http://code.google.com/p/ulipad/ UliWeb simple web framework: http://uliwebproject.appspot.com My Blog: (new)http://http://hi.baidu.com/limodou (old)http://www.donews.net/limodou --~--~-~--~~~---~--~~ 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: cascade=all, delete, delete-orphan causes insert to fail
Gloria W wrote: D'OH! (slaps head) Thanks for pointing this out, it makes perfect sense. I got it working by getting it from the DB, and merging it into the current session, then deleting: def DELETE(self,dmemberid): x = MemberInfo() memberProfile = x.GET(memberid=dmemberid,raw=True) merged_obj = self.session.merge(memberProfile[0]) self.session.delete(merged_obj) self.session.commit() Without merging into the same session, I got this error: sqlalchemy.exc.InvalidRequestError: Object 'MemberProfile at 0x-48935294' is already attached to session '-1217720980' (this is '-1217780404') I'm not sure why a merge into the current session was necessary, since I did the get() in the same session. Does each query flush the session, and create a new session key behind the scenes? its definitely not the same session. otherwise memberProfile[0] is merged_obj would return True. So happy this is working, thank you! Gloria --~--~-~--~~~---~--~~ 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: New problem with synonym (getter/setter) use in 0.5.2
Oh, sorry, forgot to mention: Python 2.5.2 SQLAlchemy 0.5.2 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
