date:20100302

[sqlalchemy] extended use of VALUES command

2010-03-02 Thread Manlio Perillo

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi.

Is it possible, in SQLAlchemy, to express this query?

CREATE TEMP TABLE foo (
x INTEGER,
y INTEGER
);

INSERT INTO foo VALUES (10, 11);
INSERT INTO foo VALUES (1, 2);

manlio= SELECT * FROM foo WHERE (x, y) in (VALUES (1, 2), (3, 4));
 x | y
- ---+---
 1 | 2
(1 riga)


This should be standard SQL.
It is supported by PostgreSQL [1], but SQLite fails with a syntax error.


[1] http://www.postgresql.org/docs/8.4/static/sql-values.html


Thanks  Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkuNNh0ACgkQscQJ24LbaUTuowCffJyiWt4/Hi9adT0Vrk4K5/6A
B8EAnA2JOmsHBQVnPiEHsrFZIW19iLx6
=ijKV
-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 sqlalch...@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] extended use of VALUES command

2010-03-02 Thread King Simon-NFHD78

Manlio Perillo wrote:
 
 Hi.
 
 Is it possible, in SQLAlchemy, to express this query?
 
 CREATE TEMP TABLE foo (
 x INTEGER,
 y INTEGER
 );
 
 INSERT INTO foo VALUES (10, 11);
 INSERT INTO foo VALUES (1, 2);
 
 manlio= SELECT * FROM foo WHERE (x, y) in (VALUES (1, 2), (3, 4));
  x | y
 - ---+---
  1 | 2
 (1 riga)
 
 
 This should be standard SQL.
 It is supported by PostgreSQL [1], but SQLite fails with a 
 syntax error.
 
 
 [1] http://www.postgresql.org/docs/8.4/static/sql-values.html
 
 
 Thanks  Manlio

Does the following thread help at all?

http://groups.google.com/group/sqlalchemy/browse_thread/thread/7f950b628
d7ebee5/4421b272d4c7f91f

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 sqlalch...@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 cache SQLAlchemy results?

2010-03-02 Thread Boda Cydo

Hello,

I have a problem that I don't know how to approach. Here it is:

Suppose I have a mapped class `Article` and I have a mapped class
`Author`. `Article.author` is a relation on `Author` that returns the
author of the `Article`. How do I make `Article.author` to cache the
value in memcached?

The problem is that referring to `Article.author` does an SQL query
each time it is called, but there is really no need to query the
database for author every single time.

This value can be cached in memcached and returned instantly from
memory.

What I can't figure out is how to cache the value of relation in
SQLAlchemy?

I could create another property, like `Article.cached_author` which
would first consult memcached for key `article_id_author`, and if
it's not there, call `Article.author`, and store this value value in
memcached.

But I don't want to invent new names for my properties.

Can anyone help me figure out how to use the SQLAlchemy relation
property names and still cache them with memcached?


Thanks, Boda Cydo.

Ps. Some people have said that SQLAlchemy caches the values already,
but that is not true! It caches the value **within a single session**
but I am talking here about tens of sessions using my program
simultaneously.

Pss. Please also don't assume that I have single property
`Article.author`, I have various properties, like `Article.keywords`
that returns a long list of keywords associated with Article. If I
don't cache the values returned by accessing these properties, the
application will soon kill the computer it is running on because of
requesting the same data over and over and over again from the
database.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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 cache SQLAlchemy results?

2010-03-02 Thread Michael Bayer

Boda Cydo wrote:
 Hello,

 I have a problem that I don't know how to approach. Here it is:

 Suppose I have a mapped class `Article` and I have a mapped class
 `Author`. `Article.author` is a relation on `Author` that returns the
 author of the `Article`. How do I make `Article.author` to cache the
 value in memcached?

Use the beaker caching recipe introduced at:

http://www.sqlalchemy.org/docs/examples.html#module-beaker_caching







 The problem is that referring to `Article.author` does an SQL query
 each time it is called, but there is really no need to query the
 database for author every single time.

 This value can be cached in memcached and returned instantly from
 memory.

 What I can't figure out is how to cache the value of relation in
 SQLAlchemy?

 I could create another property, like `Article.cached_author` which
 would first consult memcached for key `article_id_author`, and if
 it's not there, call `Article.author`, and store this value value in
 memcached.

 But I don't want to invent new names for my properties.

 Can anyone help me figure out how to use the SQLAlchemy relation
 property names and still cache them with memcached?


 Thanks, Boda Cydo.

 Ps. Some people have said that SQLAlchemy caches the values already,
 but that is not true! It caches the value **within a single session**
 but I am talking here about tens of sessions using my program
 simultaneously.

 Pss. Please also don't assume that I have single property
 `Article.author`, I have various properties, like `Article.keywords`
 that returns a long list of keywords associated with Article. If I
 don't cache the values returned by accessing these properties, the
 application will soon kill the computer it is running on because of
 requesting the same data over and over and over again from the
 database.

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] oracle insert problem

