[sqlalchemy] Postgresql COPY
Is it possible to execute a Postegresql COPY ... FROM STDIN statement via sqlalchemy, or do we have to drop down to psycopg2 for that (http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from)? Tried executing a COPY statement followed directly by the input values, as in psql, but that didn't work. Thanks in advance. -- Yang Zhang http://yz.mit.edu/ -- 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] How to find columns being updated/inserted given a Update/Insert object
Given an Update(or Insert) object how can I find the columns being updated (or Inserted). I dont find any function that gives these? t = Table(abc, MetaData(conn), Column(x, String), Column(y, Integer)) u = t.update().values(x='a') # Now I want to get list of columns being updated (i.e., x) given u (i.e., Update 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Context based execution
Hi, Thanks a lot. Can someone answer this question also = @compiles(Select) def contextual_select_thing(select, compiler, **kw): This method gets registered with Select. But How/When does this registration automatically happen? -- 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 window functions in expressions
Yeah SQL 2003 standard says no comma between window specification details generally, and postgresql, oracle at least aren't expecting it. (ansi 2003 draft:) 7.11 window clause ... window specification details ::= [ existing window name ] [ window partition clause ] [ window order clause ] [ window frame clause ] window partition clause ::= PARTITION BY window partition column reference list ... window partition column reference ::= column reference [ collate clause ] window order clause ::= ORDER BY sort specification list etc. On Apr 14, 10:14 pm, botz randa...@gmail.com wrote: Great, thanks for the quick fix. Found another minor bug with the window functions, syntax should be ( partition by x order by y) not ( partition by x, order by y) ... at least for postgresql. On Apr 14, 8:51 pm, botz randa...@gmail.com wrote: Here's a quick example: the query form i'm aiming for is: select x - lag(x) over ( order by x ) from a; meta=MetaData() a = Table('a', meta, Column('x',Integer)) print select([ a.c.x , over( func.lag(a.c.x), order_by = a.c.x ) ] ) # no problem print select([ a.c.x - over( func.lag(a.c.x), order_by = a.c.x ) ] ) # this one fails to compile Here's the error: print select([ a.c.x - over( func.lag(a.c.x), order_by = a.c.x ) ] ) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 276, in select **kwargs) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 4317, in __init__ self._froms.update(_from_objects(*self._raw_columns)) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 1165, in _from_objects return itertools.chain(*[element._from_objects for element in elements]) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 3265, in _from_objects return self.left._from_objects + self.right._from_objects TypeError: can only concatenate list (not itertools.chain) to list -- 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.
Re: [sqlalchemy] Postgresql COPY
On Apr 15, 2011, at 3:17 AM, Yang Zhang wrote: Is it possible to execute a Postegresql COPY ... FROM STDIN statement via sqlalchemy, or do we have to drop down to psycopg2 for that (http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from)? Tried executing a COPY statement followed directly by the input values, as in psql, but that didn't work. Thanks in advance. SQLAlchemy doesn't have functions that connect to psycopg2's copy_from() etc. methods and from psycopg2s docs it seems this is how those functions are usable, as opposed to emitting the word COPY. So you know as much as I do here, and you likely have to use the psycopg2 connection.you can get one from the pool via engine.raw_connection() if you wanted. -- Yang Zhang http://yz.mit.edu/ -- 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. -- 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.
Re: [sqlalchemy] Re: using window functions in expressions
yeah got your ticket someone will get to it today hopefully, thanks ! On Apr 15, 2011, at 9:47 AM, botz wrote: Yeah SQL 2003 standard says no comma between window specification details generally, and postgresql, oracle at least aren't expecting it. (ansi 2003 draft:) 7.11 window clause ... window specification details ::= [ existing window name ] [ window partition clause ] [ window order clause ] [ window frame clause ] window partition clause ::= PARTITION BY window partition column reference list ... window partition column reference ::= column reference [ collate clause ] window order clause ::= ORDER BY sort specification list etc. On Apr 14, 10:14 pm, botz randa...@gmail.com wrote: Great, thanks for the quick fix. Found another minor bug with the window functions, syntax should be ( partition by x order by y) not ( partition by x, order by y) ... at least for postgresql. On Apr 14, 8:51 pm, botz randa...@gmail.com wrote: Here's a quick example: the query form i'm aiming for is: select x - lag(x) over ( order by x ) from a; meta=MetaData() a = Table('a', meta, Column('x',Integer)) print select([ a.c.x , over( func.lag(a.c.x), order_by = a.c.x ) ] ) # no problem print select([ a.c.x - over( func.lag(a.c.x), order_by = a.c.x ) ] ) # this one fails to compile Here's the error: print select([ a.c.x - over( func.lag(a.c.x), order_by = a.c.x ) ] ) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 276, in select **kwargs) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 4317, in __init__ self._froms.update(_from_objects(*self._raw_columns)) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 1165, in _from_objects return itertools.chain(*[element._from_objects for element in elements]) File /usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7b4dev- py2.7-linux-x86_64.egg/sqlalchemy/sql/expression.py, line 3265, in _from_objects return self.left._from_objects + self.right._from_objects TypeError: can only concatenate list (not itertools.chain) to list -- 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. -- 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.
RE: [sqlalchemy] Re: Context based execution
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of bool Sent: 15 April 2011 14:41 To: sqlalchemy Subject: [sqlalchemy] Re: Context based execution Hi, Thanks a lot. Can someone answer this question also = @compiles(Select) def contextual_select_thing(select, compiler, **kw): This method gets registered with Select. But How/When does this registration automatically happen? The implementation of the compiler extension is very short - you can see it at http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/ext/compiler.py. It looks like it modifies the target class to add _compiler_dispatcher and _compiler_dispatch attributes to it (or update them if it already has them). The SA statement compiler must look at these attributes to determine how to compile the statement. The registration happens as soon as the @compiles(Select) decorator is evaluated. If it is at module-global scope (rather than being buried inside another function), it'll happen when the module is imported. Hope that helps, Simon -- 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.
Re: [sqlalchemy] How to find columns being updated/inserted given a Update/Insert object
Given an Update(or Insert) object how can I find the columns being updated (or Inserted). I dont find any function that gives these? The attributes.get_history method will return this information to you as a History record for each attribute. See here: http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.attributes.get_history -- Michael Trier http://michaeltrier.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 find columns being updated/inserted given a Update/Insert object
On Apr 15, 11:55 am, Michael Trier mtr...@gmail.com wrote: Given an Update(or Insert) object how can I find the columns being updated (or Inserted). I dont find any function that gives these? The attributes.get_history method will return this information to you as a History record for each attribute. See here: http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.attrib... Ugh misread your example. The above is only ORM related. Sorry. Michael -- 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 find columns being updated/inserted given a Update/Insert object
On Apr 15, 7:03 am, bool manohar.kod...@gmail.com wrote: Given an Update(or Insert) object how can I find the columns being updated (or Inserted). I dont find any function that gives these? t = Table(abc, MetaData(conn), Column(x, String), Column(y, Integer)) u = t.update().values(x='a') # Now I want to get list of columns being updated (i.e., x) given u (i.e., Update object) Let's try this again. The compiler has a params attribute that will get you close to what you want. See the following doc on insert expressions which demonstrates how this works. http://www.sqlalchemy.org/docs/core/tutorial.html#insert-expressions Michael -- 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.
Re: [sqlalchemy] Postgresql COPY
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Il 15/04/2011 09:17, Yang Zhang ha scritto: Is it possible to execute a Postegresql COPY ... FROM STDIN statement via sqlalchemy, or do we have to drop down to psycopg2 for that (http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from)? Tried executing a COPY statement followed directly by the input values, as in psql, but that didn't work. Thanks in advance. The problem is that psycopg2, if I'm not wrong, only supports an high level interface to COPY command. With plain libpq, you can issue a normal query string with the COPY command, and then use PQputCopyData and PQputCopyEnd functions to send data. psycopg2, instead, implements custom methods for copy support (I assume this is done to avoid having to handle/expose the additional PGRES_COPY_OUT or PGRES_COPY_IN status code, and an additional PGRES_COMMAND_OK after PQputCopyEnd). Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2odH0ACgkQscQJ24LbaURe/gCgmptRuAv1usFDYIzChjUIDt/1 bdwAnjLUij/CFScNEv1zV/K/Y//v9Akf =Sxsi -END PGP SIGNATURE- -- 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.