[sqlalchemy] Re: sqlalchemy and postgresql warning messages
Michael Bayer ha scritto: On Nov 20, 2007, at 4:35 AM, Manlio Perillo wrote: I have asked on the psycopg2 list. psycopg2 connection has a notices attribute. try: conn = db.connect() metadata.create_all(bind=conn) print conn.connection.connection.notices finally: metadata.drop_all() ['NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b\n', 'WARNING: foreign key constraint b_id_fkey will require costly sequential scans\nDETAIL: Key columns id and id are of different types: text and integer.\n'] So SQLAlchemy can process it, if this is reasonable. hmmm, thats interesting. it would have to be placed at the execute level, but of course we are only going to issue warnings, not exceptions. I think this might also be better as an optional flag for the PG dialect. create a ticket in trac else I'll lose track of this one Done: #877. Manlio Perillo --~--~-~--~~~---~--~~ 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: Sum with Grouping
Oh, okay. Thanks you. On Nov 20, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote: the aggregate methods on Query, such as apply_sum(), apply_avg(), etc., are not in such great shape right now...they've been neglected and in fact aren't even working correctly with GROUP BY, etc...I've added trac ticket #876 for this. If you know the exact SQL and columns you'd like to get back, typically its best just to issue that query without using an ORM construct. your options for this are either just text: engine.execute(SELECT sum(amount), type from purchases group by type).fetchall() or the expression construct would look like: engine.execute(select([func.sum(Purchase.amount), Purchase.type]).group_by(Purchase.type)).fetchall() one important thing to be aware of is that SA has two distinct levels of SQL API; the ORM, which deals with Sessions and Query objects, and the SQL Expression Language, which deals with select(), update(), etc. the expression language provides more direct access to SQL constructs. --~--~-~--~~~---~--~~ 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: Sum with Grouping
thank you for the response. I have a few more followup questions (I am really a newbie to this...) : 1. Where does engine come from? Is there anyway to do what you've suggested with Session? If it helps, I'm using this with pylons and am trying to get stuff working in the controller of my app. 2. If I have to write SQL (or a pythonic version of SQL) to get info out of the DB, why am I even using SQL Alchemy? it seems a little ridiculous. On Nov 20, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote: the aggregate methods on Query, such as apply_sum(), apply_avg(), etc., are not in such great shape right now...they've been neglected and in fact aren't even working correctly with GROUP BY, etc...I've added trac ticket #876 for this. If you know the exact SQL and columns you'd like to get back, typically its best just to issue that query without using an ORM construct. your options for this are either just text: engine.execute(SELECT sum(amount), type from purchases group by type).fetchall() or the expression construct would look like: engine.execute(select([func.sum(Purchase.amount), Purchase.type]).group_by(Purchase.type)).fetchall() one important thing to be aware of is that SA has two distinct levels of SQL API; the ORM, which deals with Sessions and Query objects, and the SQL Expression Language, which deals with select(), update(), etc. the expression language provides more direct access to SQL constructs. --~--~-~--~~~---~--~~ 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: Sum with Grouping
On Nov 21, 1:02 pm, Matt Haggard [EMAIL PROTECTED] wrote: thank you for the response. I have a few more followup questions (I am really a newbie to this...) : 1. Where does engine come from? Is there anyway to do what you've suggested with Session? If it helps, I'm using this with pylons and am trying to get stuff working in the controller of my app. you can get a connection from your session by saying conn = session.connection(). then you can use that to execute. Im assuming youre using the Pylons tutorial, where your engine has been explicitly associated with your Session. if not, then you'd have to say conn = session.connection(MyMappedClass). 2. If I have to write SQL (or a pythonic version of SQL) to get info out of the DB, why am I even using SQL Alchemy? it seems a little ridiculous. well first of all, the apply_sum() function you were trying to use *will* work once we get around to fixing it up. this is open source development; SA follows the philosophy of release early, release often so that the toolset, while we are fairly polished at this point and most users seem to have a decent experience, will always have some under construction elements to it. the users of an open source project are as much a part of the project's continued development as the developers themselves, just like your stumbling upon apply_sum() being broken results in trac tickets, new fixes and new test coverage (coverage tools alone could not locate this issue since the individual lines of code *are* covered in current unit tests). Also, SA is an abstraction layer over SQL and DBAPIit is designed for people who like SQL and are familiar with its workings, who want a toolset that eliminates most of the repetition and tedium of generating average SQL and dealing with the mechanics of DBAPI, while at all times providing fine-grained ability to issue literal SQL at any point. SA is not designed to insulate the developer from SQL, and competing toolsets that have been introduced since SA's release are also taking the same approach. The services SA provides (which you can read on the intro page and some of the linked pages there) include: connection pooling, abstraction from DBAPI differences, convenience features at the SQL execution and result set level, SQL expression constructs, and finally the ORM which is an entirely optional feature. Lots of people don't use the ORM at all, but still find it easier to say: table.insert().execute(col1='somevalue') rather than: conn = mysqldb.connect(username='scott', password='tiger', db='test') cursor = conn.cursor() cursor.execute(insert into mytable (col) values (%s), ['somevalue']) conn.commit() conn.close() Theres a lot more to it than that, such as having an already opened connection available, being able to use different databases like PG transparently, etc. this is all without ever having a Session or Query mentioned at all - the orm package is just icing on the cake. DBAPI is just a pain in the butt to work with directly. --~--~-~--~~~---~--~~ 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] Linking column default values?
Hi guys, sorry for the vague subject, here is my problem. Let's say I
have the following table:
a = Table('aaa', meta,
Column('id', Integer, primary_key=True),
Column('id2', Integer))
class A(object):
pass
mapper(A, a)
I want the default value of A.id2 to be that of a A.id. So, for
example, unless I modify it, I would like the A.id2 to be sort of a
duplicate primary key. I know I can find out what is A.id after
committing my object to database and then setting A.id2 accordingly,
but I was wondering can SA do this automatically for me? Maybe some
kind of a special `default=` construct in the Column call?
Thanks,
Karlo.
--~--~-~--~~~---~--~~
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] deleting
Good Day sqlalchemy. I was searching, but didn't found a way to delete records from db not executing selection first. So, how to represent this SQL statement in slqalchemy ORM : DELETE FROM a WHERE b = c ? not it look like this : stuff = session.query(A).select_from(a_table).filter(A.c.b==c).first() session.delete(stuff) P.S. How to understand the following : Traceback (most recent call last): File threading.py, line 442, in __bootstrap self.run() File ./camper.py, line 173, in run session.delete(stuff) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/scoping.py, line 74, in do return getattr(self.registry(), name)(*args, **kwargs) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 849, i n delete self._delete_impl(object) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 1007, in _delete_impl raise exceptions.InvalidRequestError(Instance '%s' is not persisted % mapp erutil.instance_str(obj)) InvalidRequestError: Instance '[EMAIL PROTECTED]' is not persisted It occurs sometimes in threaded application in this code : from sqlalchemy.orm import mapper, relation, backref, create_session, scoped_session session = scoped_session(create_session) stuff = session.query(Path).select_from(f_table.join(u_table)).filter(User.c.id==theone.id).first() session.delete(stuff) --~--~-~--~~~---~--~~ 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: deleting
imgrey ha scritto: Good Day sqlalchemy. I was searching, but didn't found a way to delete records from db not executing selection first. You must istantiate an istance of the obj to be deleted. This is an Object Manager, so all operation is available on object. or you can execute a plain string-sql direclty from engine connection = engine.connect() connection.execute(DELETE FROM a WHERE b = c) So, how to represent this SQL statement in slqalchemy ORM : DELETE FROM a WHERE b = c ? not it look like this : stuff = session.query(A).select_from(a_table).filter(A.c.b==c).first() session.delete(stuff) P.S. How to understand the following : Traceback (most recent call last): File threading.py, line 442, in __bootstrap self.run() File ./camper.py, line 173, in run session.delete(stuff) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/scoping.py, line 74, in do return getattr(self.registry(), name)(*args, **kwargs) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 849, i n delete self._delete_impl(object) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 1007, in _delete_impl raise exceptions.InvalidRequestError(Instance '%s' is not persisted % mapp erutil.instance_str(obj)) InvalidRequestError: Instance '[EMAIL PROTECTED]' is not persisted seems your object is not attached to session. It occurs sometimes in threaded application in this code : from sqlalchemy.orm import mapper, relation, backref, create_session, scoped_session session = scoped_session(create_session) stuff = session.query(Path).select_from(f_table.join(u_table)).filter(User.c.id==theone.id).first() session.delete(stuff) i don't use scoped_session. but try instead stuff.delete() Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