2010-03-02 Thread Michael Bayer

read the note about case sensitivity in

http://www.sqlalchemy.org/docs/reference/dialects/oracle.html?highlight=oracle#identifier-casing


celord wrote:
 Hello guys, I have the table on an oracle db:

 SQL desc MANPUSHMAIL.PUSHMAIL;
  Nombre¿Nulo?  Tipo
  - 
 
  TELEFONONOT NULL VARCHAR2(12)
  FECHADATE
  CORREO   VARCHAR2(40)
  ESTADO   NUMBER(1)
  FECHA_EJECUCION  DATE
  FECHA_EJECUTADO  DATE
  RESPUESTAVARCHAR2(500)

 I can insert data succesfully  via sqlplus like this:

 insert into MANPUSHMAIL.PUSHMAIL values
 ('',sysdate,'t...@domain.con','','','','');

 but using this script http://www.pastebin.org/100014 via sqlalchemy I
 get this:

 sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
 does not exist
  'INSERT INTO MANPUSHMAIL.PUSHMAIL (FECHA, CORREO) VALUES
 (:FECHA, :CORREO) RETURNING MANPUSHMAIL.PUSHMAIL.TELEFONO
 INTO :ret_0' {'CORREO': None, 'FECHA': None, 'ret_0':
 cx_Oracle.STRING with value None}

 I do not know if the error could be here: 'INSERT INTO
 MANPUSHMAIL.PUSHMAIL, because when I try to do the insert with the
 table name between quotes I get the same error:

 insert into MANPUSHMAIL.PUSHMAIL values
 ('',sysdate,'te...@domain.com','','','','')
 ORA-00942: table or view does not exist

 I am using sqlalchemy version: 0.6beta1 , Python 2.6 on a Solaris 10
 box, and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

 Thanks a lot!!!

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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] behavior of clear_mapper() function

2010-03-02 Thread Tony Tan

there is something really wired about orm.clear_mappers() function.
here is what i did:
 1. I used a mapper to add an instance to a session, i.e
session.add(instance)
 2. use clear_mappers() to clear all existing mapper
 3. define another mapper using mapper(), including a 1:N relationship
in it.
 4. after all above step, the new mapping does not have the new 1:N
relationship member in that mapped class.

may be I need to show the code snippet to make it clear. But this is
my first time to post here. I will wait and see.
  ---Tony

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] behavior of clear_mapper() function

2010-03-02 Thread Michael Bayer

Tony Tan wrote:
 there is something really wired about orm.clear_mappers() function.
 here is what i did:
  1. I used a mapper to add an instance to a session, i.e
 session.add(instance)
  2. use clear_mappers() to clear all existing mapper
  3. define another mapper using mapper(), including a 1:N relationship
 in it.
  4. after all above step, the new mapping does not have the new 1:N
 relationship member in that mapped class.

 may be I need to show the code snippet to make it clear. But this is
 my first time to post here. I will wait and see.

changing mappers on existing instances (note: instances, not classes) is
not supported.  clear_mappers() is intended only for test suites that want
to reset mappings on a set of classes in preparation for a new test.




   ---Tony

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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: oracle insert problem

2010-03-02 Thread celord

Thanks Michael, I have changed all UPERCASE to lowercase
http://www.pastebin.org/100149, the output has changed a bit but the
table name is still quoted and the insert does not work

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
does not exist
 'INSERT INTO manpushmail.pushmail (telefono, fecha, correo) VALUES
(:telefono, :fecha, :correo)' {'correo': 'cgar...@ice.co.cr', 'fecha':
' ', 'telefono': '87445511'}

On 2 mar, 12:39, Michael Bayer mike...@zzzcomputing.com wrote:
 read the note about case sensitivity in

 http://www.sqlalchemy.org/docs/reference/dialects/oracle.html?highlig...

 celord wrote:
  Hello guys, I have the table on an oracle db:

  SQL desc MANPUSHMAIL.PUSHMAIL;
   Nombre                                     Nulo?  Tipo
   - 
  
   TELEFONO                             NOT NULL VARCHAR2(12)
   FECHA                                                 DATE
   CORREO                                        VARCHAR2(40)
   ESTADO                                        NUMBER(1)
   FECHA_EJECUCION                               DATE
   FECHA_EJECUTADO                               DATE
   RESPUESTA                                     VARCHAR2(500)

  I can insert data succesfully  via sqlplus like this:

  insert into MANPUSHMAIL.PUSHMAIL values
  ('',sysdate,'t...@domain.con','','','','');

  but using this scripthttp://www.pastebin.org/100014via sqlalchemy I
  get this:

  sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
  does not exist
   'INSERT INTO MANPUSHMAIL.PUSHMAIL (FECHA, CORREO) VALUES
  (:FECHA, :CORREO) RETURNING MANPUSHMAIL.PUSHMAIL.TELEFONO
  INTO :ret_0' {'CORREO': None, 'FECHA': None, 'ret_0':
  cx_Oracle.STRING with value None}

  I do not know if the error could be here: 'INSERT INTO
  MANPUSHMAIL.PUSHMAIL, because when I try to do the insert with the
  table name between quotes I get the same error:

  insert into MANPUSHMAIL.PUSHMAIL values
  ('',sysdate,'te...@domain.com','','','','')
  ORA-00942: table or view does not exist

  I am using sqlalchemy version: 0.6beta1 , Python 2.6 on a Solaris 10
  box, and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

  Thanks a lot!!!

  --
  You received this message because you are subscribed to the Google Groups
  sqlalchemy group.
  To post to this group, send email to sqlalch...@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 sqlalch...@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: oracle insert problem

2010-03-02 Thread Michael Bayer


additionally don't shove a schema/owner name into your table's name field.  Use 
the schema kw arg for that.

On Mar 2, 2010, at 4:23 PM, celord wrote:

 Thanks Michael, I have changed all UPERCASE to lowercase
 http://www.pastebin.org/100149, the output has changed a bit but the
 table name is still quoted and the insert does not work
 
 sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
 does not exist
 'INSERT INTO manpushmail.pushmail (telefono, fecha, correo) VALUES
 (:telefono, :fecha, :correo)' {'correo': 'cgar...@ice.co.cr', 'fecha':
 ' ', 'telefono': '87445511'}
 
 On 2 mar, 12:39, Michael Bayer mike...@zzzcomputing.com wrote:
 read the note about case sensitivity in
 
 http://www.sqlalchemy.org/docs/reference/dialects/oracle.html?highlig...
 
 celord wrote:
 Hello guys, I have the table on an oracle db:
 
 SQL desc MANPUSHMAIL.PUSHMAIL;
  Nombre Nulo?  Tipo
  - 
 
  TELEFONO NOT NULL VARCHAR2(12)
  FECHA DATE
  CORREOVARCHAR2(40)
  ESTADONUMBER(1)
  FECHA_EJECUCION   DATE
  FECHA_EJECUTADO   DATE
  RESPUESTA VARCHAR2(500)
 
 I can insert data succesfully  via sqlplus like this:
 
 insert into MANPUSHMAIL.PUSHMAIL values
 ('',sysdate,'t...@domain.con','','','','');
 
 but using this scripthttp://www.pastebin.org/100014via sqlalchemy I
 get this:
 
 sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
 does not exist
  'INSERT INTO MANPUSHMAIL.PUSHMAIL (FECHA, CORREO) VALUES
 (:FECHA, :CORREO) RETURNING MANPUSHMAIL.PUSHMAIL.TELEFONO
 INTO :ret_0' {'CORREO': None, 'FECHA': None, 'ret_0':
 cx_Oracle.STRING with value None}
 
 I do not know if the error could be here: 'INSERT INTO
 MANPUSHMAIL.PUSHMAIL, because when I try to do the insert with the
 table name between quotes I get the same error:
 
 insert into MANPUSHMAIL.PUSHMAIL values
 ('',sysdate,'te...@domain.com','','','','')
 ORA-00942: table or view does not exist
 
 I am using sqlalchemy version: 0.6beta1 , Python 2.6 on a Solaris 10
 box, and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
 
 Thanks a lot!!!
 
 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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 sqlalch...@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 sqlalch...@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] extended use of VALUES command

RE: [sqlalchemy] extended use of VALUES command

[sqlalchemy] How to cache SQLAlchemy results?

Re: [sqlalchemy] How to cache SQLAlchemy results?

Re: [sqlalchemy] oracle insert problem

[sqlalchemy] behavior of clear_mapper() function

Re: [sqlalchemy] behavior of clear_mapper() function

[sqlalchemy] Re: oracle insert problem

Re: [sqlalchemy] Re: oracle insert problem

9 matches

Site Navigation

Mail list logo

Footer information