[sqlalchemy] python customer function

[jo]

Hi all,

I would like to create a python customer function to extract data from a
table column and return a calculated value as in:

def birth(data):
  mm =
dict(A='01',B='02',C='03',D='04',E='05',H='06',L='07',M='08',P='09',R='10',S='11',T='12')
  return '19'+data[6:8] +'-'+ mm[data[8:9]] +'-'+ data[9:11]



sa.func.to_date( birth(Anagrafica.c.dato_fiscale), '-mm-dd'))



in: Anagrafica.c.dato_fiscale='ZZZHHH54M11Z128Y'
out: '1954-08-11'

How can I do this in a sqlalchemy query?

thanks for any help.
j

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] sql custom function

[jo]

Hi Glauco, I'm glad to hear you. :-)
Yes, you were right, I really forgot to register the function.
I'll see you around.

j

Glauco wrote:

Il 10/02/2015 09:33, jo ha scritto:

Hi all,

I can't realize how to create a sql custom function. I need to 
transform a string column value into a number as in:


def mese(par):
   mm = dict(A=1,B=2,C=3,D=4,E=5,H=6,L=7,M=8,P=9,R=10,S=11,T=12)
   return mm[par.upper()]


I tried to use it in a query as this one below, but it doesn't work:

session.query(Ana).filter(Ana.c.mese==mese(sa.func.substr(Ana.c.cfiscale,8,9)) 



Please, could someone help me to write a such function?
thanks

j




Hi Jo,
probably you forgot to register function?

Sure you can optimize this code, but this work.

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
e = create_engine('sqlite://')

metadata = MetaData()
t = Table('t', metadata, Column('v', String(1)))
metadata.create_all(e)

def mese(par):
   mm = dict(A=1,B=2,C=3,D=4,E=5,H=6,L=7,M=8,P=9,R=10,S=11,T=12)
   return mm[par.upper()]

conn = sessionmaker(e)().connection()
conn.connection.connection.create_function(mese,1,mese)

conn.execute( t.insert().values(v='P'))
conn.execute( t.insert().values(v='D'))

conn.execute(select v, mese(v) from t).fetchall()

[(u'P', 9), (u'D', 4)]

Glauco






--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] session query and column names

[jo]

Can you send us an example how your myModel is?

j

Mehdi wrote:

How could i guess the proper aliased column name then?
In your example your table is specie and column name is codice, so 
you used specie_codice=42. but as i said i have joins and longer 
column names, so sometimes i get like table1.somelongcolumn AS 
table1_somelong_7.


On Monday, January 12, 2015 at 3:09:22 PM UTC+3:30, jo wrote:

Why don't you pass the params to session.query as a dictionary
into filter_by as in:
 
In [1]: by_where_clause=dict(specie_codice='42',

specie_descrizione='Nutrie')

In [2]: print session.query(Specie).filter_by( **by_where_clause
).count()

2015-01-12 12:37:40,518 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM (SELECT specie.codice AS specie_codice, specie.descrizione AS
specie_descrizione, specie.cod_gruppo_specie AS
specie_cod_gruppo_specie, specie.categoria_prodotto AS
specie_categoria_prodotto, specie.interparto AS specie_interparto,
specie.gestazione AS specie_gestazione, specie.codice_bdn AS
specie_codice_bdn
FROM specie
WHERE specie.codice = %(codice_1)s AND specie.descrizione =
%(descrizione_1)s) AS anon_1
2015-01-12 12:37:40,518 INFO sqlalchemy.engine.base.Engine
{'codice_1': '42', 'descrizione_1': 'Nutrie'}
1

In [3]:


j


On 01/12/2015 11:45 AM, Mehdi wrote:

Hi
I want to make a query with given filters received from client
side. client side sends a json array like this:
| |
[
0DA: {conditionType: null, column: serialNumber,
operator: GREATER_THAN, value: 50},
1DG: {conditionType: AND, column: name, operator:
EQUAL, value: john }
]

I've managed to convert the json array into string query with a
template like this:
| |
 {condition_type} {column} {operator} {value} 

But this is only the WHERE clause. for complete query i
use session.query(myModel) which myModel has two relationships
with lazy=joined mix with text() for filters:
| |
session.query(myModel).filter(text(sql_string))

But the problem is the session.query() uses alias column
names(with AS keyword) which my filter part with exact column
names couln'd find those aliased columns.
So Is there a way to tell session.query() use the exact column
names? or i should change my way?
Thanks.


-- 
You received this message because you are subscribed to the

Google Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it,
send an email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google 
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to sqlalchemy+unsubscr...@googlegroups.com 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto:sqlalchemy@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.



--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69   _/_/_/_/_/
40033 Casalecchio di Reno _/_/  _/_/  _/_/
Bologna - Italy  _/_/  _/_/  _/_/
Ph  +39051591054  _/_/  _/_/  _/_/  _/_/
fax +390516131537_/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com_/_/_/  _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
You received this message because you are subscribed to the Google Groups

Re: [sqlalchemy] DefaultClause

[jo]

Do you suggest to use server_default instead of DefaultClause in all cases?
j
Il giorno giovedì 8 gennaio 2015 15:17:20 UTC+1, Michael Bayer ha scritto:

 use server_default with text() as documented at 
 http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html#server-side-defaults.
  




 jo jose@sferacarta.com javascript: wrote: 

  Hi all, 
  
  I have a problem with DefautClause. I need to assign a default to a 
 numeric column. 
tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata, 
Column('id', Integer, Sequence('rischio_attivita_seq'), 
 primary_key=True), 
Column('cod_attivita', Unicode(10),nullable=False), 
Column('cod_caratteristica_rischio', Unicode(10), nullable=False), 
Column('tipo_calcolo', Integer), 
Column('algoritmo', Unicode(200)), 
Column('fattore_x', Numeric(4,2), DefaultClause(1.0)), 
ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']), 
ForeignKeyConstraint(['cod_caratteristica_rischio'], 
 ['caratteristica_rischio.codice']) 
) 
  
  
  I tried to assign a float to it and I got this error: 
  
  File 
 /home/sfera/sicer2/buildout/eggs/SQLAlchemy-0.9.3-py2.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py,
  
 line 999, in assert_arg_type 
(name, ' or '.join('%s' % a for a in argtype), type(arg))) 
  sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of 
 type 'type 'basestring'' or 'class 
 'sqlalchemy.sql.elements.ClauseElement'' or 'class 
 'sqlalchemy.sql.elements.TextClause'', got 'type 'float'' 
  [sfera@tstsferagamma01 release]$ vi sicer/BASE/model/sql.py 
  
  then I changed it to a string as in: 
Column('fattore_x', Numeric(4,2), DefaultClause('1.0')), 
  and now I have this: 
  
  sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01722: invalid number 
  \nCREATE TABLE attivita (\n\tid INTEGER NOT NULL, \n\tcod_attivita 
 NVARCHAR2(10) NOT NULL, \n\tcod_caratteristica_rischio NVARCHAR2(10) NOT 
 NULL, \n\ttipo_calcolo INTEGER, \n\talgoritmo NVARCHAR2(200), \n\tfattore_x 
 NUMERIC(4, 2) DEFAULT '1.0', \n\tPRIMARY KEY (id), \n\tFOREIGN 
 KEY(cod_attivita) REFERENCES attivita (codice), \n\tFOREIGN 
 KEY(cod_caratteristica_rischio) REFERENCES caratteristica_rischio 
 (codice)\n)\n\n {} 
  
  
  
  Is there another way to define a default clause? 
  thanks. 
  
  j 
  
  -- 
  You received this message because you are subscribed to the Google 
 Groups sqlalchemy group. 
  To unsubscribe from this group and stop receiving emails from it, send 
 an email to sqlalchemy+...@googlegroups.com javascript:. 
  To post to this group, send email to sqlal...@googlegroups.com 
 javascript:. 
  Visit this group at http://groups.google.com/group/sqlalchemy. 
  For more options, visit https://groups.google.com/d/optout. 


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] DefaultClause

[jo]

Hi all,

I have a problem with DefautClause. I need to assign a default to a 
numeric column.

   tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata,
   Column('id', Integer, Sequence('rischio_attivita_seq'), 
primary_key=True),

   Column('cod_attivita', Unicode(10),nullable=False),
   Column('cod_caratteristica_rischio', Unicode(10), nullable=False),
   Column('tipo_calcolo', Integer),
   Column('algoritmo', Unicode(200)),
   Column('fattore_x', Numeric(4,2), DefaultClause(1.0)),
   ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']),
   ForeignKeyConstraint(['cod_caratteristica_rischio'], 
['caratteristica_rischio.codice'])

   )


I tried to assign a float to it and I got this error:

 File 
/home/sfera/sicer2/buildout/eggs/SQLAlchemy-0.9.3-py2.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py, 
line 999, in assert_arg_type

   (name, ' or '.join('%s' % a for a in argtype), type(arg)))
sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of 
type 'type 'basestring'' or 'class 
'sqlalchemy.sql.elements.ClauseElement'' or 'class 
'sqlalchemy.sql.elements.TextClause'', got 'type 'float''

[sfera@tstsferagamma01 release]$ vi sicer/BASE/model/sql.py

then I changed it to a string as in:
   Column('fattore_x', Numeric(4,2), DefaultClause('1.0')),

and now I have this:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01722: invalid number
\nCREATE TABLE attivita (\n\tid INTEGER NOT NULL, \n\tcod_attivita 
NVARCHAR2(10) NOT NULL, \n\tcod_caratteristica_rischio NVARCHAR2(10) NOT 
NULL, \n\ttipo_calcolo INTEGER, \n\talgoritmo NVARCHAR2(200), 
\n\tfattore_x NUMERIC(4, 2) DEFAULT '1.0', \n\tPRIMARY KEY (id), 
\n\tFOREIGN KEY(cod_attivita) REFERENCES attivita (codice), \n\tFOREIGN 
KEY(cod_caratteristica_rischio) REFERENCES caratteristica_rischio 
(codice)\n)\n\n {}




Is there another way to define a default clause?
thanks.

j

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: can't adapt type 'KeyedTuple'

[jo]

Yes, it works, now!
Thanks a lot, Jonathan

j

Il giorno giovedì 20 novembre 2014 19:35:03 UTC+1, Jonathan Vanasco ha 
scritto:

 SqlAlchemy returns column results as a `KeyedTuple`.  It's similar to 
 `collections.namedtuple` in the standard library
  
 Looking at your code, my guess is that you had something like this:

 ids = session.query(Model.id).filter(...).all()

 and are then passing those ids into a query like this:

 objects = session.query(Model).filter(Model.id.in_(ids)).all()

 if that's the case, your code needs to look like this:

 # sqlalchemy returns a tuple on column queries, even for 1 item
 results = session.query(Model.id).filter(...).all()
 # grab the first element of each tuple
 ids = [i[0] for i in results]
 # now you have Integers to work with
 objects = session.query(Model).filter(Model.id.in_(ids)).all()




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] can't adapt type 'KeyedTuple'

[jo]

I all,

I got a KeyedTuple error that I don't know what it means or how to solve it.
Could anyone give me some help?
Thanks.
 
I'm using SQLAlchemy-0.9.3-py2.6.egg

ProgrammingError: (ProgrammingError) can't adapt type 'KeyedTuple' 'SELECT 
unita_aziendale.id AS unita_aziendale_id, unita_aziendale.id_azienda AS 
unita_aziendale_id_azienda, unita_aziendale.id_anagrafica AS 
unita_aziendale_id_anagrafica, unita_aziendale.codice_struttura AS 
unita_aziendale_codice_struttura, unita_aziendale.bdn_id AS 
unita_aziendale_bdn_id, unita_aziendale.bdn_data_ultima_modifica AS 
unita_aziendale_bdn_data_ultima_modifica, unita_aziendale.ts_ultima_modifica AS 
unita_aziendale_ts_ultima_modifica, unita_aziendale.id_operatore AS 
unita_aziendale_id_operatore, unita_aziendale.bdn_specie AS 
unita_aziendale_bdn_specie, unita_aziendale.bdn_flag_carne_latte AS 
unita_aziendale_bdn_flag_carne_latte, unita_aziendale.data_inizio AS 
unita_aziendale_data_inizio, unita_aziendale.data_aggiornamento_izs AS 
unita_aziendale_data_aggiornamento_izs, unita_aziendale.codice_regionale AS 
unita_aziendale_codice_regionale, unita_aziendale.data_fine AS 
unita_aziendale_data_fine, unita
 _aziendale.cod_tipologia_struttura AS unita_aziendale_cod_tipologia_struttura, 
unita_aziendale.cod_specie AS unita_aziendale_cod_specie, 
unita_aziendale.id_veterinario_associato AS 
unita_aziendale_id_veterinario_associato, unita_aziendale.num_iscrizione_cciaa 
AS unita_aziendale_num_iscrizione_cciaa, unita_aziendale.num_registro_imprese 
AS unita_aziendale_num_registro_imprese, unita_aziendale.data_iscrizione AS 
unita_aziendale_data_iscrizione, unita_aziendale.ua_dipartimento AS 
unita_aziendale_ua_dipartimento, anagrafica_1.id AS anagrafica_1_id, 
anagrafica_1.nome AS anagrafica_1_nome, anagrafica_1.indirizzo AS 
anagrafica_1_indirizzo, anagrafica_1.telefono AS anagrafica_1_telefono, 
anagrafica_1.fax AS anagrafica_1_fax, anagrafica_1.email AS anagrafica_1_email, 
anagrafica_1.id_comune AS anagrafica_1_id_comune, anagrafica_1.localita AS 
anagrafica_1_localita, anagrafica_1.cap AS anagrafica_1_cap, 
anagrafica_1.dato_fiscale AS anagrafica_1_dato_fiscale, 
anagrafica_1.cod_professione
  AS anagrafica_1_cod_professione, anagrafica_1.cod_cittadinanza AS 
anagrafica_1_cod_cittadinanza, anagrafica_1.cod_titolo_studio AS 
anagrafica_1_cod_titolo_studio, anagrafica_1.data_nascita AS 
anagrafica_1_data_nascita, anagrafica_1.id_comune_nascita AS 
anagrafica_1_id_comune_nascita, anagrafica_1.cod_nazione_nascita AS 
anagrafica_1_cod_nazione_nascita, anagrafica_1.ts_ultima_modifica AS 
anagrafica_1_ts_ultima_modifica, anagrafica_1.id_operatore AS 
anagrafica_1_id_operatore, anagrafica_1.tipo AS anagrafica_1_tipo, 
anagrafica_1.tipo_persona AS anagrafica_1_tipo_persona, anagrafica_1.data_fine 
AS anagrafica_1_data_fine \nFROM azienda AS azienda_1, unita_aziendale LEFT 
OUTER JOIN anagrafica AS anagrafica_1 ON anagrafica_1.id = 
unita_aziendale.id_anagrafica \nWHERE unita_aziendale.id_azienda = azienda_1.id 
AND unita_aziendale.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s, 
%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s, %(id_11)s, %(id_12)s, 
%(id_13)s, %(id_14)s, %(i
 d_15)s, %(id_16)s, %(id_17)s, %(id_18)s, %(id_19)s, %(id_20)s, %(id_21)s, 
%(id_22)s, %(id_23)s, %(id_24)s, %(id_25)s, %(id_26)s, %(id_27)s, %(id_28)s, 
%(id_29)s, %(id_30)s, %(id_31)s, %(id_32)s, %(id_33)s, %(id_34)s, %(id_35)s, 
%(id_36)s, %(id_37)s, %(id_38)s, %(id_39)s, %(id_40)s, %(id_41)s, %(id_42)s, 
%(id_43)s, %(id_44)s, %(id_45)s, %(id_46)s, %(id_47)s, %(id_48)s, %(id_49)s, 
%(id_50)s, %(id_51)s, %(id_52)s, %(id_53)s, %(id_54)s, %(id_55)s, %(id_56)s, 
%(id_57)s, %(id_58)s, %(id_59)s, %(id_60)s, %(id_61)s, %(id_62)s, %(id_63)s, 
%(id_64)s, %(id_65)s, %(id_66)s, %(id_67)s, %(id_68)s, %(id_69)s, %(id_70)s, 
%(id_71)s, %(id_72)s, %(id_73)s, %(id_74)s, %(id_75)s, %(id_76)s, %(id_77)s, 
%(id_78)s, %(id_79)s, %(id_80)s, %(id_81)s, %(id_82)s, %(id_83)s, %(id_84)s, 
%(id_85)s, %(id_86)s, %(id_87)s, %(id_88)s, %(id_89)s, %(id_90)s, %(id_91)s, 
%(id_92)s, %(id_93)s, %(id_94)s, %(id_95)s, %(id_96)s, %(id_97)s, %(id_98)s, 
%(id_99)s, %(id_100)s, %(id_101)s, %(id_102)s, %(id_103)s, %(id_104)
 s, %(id_105)s, %(id_106)s, %(id_107)s, %(id_108)s, %(id_109)s, %(id_110)s, 
%(id_111)s, %(id_112)s, %(id_113)s, %(id_114)s, %(id_115)s, %(id_116)s, 
%(id_117)s, %(id_118)s, %(id_119)s, %(id_120)s, %(id_121)s, %(id_122)s, 
%(id_123)s, %(id_124)s, %(id_125)s, %(id_126)s, %(id_127)s, %(id_128)s, 
%(id_129)s, %(id_130)s, %(id_131)s, %(id_132)s, %(id_133)s, %(id_134)s, 
%(id_135)s, %(id_136)s, %(id_137)s, %(id_138)s, %(id_139)s, %(id_140)s, 
%(id_141)s, %(id_142)s, %(id_143)s, %(id_144)s, %(id_145)s, %(id_146)s, 
%(id_147)s, %(id_148)s, %(id_149)s, %(id_150)s, %(id_151)s, %(id_152)s, 
%(id_153)s, %(id_154)s, %(id_155)s, %(id_156)s, %(id_157)s, %(id_158)s, 
%(id_159)s, %(id_160)s, %(id_161)s, %(id_162)s, %(id_163)s, %(id_164)s, 
%(id_165)s, %(id_166)s, %(id_167)s, %(id_168)s, %(id_169)s, %(id_170)s, 

Re: [sqlalchemy] Re: UNION howto

[jo]

It works. Thanks a lot, Jonathan.
j
Jonathan Vanasco wrote:
I asked a similar question a few days ago; it's still on the front 
page of the group.  Mike replied to that and gave some details


Your query would be something like:




class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
cod_sticker = Column(String)
sticker_checklist = Column(String)
protocollo = Column(String)

q1 = sess.query(A.cod_sticker.label('bruciato'))
q2 = sess.query(A.cod_sticker.label('bruciato'))
q3 = sess.query(A.sticker_checklist.label('bruciato'))
q4 = sess.query(A.protocollo.label('bruciato'))

as_union = union(q1, q2, q3, q4)
as_union_alias = as_union.alias('foo')

q = sess.query(as_union_alias).filter( as_union_alias.c.bruciato 
== 'E1212').count()

--
You received this message because you are subscribed to the Google 
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to sqlalchemy+unsubscr...@googlegroups.com 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto:sqlalchemy@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.



--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69   _/_/_/_/_/
40033 Casalecchio di Reno _/_/  _/_/  _/_/
Bologna - Italy  _/_/  _/_/  _/_/
Ph  +39051591054  _/_/  _/_/  _/_/  _/_/
fax +390516131537_/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com_/_/_/  _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] sqlite and max_overflow

[jo]

Thanks for the tip, Michael, I'm going to see how to manage with this 
keyword (because I'm not specify such parameter, but probable is 
turbogears who uses it by default).


j



Michael Bayer wrote:
you can't use max_overflow with the default connection pool used by 
the SQLite engine, remove that keyword.



On May 27, 2013, at 7:17 AM, jo jose.soa...@sferacarta.com 
mailto:jose.soa...@sferacarta.com wrote:



I have not been able to solve the problem
can someone please help me?

...
database.bind_meta_data()
File 
/home/jose/buildout/eggs/TurboGears-1.1.3-py2.6.egg/turbogears/database.py, 
line 73, in bind_metadata

metadata.bind = sqlalchemy.create_engine(dburi, **alch_args)
File 
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/__init__.py, 
line 263, in create_engine

return strategy.create(*args, **kwargs)
File 
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/strategies.py, 
line 134, in create

engineclass.__name__))
TypeError: Invalid argument(s) 'max_overflow' sent to 
create_engine(), using configuration 
SQLiteDialect_pysqlite/SingletonThreadPool/Engine. Please check that 
the keyword arguments are appropriate for this combination of components.


j


--
You received this message because you are subscribed to the Google 
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, 
send an email to sqlalchemy+unsubscr...@googlegroups.com 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto:sqlalchemy@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google 
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to sqlalchemy+unsubscr...@googlegroups.com.

To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69   _/_/_/_/_/
40033 Casalecchio di Reno _/_/  _/_/  _/_/
Bologna - Italy  _/_/  _/_/  _/_/
Ph  +39051591054  _/_/  _/_/  _/_/  _/_/
fax +390516131537_/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com_/_/_/  _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] sqlite and max_overflow

[jo]

Yes, you are right, Simon,
I found it in app.cfg as:

sqlalchemy.max_overflow=40 # Bumped up from 10
sqlalchemy.pool_size=10# default


thanks a lot.
j


Simon King wrote:

I think turbogears is loading your sqlalchemy parameters from a config
file (dev.cfg perhaps?). You should look for a line of the form
sqlalchemy.max_overflow = X and delete it.

Hope that helps,

Simon

On Tue, May 28, 2013 at 10:34 AM, jo jose.soa...@sferacarta.com wrote:
  

Thanks for the tip, Michael, I'm going to see how to manage with this
keyword (because I'm not specify such parameter, but probable is turbogears
who uses it by default).

j



Michael Bayer wrote:


you can't use max_overflow with the default connection pool used by the
SQLite engine, remove that keyword.


On May 27, 2013, at 7:17 AM, jo jose.soa...@sferacarta.com
mailto:jose.soa...@sferacarta.com wrote:

  

I have not been able to solve the problem
can someone please help me?

...
database.bind_meta_data()
File
/home/jose/buildout/eggs/TurboGears-1.1.3-py2.6.egg/turbogears/database.py,
line 73, in bind_metadata
metadata.bind = sqlalchemy.create_engine(dburi, **alch_args)
File
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/__init__.py,
line 263, in create_engine
return strategy.create(*args, **kwargs)
File
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/strategies.py,
line 134, in create
engineclass.__name__))
TypeError: Invalid argument(s) 'max_overflow' sent to create_engine(),
using configuration SQLiteDialect_pysqlite/SingletonThreadPool/Engine.
Please check that the keyword arguments are appropriate for this combination
of components.

j


--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.


  

--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69   _/_/_/_/_/
40033 Casalecchio di Reno _/_/  _/_/  _/_/
Bologna - Italy  _/_/  _/_/  _/_/
Ph  +39051591054  _/_/  _/_/  _/_/  _/_/
fax +390516131537_/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com_/_/_/  _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file
allegato sono riservate e, comunque, destinate esclusivamente alla persona o
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196.
La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
l’integrità e la sicurezza della presente mail non possono essere garantite.
Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to
legislative decree 30 June 2003, n. 196. It may contain confidential or
privileged information. You should not copy or use it to disclose its
contents to any other person. Transmission cannot be guaranteed to be
error-free, complete and secure. If you are not the intended recipient and
receive this communication unintentionally, please inform us immediately and
then delete this message from your system. Thank you.


--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.





  



--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69   _/_/_/_/_/
40033 Casalecchio di Reno _/_/  _/_/  _/_/
Bologna - Italy  _/_/  _/_/  _/_/
Ph  +39051591054  _/_/  _/_/  _/_/  _/_/
fax +390516131537_/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com_/_/_/  _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque

[sqlalchemy] Re: sqlite and max_overflow

[jo]

I have not been able to solve the problem
can someone please help me?

...
database.bind_meta_data()
  File 
/home/jose/buildout/eggs/TurboGears-1.1.3-py2.6.egg/turbogears/database.py, 
line 73, in bind_metadata
metadata.bind = sqlalchemy.create_engine(dburi, **alch_args)
  File 
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/__init__.py,
 
line 263, in create_engine
return strategy.create(*args, **kwargs)
  File 
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/strategies.py,
 
line 134, in create
engineclass.__name__))
TypeError: Invalid argument(s) 'max_overflow' sent to create_engine(), 
using configuration SQLiteDialect_pysqlite/SingletonThreadPool/Engine.  
Please check that the keyword arguments are appropriate for this 
combination of components.

j

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] distinct on

[jo]

|Hi all,

I wondered if it is possible to execute a partial distinct in sqlalchemy.
The following query works in oracle and postgresql:

select distinct col1, 
first_value(col2) over (partition by col1 order by col2 asc)

from tmp;

How can I do such query in sqlalchemy?
Thanks for any help.
j


|

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] sqlite and max_overflow

[jo]

Hi all,

I'm trying to use turbogears and sqlite as:
sqlalchemy.dburi=sqlite://
but I got this error:

TypeError: Invalid argument(s) 'max_overflow' sent to create_engine(), 
using configuration SQLiteDialect_pysqlite/SingletonThreadPool/Engine.  
Please check that the keyword arguments are appropriate for this 
combination of components.


What can I do, to avoid it?
thanks for any help.
j

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] A transaction is already begun. Use subtransactions=True to allow subtransactions.

[jo]

Hi all,

I'm running an web application in turbogears1 using sqlalchemy I'm using mainly postgresql as database 
but in a few installations I'm using oracle (same programs only db change).

Using PostgreSQL all is ok but when I use
oracle some times I get the following error:

 File 
/sferacarta/prod/eggs/TurboGears-1.1.1-py2.6.egg/turbogears/database.py, line 
455, in sa_rwt
   request.sa_transaction = session.begin()
 File 
/sferacarta/prod/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/orm/scoping.py, 
line 139, in do
   return getattr(self.registry(), name)(*args, **kwargs)
 File 
/sferacarta/prod/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/orm/session.py, 
line 550, in begin
   A transaction is already begun.  Use subtransactions=True 
InvalidRequestError: A transaction is already begun.  Use subtransactions=True 
to allow subtransactions.

I'm not using explicity session.begin() because turbogears do that for me,
thus I don't know where to define subtransactions=True.
What can I do?

thanks for any help

j


--
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] maximum number of expressions in a list is 1000

[jo]

_tuple() is fine,
Thanks Michael :-)
j

Michael Bayer wrote:

On Jan 3, 2013, at 2:40 AM, jo wrote:

  

Hi all,

I need to use in_(), but in oracle it has a limit of 1000 values,
there's an alternative syntax that can be used successful in oracle and it is:
(field,-1) in ( (123,-1), (333,-1), ... )

I tryed this:
session.query(Mytable).filter((Mytable.c.id,-1).in_([(123,-1),(333,-1)]) )

AttributeError: 'tuple' object has no attribute 'in_'

How can I use this syntax with sa?




I usually handle the Oracle 1000 value limit by running the same query multiple 
times, then merging in memory.

But if you want to do (a, b) IN ((x1, y1), (x2, y2), ...) there's a construct 
called tuple_() that should do it:

  

from sqlalchemy import tuple_
print tuple_(mytable.c.id, -1).in_([tuple_(1, -1), tuple_(2, -1)])


(t.x, :param_1) IN ((:param_2, :param_3), (:param_4, :param_5))


  



--
Jose Soares Da Silva _/_/
Sferacarta Net
Via Bazzanese 69   _/_/_/_/_/
40033 Casalecchio di Reno _/_/  _/_/  _/_/
Bologna - Italy  _/_/  _/_/  _/_/
Ph  +39051591054  _/_/  _/_/  _/_/  _/_/
fax +390516131537_/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com_/_/_/  _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] maximum number of expressions in a list is 1000

[jo]

Hi all,

I need to use in_(), but in oracle it has a limit of 1000 values,
there's an alternative syntax that can be used successful in oracle and 
it is:

(field,-1) in ( (123,-1), (333,-1), ... )

I tryed this:
session.query(Mytable).filter((Mytable.c.id,-1).in_([(123,-1),(333,-1)]) )

AttributeError: 'tuple' object has no attribute 'in_'

How can I use this syntax with sa?
j

--
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] NotSupportedError

[jo]

Hi all,

Someone could help me with this query?

SELECT MAX(CAST(SUBSTR(codice_aziendale, 2) AS INTEGER)) FROM azienda 
WHERE codice_aziendale LIKE '#%%' AND 
REGEXP_LIKE(SUBSTR(codice_aziendale, 2) , '[[:digit:]]')



I'm trying in this way:

session.query(sa.func.max(sa.func.cast(sa.func.substr(Azienda.c.codice_aziendale,2), 
sa.Integer))).filter(sa.func.REGEXP_LIKE(Azienda.c.data_inizio, 
'[[:digit:]]')).scalar()


but I got this error:

NotSupportedError: (NotSupportedError) Variable_TypeByValue(): unhandled 
data type Integer 'SELECT max(cast(substr(azienda.codice_aziendale, 
:substr_1), :cast_1)) AS max_1 \nFROM azienda \nWHERE 
REGEXP_LIKE(azienda.data_inizio, :REGEXP_LIKE_1)' {'REGEXP_LIKE_1': 
'[[:digit:]]', 'substr_1': 2, 'cast_1': Integer()}



j

--
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] NotSupportedError

[jo]

I'm using oracle. The error exists even without REGEXP_LIKE:

session.query(sa.func.max(sa.func.cast(sa.func.substr(Azienda.c.codice_aziendale,2), 
sa.Integer))).scalar()


NotSupportedError: (NotSupportedError) Variable_TypeByValue(): unhandled 
data type Integer 'SELECT max(cast(substr(azienda.codice_aziendale, 
:substr_1), :cast_1)) AS max_1 \nFROM azienda' {'substr_1': 2, 'cast_1': 
Integer()}


-

the error is on func.cast, if I remove the cast function, it works:

session.query(sa.func.max(sa.func.substr(Azienda.c.codice_aziendale,2))).filter(sa.func.REGEXP_LIKE(Azienda.c.data_inizio, 
'[[:digit:]]')).scalar()


SELECT max(substr(azienda.codice_aziendale, :substr_1)) AS max_1
FROM azienda
WHERE REGEXP_LIKE(azienda.data_inizio, :REGEXP_LIKE_1)

{'REGEXP_LIKE_1': '[[:digit:]]', 'substr_1': 2}
Out[12]: u'_999'


Michael Bayer wrote:

we would need to know what database you're using.   It's likely your DB doesn't 
support REGEXP_LIKE.


On Apr 18, 2012, at 4:00 AM, jo wrote:

  

Hi all,

Someone could help me with this query?

SELECT MAX(CAST(SUBSTR(codice_aziendale, 2) AS INTEGER)) FROM azienda WHERE 
codice_aziendale LIKE '#%%' AND REGEXP_LIKE(SUBSTR(codice_aziendale, 2) , 
'[[:digit:]]')


I'm trying in this way:

session.query(sa.func.max(sa.func.cast(sa.func.substr(Azienda.c.codice_aziendale,2),
 sa.Integer))).filter(sa.func.REGEXP_LIKE(Azienda.c.data_inizio, 
'[[:digit:]]')).scalar()

but I got this error:

NotSupportedError: (NotSupportedError) Variable_TypeByValue(): unhandled data 
type Integer 'SELECT max(cast(substr(azienda.codice_aziendale, :substr_1), 
:cast_1)) AS max_1 \nFROM azienda \nWHERE REGEXP_LIKE(azienda.data_inizio, 
:REGEXP_LIKE_1)' {'REGEXP_LIKE_1': '[[:digit:]]', 'substr_1': 2, 'cast_1': 
Integer()}


j

--
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] query vs select

[jo]

Michael Bayer wrote:

there's nothing particularly advanced in that query and either system can 
accomplish it in a straightforward way.

What's the part of it that isn't clear ?   Here you'd be using funx.max(), join(), 
x != None, group_by() and order_by().


  

II did it in this way:

session.query(sa.func.max(Sopralluogo.c.data_sopralluogo),
  ScadenzaMalattia.c.cod_malattia,
  ScadenzaMalattia.c.id_unita_aziendale).select_from(
  ScadenzaMalattia).join(
  Scadenziario).join(
  Sopralluogo).group_by(
  
ScadenzaMalattia.c.id_unita_aziendale,ScadenzaMalattia.c.cod_malattia).order_by(
  
ScadenzaMalattia.c.id_unita_aziendale,ScadenzaMalattia.c.cod_malattia).filter(

  sa.and_(ScadenzaMalattia.c.cod_malattia!=None,
  Sopralluogo.c.data_sopralluogo  '2011-01-01'),
  ScadenzaMalattia.c.id_unita_aziendale == 2,
  ScadenzaMalattia.c.cod_malattia == '012'))



On Apr 13, 2012, at 1:59 AM, jo wrote:

  

Thanks Michael, your explanation is comprehensive, currently I'm using both of 
them,
but I feared that one of them could become obsolete in the future.
I'm trying to translate some queries that I had done with engine
but I find it hard to do.
For example, a query like this one, I can not set it with query neither with 
select,
what do you suggest?

SELECT max(sopralluogo.data_sopralluogo),
scadenza_malattia.cod_malattia,
scadenza_malattia.id_unita_aziendale
FROM scadenza_malattia
JOIN scadenziario ON scadenza_malattia.id_scadenziario = scadenziario.id
JOIN sopralluogo ON scadenziario.id_sopralluogo = sopralluogo.id
WHERE scadenza_malattia.cod_malattia IS NOT NULL AND
sopralluogo.data_sopralluogo  '2011-01-01' AND
scadenza_malattia.id_unita_aziendale = 2 AND
scadenza_malattia.cod_malattia = '012'
GROUP BY scadenza_malattia.id_unita_aziendale,
scadenza_malattia.cod_malattia
ORDER BY scadenza_malattia.id_unita_aziendale,
scadenza_malattia.cod_malattia



Michael Bayer wrote:


There's some degree of history here as SQLAlchemy initially didn't have the whole generative notion 
of things, and the Mapper object itself would accept arguments which it passed mostly straight to a select() 
object.   So you saw similar interfaces and it was kind of like switching between table.select(all the 
arguments) and mapper.select(all the arguments).   There was a great emphasis on using regular 
select() constructs with mappers, and queries were always done in terms of Table objects, not classes - the 
idea of MyClass.foo=='bar' was introduced much later, even though this Table access was somewhat convenient via 
the .c. attribute on classes (so MyClass.c.foo == 'bar' - this is actually completely different 
than what MyClass.foo is today).   Overall there was a lot of SQLObject influencing how things were done.

If I were doing this again today, I might try to have a select() object that 
somehow morphs more smoothly into an ORM-centric object - though this would be 
challenging as the ORM Query does a lot of things with it's state before 
generating a much more rudimentary select() construct.  The Query has a lot of 
opinions about things that the select() does not, even though our Query is 
still much closer to SQL than that of any other ORM.   I have thought recently 
about this subject.   But each time I try to consider there being just one 
construct that can move between them, the details of how that would work become 
apparently very hazy and unclear - it would take a lot of thinking.   Still 
could be worth it, though.

So as things turned out in 0.5, 0.6 and onwards, we've made the Query object be the main thing you work with when using the ORM.   There's very little emphasis on using a select() construct directly except when embedding a subquery into an existing Query - but even then we have you build up the select() using the Query interface.  The select() and Query still play together pretty well but we try to say when you're actually building select() constructs directly, you're working in a schema-centric fashion as opposed to a domain-centric fashion.If you're considering your query in terms of tables, and you want plain tuples back, that leans towards select([]), and if you're querying in terms of the object model, you use Query and can get back any combination of objects/tuples.
I know it's not 100% pure but it does seem to work out pretty well.






On Apr 12, 2012, at 2:06 PM, jo wrote:

 
  

Hi all,

I'm sorry for this simple question.
What's the difference between query and select ?
are they interchangeable?
which of the two, it is best to use?

--- print(session.query(Azienda.c.data_inizio).limit(1))
SELECT azienda_data_inizio
FROM (SELECT azienda.data_inizio AS azienda_data_inizio
FROM azienda)
WHERE ROWNUM = :ROWNUM_1


--- print(select([Azienda.c.data_inizio]).limit(1))
SELECT data_inizio
FROM (SELECT azienda.data_inizio AS data_inizio
FROM

[sqlalchemy] query vs select

[jo]

Hi all,

I'm sorry for this simple question.
What's the difference between query and select ?
are they interchangeable?
which of the two, it is best to use?

--- print(session.query(Azienda.c.data_inizio).limit(1))
SELECT azienda_data_inizio
FROM (SELECT azienda.data_inizio AS azienda_data_inizio
FROM azienda)
WHERE ROWNUM = :ROWNUM_1


--- print(select([Azienda.c.data_inizio]).limit(1))
SELECT data_inizio
FROM (SELECT azienda.data_inizio AS data_inizio
FROM azienda)
WHERE ROWNUM = :ROWNUM_1


j

--
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] query vs select

[jo]

Thanks Michael, your explanation is comprehensive, currently I'm using 
both of them,

but I feared that one of them could become obsolete in the future.
I'm trying to translate some queries that I had done with engine
but I find it hard to do.
For example, a query like this one, I can not set it with query neither 
with select,

what do you suggest?

SELECT max(sopralluogo.data_sopralluogo),
scadenza_malattia.cod_malattia,
scadenza_malattia.id_unita_aziendale
FROM scadenza_malattia
JOIN scadenziario ON scadenza_malattia.id_scadenziario = scadenziario.id
JOIN sopralluogo ON scadenziario.id_sopralluogo = sopralluogo.id
WHERE scadenza_malattia.cod_malattia IS NOT NULL AND
sopralluogo.data_sopralluogo  '2011-01-01' AND
scadenza_malattia.id_unita_aziendale = 2 AND
scadenza_malattia.cod_malattia = '012'
GROUP BY scadenza_malattia.id_unita_aziendale,
scadenza_malattia.cod_malattia
ORDER BY scadenza_malattia.id_unita_aziendale,
scadenza_malattia.cod_malattia



Michael Bayer wrote:

There's some degree of history here as SQLAlchemy initially didn't have the whole generative notion 
of things, and the Mapper object itself would accept arguments which it passed mostly straight to a select() 
object.   So you saw similar interfaces and it was kind of like switching between table.select(all the 
arguments) and mapper.select(all the arguments).   There was a great emphasis on using regular 
select() constructs with mappers, and queries were always done in terms of Table objects, not classes - the 
idea of MyClass.foo=='bar' was introduced much later, even though this Table access was somewhat convenient via 
the .c. attribute on classes (so MyClass.c.foo == 'bar' - this is actually completely different 
than what MyClass.foo is today).   Overall there was a lot of SQLObject influencing how things were done.

If I were doing this again today, I might try to have a select() object that 
somehow morphs more smoothly into an ORM-centric object - though this would be 
challenging as the ORM Query does a lot of things with it's state before 
generating a much more rudimentary select() construct.  The Query has a lot of 
opinions about things that the select() does not, even though our Query is 
still much closer to SQL than that of any other ORM.   I have thought recently 
about this subject.   But each time I try to consider there being just one 
construct that can move between them, the details of how that would work become 
apparently very hazy and unclear - it would take a lot of thinking.   Still 
could be worth it, though.

So as things turned out in 0.5, 0.6 and onwards, we've made the Query object be the main thing you work with when using the ORM.   There's very little emphasis on using a select() construct directly except when embedding a subquery into an existing Query - but even then we have you build up the select() using the Query interface.  The select() and Query still play together pretty well but we try to say when you're actually building select() constructs directly, you're working in a schema-centric fashion as opposed to a domain-centric fashion.If you're considering your query in terms of tables, and you want plain tuples back, that leans towards select([]), and if you're querying in terms of the object model, you use Query and can get back any combination of objects/tuples.


I know it's not 100% pure but it does seem to work out pretty well.





On Apr 12, 2012, at 2:06 PM, jo wrote:

  

Hi all,

I'm sorry for this simple question.
What's the difference between query and select ?
are they interchangeable?
which of the two, it is best to use?

--- print(session.query(Azienda.c.data_inizio).limit(1))
SELECT azienda_data_inizio
FROM (SELECT azienda.data_inizio AS azienda_data_inizio
FROM azienda)
WHERE ROWNUM = :ROWNUM_1


--- print(select([Azienda.c.data_inizio]).limit(1))
SELECT data_inizio
FROM (SELECT azienda.data_inizio AS data_inizio
FROM azienda)
WHERE ROWNUM = :ROWNUM_1


j

--
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.




  



--
Jose Soares  _/_/  
Sferacarta Net  
Via Bazzanese 69   _/_/ _/_/_/

40033 Casalecchio di Reno _/_/   _/_/  _/_/
Bologna - Italy  _/_/   _/_/  _/_/
Ph  +39051591054  _/_/  _/_/   _/_/  _/_/
fax +390516131537_/_/  _/_/   _/_/  _/_/
web:www.sferacarta.com_/_/_/   _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione

Re: [sqlalchemy] After insert/update events

[jo]

This is the way I solved the problem... (how to backup a table row into 
another table before delete or update it) ...if it can help:


from sqlalchemy.orm import MapperExtension
class History(MapperExtension):
def __init__(self):
MapperExtension.__init__(self)
self.methods = ('before_update','before_delete')

def clone(self, mapper, connection, instance, action):
tablename=mapper.mapped_table.name
tablename_bak=tablename+'_bak'
rec = connection.execute(select([tbl[tablename]], tbl[tablename].c.id == 
getattr( instance, '%s_id'%tablename))).fetchone()

dd=dict()
modified=False
for k,v in rec.items():
dd[ k.lower() ] = v
if action=='D': #if delete request...
connection.execute(tbl[tablename_bak].insert(values=dd))
elif action=='U': #if update request...
for k in instance.c: #check for differences, to save it only if it was 
modified...

if getattr( instance,str(k).replace('.','_')) != dd[str(k).split('.')[1]]:
modified=True
break
if modified is True:
connection.execute(tbl[tablename_bak].insert(values=dd))
return

def before_update(self, mapper, connection, instance):
return self.clone(mapper, connection, instance, 'U')

def before_delete(self, mapper, connection, instance):
return self.clone(mapper, connection, instance, 'D')


I call it in this way:

from ... import History
mapper(Anagrafica,
tbl['anagrafica'],
column_prefix = 'anagrafica_',
extension=History(),
)


Paddy Mullen wrote:
I have been trying to create a nice decorator for tasks that are 
methods of models. I want the tasks to run after specific conditions 
(after_update/insert, with predicates).


I was able to set this up through a series of event hooks starting 
with mapper_configured.


The problem I have run into is, listening for after_insert on a 
subclass extending DeclarativeBase, only results in calls after insert 
has been called for, but not after it has been executed. I can listen 
on the engine to after_execute, which does seem to give a callback 
after commit/flush has actually been called, but at this I have no 
declarativeBase references to the objects that have been inserted, 
only to the raw sql.


Am I missing something?

Here are the example files
https://github.com/paddymul/sqlalchemy_garden/blob/master/lib/deferred_task.py 
- the library


https://github.com/paddymul/sqlalchemy_garden/blob/master/schemas/deferred_schema.py 
- an example usage


Thanks,
Paddy
--
You received this message because you are subscribed to the Google 
Groups sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/IpLW9LroG6IJ.

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.



--
Jose Soares  _/_/  
Sferacarta Net  
Via Bazzanese 69   _/_/ _/_/_/

40033 Casalecchio di Reno _/_/   _/_/  _/_/
Bologna - Italy  _/_/   _/_/  _/_/
Ph  +39051591054  _/_/  _/_/   _/_/  _/_/
fax +390516131537_/_/  _/_/   _/_/  _/_/
web:www.sferacarta.com_/_/_/   _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] datetime and engine

[jo]

Hi all,

I found a difference between Oracle and PostgreSQL about datetime objects.
Using the engine Oracle returns every date column as datetime.datetime
while PostgreSQL returns it as datetime.date

I have a table with a column data_inizio defined as DATE in table 
tariffa in my db.


take a look:


In [5]: engine.connect().execute('select data_inizio from tariffa where 
id=391').fetchone()

Out[5]: (datetime.datetime(2009, 1, 1, 0, 0),)

In [6]: type(Tariffa.get(391).tariffa_data_inizio)
Out[6]: type 'datetime.date'



In [1]: engine.connect().execute('select data_inizio from tariffa where 
id=391').fetchone()

Out[1]: (datetime.date(2009, 1, 1),)

In [2]: type(Tariffa.get(391).tariffa_data_inizio)
Out[2]: type 'datetime.date'


Why this difference? Is there a way to change Oracle behavior in engine?

thanks for any help.

j

--
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] datetime and engine

[jo]

shinriyo wrote:

hi jo

Oracle and PostgreSQL are different.
Oracle also have hour and minutes and second.

If you want minutes and second on PostgresQL, you should use datetime.

  


Hi shinryo,

I don't want hours and minutes.
My problem is that I have a comparison in my code like this:

if   data_inizio  data_fine:
...

TypeError: can't compare datetime.datetime to datetime.date

--

data_inizio  is a value returned by:
 Frazione.get(31).tariffa_data_inizio   # returns an object 
datetime.date (both oracle and pg)


data_fine is a value returned by engine: #oracle returns a datetime 
while pg returns a date
   engine.connect().execute('select data_fine from tariffa where 
id_frazione=31').fetchone() 




I would like to know why Oracle engine returns dates as 
datetime.datetime instead of datetime.date
and if there is a way to have the same behavior between oracle and pg in 
such case.


j

--
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] sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables

[jo]

Hi all,
I don't know how to fix this error in my model:

sqlalchemy.exc.ArgumentError: Could not determine join condition between 
parent/child tables on relationship RuoloPermesso.permessi.  Specify a 
'primaryjoin' expression.  If 'secondary' is present, 'secondaryjoin' is 
needed as well.



tbl['ruolo'] = Table('ruolo', database.metadata,
Column('codice', Unicode(10), nullable=False, primary_key=True),
Column('descrizione', Unicode(200), nullable=False),
)

   tbl['permesso'] = Table('permesso', database.metadata,
   Column('id', Integer, Sequence('permesso_seq'),  
primary_key=True, nullable=False),
   Column('cod_menu', Unicode(10), ForeignKey('menu.codice'), 
nullable=False),

   Column('sub_menu', Unicode(10)),
   Column('codice', Unicode(200), nullable=False),
   Column('descrizione', Unicode(200), nullable=False),
   Column('path', Unicode(200), nullable=False),
   )

   tbl['ruolo_permesso'] = Table('ruolo_permesso', database.metadata,
   Column('cod_ruolo', Unicode(10), primary_key=True),
   Column('cod_permesso', Unicode(200), primary_key=True),
   Column('lettura', Boolean),
   Column('modifica', Boolean),
   Column('inserimento', Boolean),
   Column('cancellazione', Boolean),
   ForeignKeyConstraint(['cod_ruolo'], ['ruolo.codice']),
   ForeignKeyConstraint(['cod_permesso'], ['permesso.codice'], 
ondelete='CASCADE')

   )

mapper(Ruolo,
  tbl['ruolo'],
  column_prefix = 'ruolo_',
  properties = {
  'permissions' : relation(Permesso, 
secondary=tbl['ruolo_permesso'], backref='groups'),

  }
   )

mapper(Permesso,
  tbl['permesso'],
  column_prefix = 'permesso_',
   )
mapper(RuoloPermesso,
  tbl['ruolo_permesso'],
  column_prefix = 'ruolo_permesso_',
  properties = {
  'permessi' : relation(Permesso),
  }
   )

thanks for any help.
j

--
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] on delete cascade

[jo]

It works!
Thanks a lot, Tate.

j

Tate Kim wrote:

Hi, jo.

At first, try with cascade option 'all, delete'
(note that dropped 'delete-orphan').

Second, I've ever been your situation before, maybe 
-Original Message-

From: jo jose.soa...@sferacarta.com
Sender: sqlalchemy@googlegroups.com
Date: Fri, 24 Feb 2012 11:41:48 
To: sasqlalchemy@googlegroups.com

Reply-To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] on delete cascade

Hi all,

I'm trying to delete cascade a linked row without success.
Could anyone give me some help?


This is my mapper:

mapper(Azienda,
   tbl['azienda'],
   properties = {
   'anagrafica': relation(Anagrafica, cascade='all, delete, 
delete-orphan'),

   })

the table Azienda has a foreign key (not null) linked to table Anagrafica.


When I try to delete a row from Azienda I would like remove also the 
linked row in table Anagrafica... and I supposed it was done by
the instruction on properties:  'anagrafica': relation(Anagrafica, 
cascade='all, delete, delete-orphan')... but it doesn't work...


record = Azienda.get( 867 )
session.delete(record)

IntegrityError: ('(IntegrityError) update or delete on table 
anagrafica violates

foreign key constraint azienda_id_anagrafica_fkey on table azienda
DETAIL: Key (id)=(313836) is still referenced from table azienda.
, bound method Controller.save of 
sicer.BASE.controller.anagraficaAlta.azienda.Controller object at 
0x8fde590)

'DELETE FROM anagrafica WHERE anagrafica.id = %(id)s' {'id': 313836}

Thanks for any help

j

  



--
Jose Soares  _/_/  
Sferacarta Net  
Via Bazzanese 69   _/_/ _/_/_/

40033 Casalecchio di Reno _/_/   _/_/  _/_/
Bologna - Italy  _/_/   _/_/  _/_/
Ph  +39051591054  _/_/  _/_/   _/_/  _/_/
fax +390516131537_/_/  _/_/   _/_/  _/_/
web:www.sferacarta.com_/_/_/   _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] on delete cascade

[jo]

Hi all,

I'm trying to delete cascade a linked row without success.
Could anyone give me some help?


This is my mapper:

mapper(Azienda,
  tbl['azienda'],
  properties = {
  'anagrafica': relation(Anagrafica, cascade='all, delete, 
delete-orphan'),

  })

the table Azienda has a foreign key (not null) linked to table Anagrafica.


When I try to delete a row from Azienda I would like remove also the 
linked row in table Anagrafica... and I supposed it was done by
the instruction on properties:  'anagrafica': relation(Anagrafica, 
cascade='all, delete, delete-orphan')... but it doesn't work...


record = Azienda.get( 867 )
session.delete(record)

IntegrityError: ('(IntegrityError) update or delete on table 
anagrafica violates

foreign key constraint azienda_id_anagrafica_fkey on table azienda
DETAIL: Key (id)=(313836) is still referenced from table azienda.
, bound method Controller.save of 
sicer.BASE.controller.anagraficaAlta.azienda.Controller object at 
0x8fde590)

'DELETE FROM anagrafica WHERE anagrafica.id = %(id)s' {'id': 313836}

Thanks for any help

j

--
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] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

[jo]

In cx_oracle mailing list, they suggested me, this:

... write your own equivalent of makedsn, which really ought not be too hard. 
You'd want to emit something like this:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SERVICE_NAME=your-service-name)))
vs. what makedsn emits, which is stuff like this:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SID=your-dbname)))

I tried to replace 'SID' with 'SERVICE_NAME' in this string returned by 
makedsn like this:


dsn = 
cx_Oracle.makedsn(parms['host'],parms['port'],parms['dbname']).replace('SID','SERVICE_NAME')
and IT WORKS, but I don't know how to apply this to tg+sqlalchemy  in 
the following context:


from turbogears import database
from sqlalchemy.orm import class_mapper
database.bind_meta_data()
engine = database.get_engine()
session = database.session

def mapper(klass, *args, **kw):
   session.mapper(klass, *args, **kw)
   class_mapper(klass).compile()

j


Michael Bayer wrote:

yeah I dunno,   the problem is at the cx_Oracle / OCI level at this point, 
since you can illustrate cx_Oracle/makedsn() not working.  You might need to 
ask on their list at this point (only give them the init_db_conn() scripts, 
don't give them any SQLalchemy stuff):

https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

a workaround for now is you can put your successfully-connecting function to 
create_engine() using the creator argument:

e = create_engine(oracle://, creator=my_connect_function)



On Dec 14, 2011, at 10:49 AM, jose soares wrote:

  

Ok, I changed the file  $ORACLE_HOME/network/admin/tnsnames.ora but it still 
doesn't work.:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None



# tnsnames.ora Network Configuration File: 
/usr/share/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SHELL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))

SHELL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))
  (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SHELL)
  )
)



Michael Bayer wrote:


so makedsn() will give you:

 
  

cx_Oracle.makedsn(oracapsul.net,  1521, SHELL)
   


'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracapsul.net)(PORT=1521)))(CONNECT_DATA=(SID=SHELL)))'


and that should match in your tnsnames.ora file  
($ORACLE_HOME/network/admin/tnsnames.ora).   would have an entry like:

SHELL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsul.net)(PORT = 1521))
   (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = SHELL)
   )
 )


maybe there's a discrepancy between the hostnames in use in the file vs. your 
URL.




On Dec 14, 2011, at 4:50 AM, jose soares wrote:

 
  

I also tried two different connection mode.
The first one works but the second one using makedsn doesn't.


def init_db_conn(parms): #this work
 import cx_Oracle
 dburi=%(user)s/%(password)s@%(host)s:%(port)s/%(sid)s % parms
 return cx_Oracle.connect(dburi)


def init_db_conn(parms): #this doesn't work
 import cx_Oracle
 dsn = cx_Oracle.makedsn(parms['host'],parms['port'],parms['sid'])
 return cx_Oracle.connect(parms['user'], parms['password'], dsn)

jose soares wrote:
   


Hi Michael,
I tried your script.
the cx_Oracle.connect, works but
the create_engine doesn't...
---

import cx_Oracle
import sqlalchemy
c2 = cx_Oracle.connect(SFERA/p...@oracapusl.net:1521/SHELL)
cursor = c2.cursor();
print 'this one works'
print '-'*30
print cursor.execute(select 1 from dual).fetchone()
print
print 'this one does not:'
print '-'*30
e = sqlalchemy.create_engine(oracle://SFERA:p...@oracapusl.net:1521/SHELL)
c = e.connect()
c.scalar(select 1 from dual)
==



this one works
--
(1,)

this one does not:
--
Traceback (most recent call last):
File /home/admin/buildout/bin/python, line 73, in module
 execfile(__file__)
File b.py, line 27, in module
 c = e.connect()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1811, in connect
 return self.Connection(self, **kwargs)
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 832, in __init__
 self.__connection = connection or engine.raw_connection()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1874, in raw_connection
 return self.pool.unique_connection()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 142, in unique_connection
 return _ConnectionFairy(self).checkout()
File 

Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

[jo]

I solved the problem using this monkeypatch to makedsn as suggested me 
by Christoph Zwerschke.


makedsn = cx_Oracle.makedsn
cx_Oracle.makedsn = lambda *args, **kw: makedsn(*args, 
**kw).replace('SID','SERVICE_NAME')


Thaks any way to everyone.

j

Michael Bayer wrote:

yeah I dunno,   the problem is at the cx_Oracle / OCI level at this point, 
since you can illustrate cx_Oracle/makedsn() not working.  You might need to 
ask on their list at this point (only give them the init_db_conn() scripts, 
don't give them any SQLalchemy stuff):

https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

a workaround for now is you can put your successfully-connecting function to 
create_engine() using the creator argument:

e = create_engine(oracle://, creator=my_connect_function)



On Dec 14, 2011, at 10:49 AM, jose soares wrote:

  

Ok, I changed the file  $ORACLE_HOME/network/admin/tnsnames.ora but it still 
doesn't work.:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None



# tnsnames.ora Network Configuration File: 
/usr/share/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SHELL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))

SHELL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))
  (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SHELL)
  )
)



Michael Bayer wrote:


so makedsn() will give you:

 
  

cx_Oracle.makedsn(oracapsul.net,  1521, SHELL)
   


'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracapsul.net)(PORT=1521)))(CONNECT_DATA=(SID=SHELL)))'


and that should match in your tnsnames.ora file  
($ORACLE_HOME/network/admin/tnsnames.ora).   would have an entry like:

SHELL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsul.net)(PORT = 1521))
   (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = SHELL)
   )
 )


maybe there's a discrepancy between the hostnames in use in the file vs. your 
URL.




On Dec 14, 2011, at 4:50 AM, jose soares wrote:

 
  

I also tried two different connection mode.
The first one works but the second one using makedsn doesn't.


def init_db_conn(parms): #this work
 import cx_Oracle
 dburi=%(user)s/%(password)s@%(host)s:%(port)s/%(sid)s % parms
 return cx_Oracle.connect(dburi)


def init_db_conn(parms): #this doesn't work
 import cx_Oracle
 dsn = cx_Oracle.makedsn(parms['host'],parms['port'],parms['sid'])
 return cx_Oracle.connect(parms['user'], parms['password'], dsn)

jose soares wrote:
   


Hi Michael,
I tried your script.
the cx_Oracle.connect, works but
the create_engine doesn't...
---

import cx_Oracle
import sqlalchemy
c2 = cx_Oracle.connect(SFERA/p...@oracapusl.net:1521/SHELL)
cursor = c2.cursor();
print 'this one works'
print '-'*30
print cursor.execute(select 1 from dual).fetchone()
print
print 'this one does not:'
print '-'*30
e = sqlalchemy.create_engine(oracle://SFERA:p...@oracapusl.net:1521/SHELL)
c = e.connect()
c.scalar(select 1 from dual)
==



this one works
--
(1,)

this one does not:
--
Traceback (most recent call last):
File /home/admin/buildout/bin/python, line 73, in module
 execfile(__file__)
File b.py, line 27, in module
 c = e.connect()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1811, in connect
 return self.Connection(self, **kwargs)
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 832, in __init__
 self.__connection = connection or engine.raw_connection()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1874, in raw_connection
 return self.pool.unique_connection()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 142, in unique_connection
 return _ConnectionFairy(self).checkout()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 369, in __init__
 rec = self._connection_record = pool.get()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 213, in get
 return self.do_get()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 732, in do_get
 con = self.create_connection()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 147, in create_connection
 return _ConnectionRecord(self)
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 253, in __init__
 self.connection = self.__connect()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 319, in __connect
 connection = self.__pool._creator()
File 

Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

[jo]

I don't know Michael, only that Craig Hagan in oracl...@freelists.org, 
suggested me this workaround...


...However, I'm pretty sure that the problem is that you're depending 
upon service names for your connection to succeed (that should be how 
the url in your working example behaves), but the dsn you're 
constructing via make_dsn is specifying SID= which is failing your 
connection.


A possible simple solutions for you would be to either

1) write your own equivalent of makedsn, which really ought not be too 
hard. You'd want to emit something like this:


(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SERVICE_NAME=your-service-name)))

vs. what makedsn emits, which is stuff like this:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SID=your-dbname)))

2) use tnsnames, which would then let you avoid generating the dsn in 
software


-- craig




Michael Bayer wrote:

is that a known bug in cx_Oracle ?


On Dec 16, 2011, at 4:45 AM, jo wrote:

  

I solved the problem using this monkeypatch to makedsn as suggested me by 
Christoph Zwerschke.

makedsn = cx_Oracle.makedsn
cx_Oracle.makedsn = lambda *args, **kw: makedsn(*args, 
**kw).replace('SID','SERVICE_NAME')

Thaks any way to everyone.

j

Michael Bayer wrote:


yeah I dunno,   the problem is at the cx_Oracle / OCI level at this point, 
since you can illustrate cx_Oracle/makedsn() not working.  You might need to 
ask on their list at this point (only give them the init_db_conn() scripts, 
don't give them any SQLalchemy stuff):

https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

a workaround for now is you can put your successfully-connecting function to 
create_engine() using the creator argument:

e = create_engine(oracle://, creator=my_connect_function)



On Dec 14, 2011, at 10:49 AM, jose soares wrote:

 
  

Ok, I changed the file  $ORACLE_HOME/network/admin/tnsnames.ora but it still 
doesn't work.:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None



# tnsnames.ora Network Configuration File: 
/usr/share/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SHELL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))

SHELL =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))
 (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = SHELL)
 )
)



Michael Bayer wrote:
   


so makedsn() will give you:

  
  

cx_Oracle.makedsn(oracapsul.net,  1521, SHELL)
  


'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracapsul.net)(PORT=1521)))(CONNECT_DATA=(SID=SHELL)))'


and that should match in your tnsnames.ora file  
($ORACLE_HOME/network/admin/tnsnames.ora).   would have an entry like:

SHELL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsul.net)(PORT = 1521))
  (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SHELL)
  )
)


maybe there's a discrepancy between the hostnames in use in the file vs. your 
URL.




On Dec 14, 2011, at 4:50 AM, jose soares wrote:

  
  

I also tried two different connection mode.
The first one works but the second one using makedsn doesn't.


def init_db_conn(parms): #this work
import cx_Oracle
dburi=%(user)s/%(password)s@%(host)s:%(port)s/%(sid)s % parms
return cx_Oracle.connect(dburi)


def init_db_conn(parms): #this doesn't work
import cx_Oracle
dsn = cx_Oracle.makedsn(parms['host'],parms['port'],parms['sid'])
return cx_Oracle.connect(parms['user'], parms['password'], dsn)

jose soares wrote:
  


Hi Michael,
I tried your script.
the cx_Oracle.connect, works but
the create_engine doesn't...
---

import cx_Oracle
import sqlalchemy
c2 = cx_Oracle.connect(SFERA/p...@oracapusl.net:1521/SHELL)
cursor = c2.cursor();
print 'this one works'
print '-'*30
print cursor.execute(select 1 from dual).fetchone()
print
print 'this one does not:'
print '-'*30
e = sqlalchemy.create_engine(oracle://SFERA:p...@oracapusl.net:1521/SHELL)
c = e.connect()
c.scalar(select 1 from dual)
==



this one works
--
(1,)

this one does not:
--
Traceback (most recent call last):
File /home/admin/buildout/bin/python, line 73, in module
execfile(__file__)
File b.py, line 27, in module
c = e.connect()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1811, in connect
return self.Connection(self, **kwargs)
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 832, in __init__
self.__connection = connection or engine.raw_connection()
File 
/home/admin/buildout/eggs

Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

[jo]

Michael Bayer wrote:

On Dec 16, 2011, at 10:31 AM, jo wrote:

  

I don't know Michael, only that Craig Hagan in oracl...@freelists.org, 
suggested me this workaround...

...However, I'm pretty sure that the problem is that you're depending upon 
service names for your connection to succeed (that should be how the url in your 
working example behaves), but the dsn you're constructing via make_dsn is specifying 
SID= which is failing your connection.

A possible simple solutions for you would be to either

1) write your own equivalent of makedsn, which really ought not be too hard. 
You'd want to emit something like this:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SERVICE_NAME=your-service-name)))

vs. what makedsn emits, which is stuff like this:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your-db-hostname)(PORT=yourport)))(CONNECT_DATA=(SID=your-dbname)))

2) use tnsnames, which would then let you avoid generating the dsn in software



OK so let me tell you how to do that - just leave out the DB part, and replace 
host with tns name:

create_engine(oracle://user:password@SHELL)

  

could you tell me how it becomes in sqlalchemy.dburi on tg prod.cfg ?

sqlalchemy.dburi=oracle://username:password@host:port/service_name

I tried in this way:
sqlalchemy.dburi=oracle://username:password@SHELL:1521/SHELL
but...
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12545: Connect failed 
because target host or object does not exist






  

-- craig




Michael Bayer wrote:


is that a known bug in cx_Oracle ?


On Dec 16, 2011, at 4:45 AM, jo wrote:

 
  

I solved the problem using this monkeypatch to makedsn as suggested me by 
Christoph Zwerschke.

makedsn = cx_Oracle.makedsn
cx_Oracle.makedsn = lambda *args, **kw: makedsn(*args, 
**kw).replace('SID','SERVICE_NAME')

Thaks any way to everyone.

j

Michael Bayer wrote:
   


yeah I dunno,   the problem is at the cx_Oracle / OCI level at this point, 
since you can illustrate cx_Oracle/makedsn() not working.  You might need to 
ask on their list at this point (only give them the init_db_conn() scripts, 
don't give them any SQLalchemy stuff):

https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

a workaround for now is you can put your successfully-connecting function to 
create_engine() using the creator argument:

e = create_engine(oracle://, creator=my_connect_function)



On Dec 14, 2011, at 10:49 AM, jose soares wrote:

  
  

Ok, I changed the file  $ORACLE_HOME/network/admin/tnsnames.ora but it still 
doesn't work.:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None



# tnsnames.ora Network Configuration File: 
/usr/share/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SHELL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))

SHELL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = SHELL)
)
)



Michael Bayer wrote:
  


so makedsn() will give you:

   
  

cx_Oracle.makedsn(oracapsul.net,  1521, SHELL)
 


'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracapsul.net)(PORT=1521)))(CONNECT_DATA=(SID=SHELL)))'


and that should match in your tnsnames.ora file  
($ORACLE_HOME/network/admin/tnsnames.ora).   would have an entry like:

SHELL =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsul.net)(PORT = 1521))
 (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = SHELL)
 )
)


maybe there's a discrepancy between the hostnames in use in the file vs. your 
URL.




On Dec 14, 2011, at 4:50 AM, jose soares wrote:

   
  

I also tried two different connection mode.
The first one works but the second one using makedsn doesn't.


def init_db_conn(parms): #this work
import cx_Oracle
dburi=%(user)s/%(password)s@%(host)s:%(port)s/%(sid)s % parms
return cx_Oracle.connect(dburi)


def init_db_conn(parms): #this doesn't work
import cx_Oracle
dsn = cx_Oracle.makedsn(parms['host'],parms['port'],parms['sid'])
return cx_Oracle.connect(parms['user'], parms['password'], dsn)

jose soares wrote:
 


Hi Michael,
I tried your script.
the cx_Oracle.connect, works but
the create_engine doesn't...
---

import cx_Oracle
import sqlalchemy
c2 = cx_Oracle.connect(SFERA/p...@oracapusl.net:1521/SHELL)
cursor = c2.cursor();
print 'this one works'
print '-'*30
print cursor.execute(select 1 from dual).fetchone()
print
print 'this one does not:'
print '-'*30
e = sqlalchemy.create_engine(oracle://SFERA:p...@oracapusl.net:1521/SHELL)
c = e.connect()
c.scalar(select 1 from dual

Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

[jo]

Michael Bayer wrote:

is that a known bug in cx_Oracle ?
  

The latest version of cx_Oracle 5.1 is changed:

5) Added additional parameter service_name to makedsn() which can be 
used to

   use the service_name rather than the SID in the DSN string that is
   generated.

makedsn(host = 'myhost', port = 1521, service_name = 'mydb')

j




On Dec 16, 2011, at 4:45 AM, jo wrote:

  

I solved the problem using this monkeypatch to makedsn as suggested me by 
Christoph Zwerschke.

makedsn = cx_Oracle.makedsn
cx_Oracle.makedsn = lambda *args, **kw: makedsn(*args, 
**kw).replace('SID','SERVICE_NAME')

Thaks any way to everyone.

j

Michael Bayer wrote:


yeah I dunno,   the problem is at the cx_Oracle / OCI level at this point, 
since you can illustrate cx_Oracle/makedsn() not working.  You might need to 
ask on their list at this point (only give them the init_db_conn() scripts, 
don't give them any SQLalchemy stuff):

https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

a workaround for now is you can put your successfully-connecting function to 
create_engine() using the creator argument:

e = create_engine(oracle://, creator=my_connect_function)



On Dec 14, 2011, at 10:49 AM, jose soares wrote:

 
  

Ok, I changed the file  $ORACLE_HOME/network/admin/tnsnames.ora but it still 
doesn't work.:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not 
currently know of SID given in connect descriptor
None None



# tnsnames.ora Network Configuration File: 
/usr/share/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SHELL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))

SHELL =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsusl.net)(PORT = 1521))
 (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = SHELL)
 )
)



Michael Bayer wrote:
   


so makedsn() will give you:

  
  

cx_Oracle.makedsn(oracapsul.net,  1521, SHELL)
  


'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracapsul.net)(PORT=1521)))(CONNECT_DATA=(SID=SHELL)))'


and that should match in your tnsnames.ora file  
($ORACLE_HOME/network/admin/tnsnames.ora).   would have an entry like:

SHELL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracapsul.net)(PORT = 1521))
  (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SHELL)
  )
)


maybe there's a discrepancy between the hostnames in use in the file vs. your 
URL.




On Dec 14, 2011, at 4:50 AM, jose soares wrote:

  
  

I also tried two different connection mode.
The first one works but the second one using makedsn doesn't.


def init_db_conn(parms): #this work
import cx_Oracle
dburi=%(user)s/%(password)s@%(host)s:%(port)s/%(sid)s % parms
return cx_Oracle.connect(dburi)


def init_db_conn(parms): #this doesn't work
import cx_Oracle
dsn = cx_Oracle.makedsn(parms['host'],parms['port'],parms['sid'])
return cx_Oracle.connect(parms['user'], parms['password'], dsn)

jose soares wrote:
  


Hi Michael,
I tried your script.
the cx_Oracle.connect, works but
the create_engine doesn't...
---

import cx_Oracle
import sqlalchemy
c2 = cx_Oracle.connect(SFERA/p...@oracapusl.net:1521/SHELL)
cursor = c2.cursor();
print 'this one works'
print '-'*30
print cursor.execute(select 1 from dual).fetchone()
print
print 'this one does not:'
print '-'*30
e = sqlalchemy.create_engine(oracle://SFERA:p...@oracapusl.net:1521/SHELL)
c = e.connect()
c.scalar(select 1 from dual)
==



this one works
--
(1,)

this one does not:
--
Traceback (most recent call last):
File /home/admin/buildout/bin/python, line 73, in module
execfile(__file__)
File b.py, line 27, in module
c = e.connect()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1811, in connect
return self.Connection(self, **kwargs)
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 832, in __init__
self.__connection = connection or engine.raw_connection()
File 
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
 line 1874, in raw_connection
return self.pool.unique_connection()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 142, in unique_connection
return _ConnectionFairy(self).checkout()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 369, in __init__
rec = self._connection_record = pool.get()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 213, in get
return self.do_get()
File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py, 
line 732, in do_get
con = self.create_connection()
File /home/admin/buildout/eggs

[sqlalchemy] Postgres:TEXT and Oracle:CLOB

[jo]

Hi all,

I have this definition of a table.


   session = Table('session', database.metadata,
Column('id', Unicode(40), primary_key=True, nullable=False),
Column('data', Text),
Column('expiration_time', TIMESTAMP(timezone=False)),
)

In the PostgreSQL DB, it creates a table like this:

 name   |type | default | not_null
-+-+-+--
id  | character varying(40)   | | t
data| text| | f
expiration_time | timestamp without time zone | | f

In the Oracle DB, like this:

name| data_type| nullable | data_default | data_length
--- +  +  +  + ---
ID  | NVARCHAR2| N| NULL | 80
DATA| CLOB | Y| NULL | 4000
EXPIRATION_TIME | TIMESTAMP(6) | Y| NULL | 11

When I use it with PostgreSQL all is OK but
when I try to use it with Oracle, pickle raises this error:

...data *=* pickle*.*loads*(*pickled_data*)*|
*TypeError: ('loads() argument 1 must be string, not cx_Oracle.LOB', 
bound method Root.index of sicer.BASE.controller.Root object at 
0x8231f10)*



What can I do to avoid this error?
thank you,

j

--
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] group by and Oracle

[jo]

Yes, Ian, it works, :-)
thank you
j

Ian Kelly wrote:

On Fri, Dec 3, 2010 at 4:08 AM, jo jose.soa...@sferacarta.com wrote:
  

Hi all,

I'm trying to write a GROUP BY query grouped by a function (to_char) using a
variable format, which could be 'yy' or ''
as in:

sql=session.query(
  func.to_char(Prestazione.c.data,format),
  func.sum(Prestazione.c.quantita).label('quantita'),
  func.sum(Prestazione.c.importo).label('importo')
  )
sql=sql.filter(Verifica.c.codice == Tariffa.c.codice)
sql=sql.filter(Prestazione.c.id_tariffa == Tariffa.c.id)
sql=sql.group_by(Verifica.c.codice, func.to_char(Prestazione.c.data,format))



Have you tried using the same func result in both places, i.e.:

to_char = func.to_char(Prestazione.c.data,format)

sql=session.query(
  to_char,
  func.sum(Prestazione.c.quantita).label('quantita'),
  func.sum(Prestazione.c.importo).label('importo')
  )
sql=sql.filter(Verifica.c.codice == Tariffa.c.codice)
sql=sql.filter(Prestazione.c.id_tariffa == Tariffa.c.id)
sql=sql.group_by(Verifica.c.codice, to_char)

  



--
Jose Soares
Sferacarta Net 
Via Bazzanese 69

40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
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] group by and Oracle

[jo]

Hi all,

I'm trying to write a GROUP BY query grouped by a function (to_char) 
using a variable format, which could be 'yy' or ''

as in:

sql=session.query(
   func.to_char(Prestazione.c.data,format),
   func.sum(Prestazione.c.quantita).label('quantita'),
   func.sum(Prestazione.c.importo).label('importo')
   )
sql=sql.filter(Verifica.c.codice == Tariffa.c.codice)
sql=sql.filter(Prestazione.c.id_tariffa == Tariffa.c.id)
sql=sql.group_by(Verifica.c.codice, func.to_char(Prestazione.c.data,format))


it works fine in PostgreSQL...

pg:

SELECT to_char(prestazione.data, %(to_char_2)s) AS to_char_1, 
sum(prestazione.quantita) AS quantita, sum(prestazione.importo) AS importo

FROM prestazione, verifica, tariffa
WHERE verifica.codice = tariffa.codice
AND prestazione.id_tariffa = tariffa.id
AND prestazione.aa_bolletta = %(aa_bolletta_1)s
AND prestazione.nr_bolletta = %(nr_bolletta_1)s
AND prestazione.sezionale = %(sezionale_1)s
GROUP BY verifica.codice, to_char(prestazione.data, %(to_char_3)s)
{'to_char_2': '', 'nr_bolletta_1': 1, 'aa_bolletta_1': 2009, 
'sezionale_1': u'53', 'to_char_3': ''}


Col ('to_char_1', 'quantita', 'importo')

Row (u'2009', Decimal('1.000'), Decimal('482.000'))
Out[1]: (u'2009', Decimal('1.000'), Decimal('482.000'))


... but Oracle...

DatabaseError: (DatabaseError) ORA-00979: not a GROUP BY expression
'SELECT to_char(prestazione.data, :to_char_2) AS to_char_1, 
sum(prestazione.quantita) AS quantita, sum(prestazione.importo) AS importo

FROM prestazione, verifica, tariffa
WHERE verifica.codice = tariffa.codice
AND prestazione.id_tariffa = tariffa.id
AND prestazione.aa_bolletta = :aa_bolletta_1
AND prestazione.nr_bolletta = :nr_bolletta_1
AND prestazione.sezionale = :sezionale_1
GROUP BY verifica.codice, to_char(prestazione.data, :to_char_3)'
{'to_char_2': '', 'nr_bolletta_1': 1, 'aa_bolletta_1': 2010, 
'sezionale_1': u'53', 'to_char_3': ''}



version: SA 0.6beta3

Thanks for any help
j

--
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] CheckConstraint compatibility

[jo]

Michael Bayer wrote:

On May 19, 2010, at 5:34 AM, jose soares wrote:

  

Hi all,

I have to create a constraint like this:

  CheckConstraint('data_start = CURRENT_DATE'),

it works for PostgreSQL but it doesn't work for Oracle10.

Is there some workaround to make it compatible with pg and oracle?



Assuming there's a way to create such a CHECK constraint on Oracle, you should 
use the DDL() construct with per-dialect instructions for Oracle - and for your 
CheckConstraint that works on PG, set it up with an AddConstraint that is 
specific to Postgresql.

http://www.sqlalchemy.org/docs/metadata.html#customizing-ddl
  
Oracle doesn't support such constrtaint, then I have to find a way to 
create it, not at db level, but in the code.
My hope was to be able to define it, only at SQLAlchemy level, like we 
do with some sa functions.


j

--
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] select .group_by

[jo]

Hi all,

In version 0.6 seems the group_by property does nothing...

(Pdb) sql = 
select([Verifica.c.codice,func.sum(Prestazione.c.importo).label('importo')])


(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione

(Pdb) sql.group_by(Verifica.c.codice)
sqlalchemy.sql.expression.Select at 0x706b6d0; Select object

(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
(Pdb)  




I expected a query like this:

SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
GROUP BY verifica.codice

How it works in 0.6?

j


--
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] select .group_by

[jo]

Lance Edgar wrote:

On 4/27/2010 5:22 AM, jo wrote:

Hi all,

In version 0.6 seems the group_by property does nothing...

(Pdb) sql =
select([Verifica.c.codice,func.sum(Prestazione.c.importo).label('importo')]) 




(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione

(Pdb) sql.group_by(Verifica.c.codice)
sqlalchemy.sql.expression.Select at 0x706b6d0; Select object

(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
(Pdb)


I expected a query like this:

SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
GROUP BY verifica.codice

How it works in 0.6?


I believe the group_by() method will return a new selectable instead 
of changing your current one in-place.  So perhaps try:


sql_grouped = sql.group_by(Verifica.c.codice)

print sql_grouped

Lance


You are right, Lance,
Thank you.
j

--
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] about commit()

[jo]

Hi all,

I need to insert a new row and get back the last inserted id,
I have some difficulty using the flush(), then I'm trying with commit() but
I can't understand how commit() works in 0.6.
In the following script I try to update a row and it works properly
but when I try to insert a new one, it doesn't work,
there's no messages but the row is not inserted into the table.
Is this the right way ?


from sqlalchemy.orm import sessionmaker
Session = sessionmaker(autoflush=True)
session = Session()

#update an existing row... it works
old = Specie.get('D')
old.specie_descrizione='dog'

#insert a new row... it doesn't work
new=Specie(
   specie_codice='C',
   specie_descrizione='cat'
)

session.commit()

thanks for any help

j

--
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] flush(), save(), delete()

[jo]

King Simon-NFHD78 wrote:

Jo wrote:
[SNIP]
  

 and-

In [13]: aa=Anagrafica.get(111)

In [14]: aa.delete()

In [15]: aa.flush()

-

but in version 0.6 I can't find flush(), save(), delete(). 
Where are them?


thank you

j




These methods were added to your objects by the old assign_mapper
extension. This extension no longer exists, and the methods on the
Session should be used instead. For example, instead of aa.delete(), you
would say session.delete(aa).

If you want to preserve your old API, you could create a base class for
your mapped objects that implements each of the old methods. A delete
method might look like this (untested):

class Base(object):
def _get_session(self):
return sqlalchemy.orm.object_session(self)

def delete(self):
session = self._get_session()
session.delete(self)


The flush method would correspond to session.flush([self]), but you
should read the deprecation warning about passing a list of objects at
http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.or
m.session.Session.flush.

Assuming that the save() method adds the object to the current
contextual (scoped) session, it would be as simple as:

def save(self):
session = Session()
session.add(self)

However, I personally wouldn't add that one, as it ties your class to
the scoped session mechanism which may not always be what you want.

Hope that helps,

Simon
  

thank you for your help, Simon.

j


--
Jose Soares
Sferacarta Net 
Via Bazzanese 69

40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
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] flush() again

[jo]

Problems with flush() again...

I thougth I could avoid the use of flush() by nesting the  mappers,
but...
I have two mappers:

mapper(CatalogazioneModulistica,
   tbl['catalogazione_modulistica'],
   column_prefix = 'catalogazione_modulistica_',
   )
and:
mapper(GestioneDocFile,
   tbl['gestione_doc_file'],
   column_prefix = 'gestione_doc_file_',
   properties = dict(catalogazione =
relation(CatalogazioneModulistica),
   )
 
fk = GestioneDocFile.numero_catalogo_catalogazione REFERENCES
catalogazioneModulistica.numero_catalogo

I tried an INSERT in this way:

GestioneDocFile( 
    catalogazione = CatalogazioneModulistica(
                   catalogazione_modulistica_numero_catalogo = 1,
                   catalogazione_modulistica_nome = 'pippo',
              
catalogazione_modulistica_cod_classe_titolario='VETB',
   catalogazione_modulistica_cod_tipologia = 'VB'
   ),
    gestione_doc_file_nomefile='nome',
    gestione_doc_file_id_operatore=1,
    gestione_doc_file_tipo_documento='M')

but...

SQLError: (IntegrityError) insert or update on table
"gestione_doc_file" violates foreign key constraint "gestione_doc_file_numero_catalogo_catalogazione_fkey"
DETAIL:  Key
(numero_catalogo_catalogazione)=(1) is not present in table
"catalogazione_modulistica".
 'INSERT INTO gestione_doc_file (id, nomefile, nome_visualizzato,
descrizione, protocollo, data_protocollo, tipo_file, id_operatore,
numero_catalogo_catalogazione, tipo_documento, nome_tabella_proven,
chiave_tabella_proven, id_unita_aziendale, cod_argomento, password)
VALUES (%(id)s, %(nomefile)s, %(nome_visualizzato)s, %(descrizione)s,
%(protocollo)s, %(data_protocollo)s, %(tipo_file)s, %(id_operatore)s,
%(numero_catalogo_catalogazione)s, %(tipo_documento)s,
%(nome_tabella_proven)s, %(chiave_tabella_proven)s,
%(id_unita_aziendale)s, %(cod_argomento)s, %(password)s)' {'password':
None, 'chiave_tabella_proven': None, 'nome_tabella_proven': None,
'cod_argomento': None, 'nomefile': 'nome', 'descrizione': None, 'id':
85L, 'id_operatore': 1, 'numero_catalogo_catalogazione': 1,
'id_unita_aziendale': None, 'nome_visualizzato': None,
'data_protocollo': None, 'protocollo': None, 'tipo_file': None,
'tipo_documento': 'M'}


How can I do this operation without insert before
CatalogazioneModulistica and flushing it
and then insert GestioneDocFile assigning
CatalogazioneModulistica.numero_catalogo to
GestioneDocFile.numero_catalogo_catalogazione ?

j


jo wrote:
King
Simon-NFHD78 wrote:
  
  Jo wrote:

[SNIP]

 

and-
  
  
In [13]: aa=Anagrafica.get(111)
  
  
In [14]: aa.delete()
  
  
In [15]: aa.flush()
  
  
-
  
  
but in version 0.6 I can't find flush(), save(), delete(). Where are
them?
  
  
thank you
  
  
j
  
  
    

These methods were added to your objects by the old assign_mapper

extension. This extension no longer exists, and the methods on the

Session should be used instead. For example, instead of aa.delete(),
you

would say session.delete(aa).


If you want to preserve your old API, you could create a base class for

your mapped objects that implements each of the old methods. A delete

method might look like this (untested):


class Base(object):

    def _get_session(self):

    return sqlalchemy.orm.object_session(self)


    def delete(self):

    session = self._get_session()

    session.delete(self)



The flush method would correspond to session.flush([self]), but you

should read the deprecation warning about passing a list of objects at

http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.or

m.session.Session.flush.


Assuming that the save() method adds the object to the current

contextual (scoped) session, it would be as simple as:


    def save(self):

    session = Session()

    session.add(self)


However, I personally wouldn't add that one, as it ties your class to

the scoped session mechanism which may not always be what you want.


Hope that helps,


Simon

  
thank you for your help, Simon.
  
  
j
  
  
  



-- 
Jose Soares
Sferacarta Net 
Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La

[sqlalchemy] column_prefix

[jo]

Hi all,


Module sqlalchemy.engine.base:*1180* in |__execute_context
||context*.*parameters*[**0**]**,* context*=*context*)*|
Module sqlalchemy.engine.base:*1249* in |_cursor_execute||
self*.*_handle_dbapi_exception*(*e*,* statement*,* parameters*,* 
cursor*,* context*)*|

Module sqlalchemy.engine.base:*1247* in |_cursor_execute|
|self*.*dialect*.*do_execute*(*cursor*,* statement*,* parameters*,* 
context*=*context*)*|
Module sqlalchemy.engine.default:*266* in |do_execute| 
http://tg11.sferacarta.com:8000/operatore/save#

|cursor*.*execute*(*statement*,* parameters*)*|
*IntegrityError: ('(IntegrityError) null value in column id violates 
not-null constraint\n', bound method Controller.save of 
sicer.BASE.controller.authentication.operatore.Controller object at 
0x4e178d0) 'UPDATE anagrafica SET id=%(id)s, telefono=%(telefono)s, 
ts_ultima_modifica=%(ts_ultima_modifica)s, 
id_operatore=%(id_operatore)s, tipo=%(tipo)s WHERE anagrafica.id = 
%(anagrafica_id)s' {'ts_ultima_modifica': None, 'tipo': None, 
'anagrafica_id': 141092, 'telefono': None, 'id': None, 'id_operatore': 
1695}* |  
http://tg11.sferacarta.com:8000/operatore/save#
context*.*cursor*,*

   context*.*statement*,*
   context*.*parameters*[**0**]**,* 
context*=*context*)*
  
   *if* context*.*compiled*:*| |  
http://tg11.sferacarta.com:8000/operatore/save#
self.dialect.do_execute(cursor, statement, parameters, context=context)

   except Exception, e:
   self._handle_dbapi_exception(e, statement, parameters, 
cursor, context)

   raise
   | |  http://tg11.sferacarta.com:8000/operatore/save#
self.engine.logger.info(%r, parameters)

   try:
   self.dialect.do_execute(cursor, statement, parameters, 
context=context)

   except Exception, e:
   self._handle_dbapi_exception(e, statement, parameters, 
cursor, context)| |  http://tg11.sferacarta.com:8000/operatore/save#
   *def* *do_execute**(**self**,* *cursor**,* *statement**,* 
*parameters**,* *context**=*None*)**:*

   cursor*.*execute*(*statement*,* parameters*)*
  
   *def* *is_disconnect**(**self**,* *e**)**:*|


---

I'm using column_prefix, but seems sa compiles this query without prefix, 
furthermore it try to update the primary key with a NULL value.

*'UPDATE anagrafica 
SET id=%(id)s,
   telefono=%(telefono)s, 
   ts_ultima_modifica=%(ts_ultima_modifica)s,
   id_operatore=%(id_operatore)s, 
   tipo=%(tipo)s 
WHERE anagrafica.id = %(anagrafica_id)s' 



*|   http://tg11.sferacarta.com:8000/operatore/save# *def* 
*do_execute**(**self**,* *cursor**,* *statement**,* *parameters**,* 
*context**=*None*)**:*

   cursor*.*execute*(*statement*,* parameters*)*

   


   *def* *is_disconnect**(**self**,* *e**)**:*|
class Anagrafica():
 pass
mapper(Anagrafica,
  tbl['anagrafica'],
  column_prefix = 'anagrafica_',
  extension=History(),
   )

The migration from 0.3 to 0.6 is too hard. :-( 


j







--
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] object has no attribute 'delete'

[jo]

Hi all,

I'm trying migrate from 0.3 to 0.6
I don't know how to delete an object

in the old version it was:

My.get(1).delete()

in 0.6:

My.get(1).delete()

AttributeError: 'My' object has no attribute 'delete'

j

--
Jose Soares
Sferacarta Net 
Via Bazzanese 69

40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
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] cls._state / cls._state.get('original')

[jo]

Hi all,

I cannot find anymore the attribute _state :

if (not cls._state or not cls._state.get('original') or 
(cls._state['original'].data.get(k) != data.get(k:


Could someone please help me?
thank you

j

--
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] object has no attribute 'delete'

[jo]

It works, thank you, Fernando.  :-)
j
Fernando Takai wrote:


Hi,

You can try:

My.query.filter_by(id=id).delete()
(If your object can use the .query syntax)
or
session.query(My).filter_by(id=id).delete()

On Apr 13, 2010 6:12 AM, jo jose.soa...@sferacarta.com 
mailto:jose.soa...@sferacarta.com wrote:


Hi all,

I'm trying migrate from 0.3 to 0.6
I don't know how to delete an object

in the old version it was:

My.get(1).delete()

in 0.6:

My.get(1).delete()

AttributeError: 'My' object has no attribute 'delete'

j

--
Jose Soares
Sferacarta Net Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com http://www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale 
file allegato sono riservate e, comunque, destinate esclusivamente 
alla persona o ente sopraindicati, ai sensi del decreto legislativo 
30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura 
della mail trasmessa, da parte di qualsiasi soggetto diverso dal 
destinatario, sono vietate. La correttezza, l’integrità e la 
sicurezza della presente mail non possono essere garantite. Se avete 
ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.


This communication is intended only for use by the addressee, 
pursuant to legislative decree 30 June 2003, n. 196. It may contain 
confidential or privileged information. You should not copy or use it 
to disclose its contents to any other person. Transmission cannot be 
guaranteed to be error-free, complete and secure. If you are not the 
intended recipient and receive this communication unintentionally, 
please inform us immediately and then delete this message from your 
system. 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 
mailto:sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com 
mailto:sqlalchemy%2bunsubscr...@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.



--
Jose Soares
Sferacarta Net 
Via Bazzanese 69

40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
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] SQLAlchemy.func.max()

[jo]

Michael Bayer wrote:

jose soares wrote:
  

Michael Bayer wrote:


jo wrote:

  

 I was using heavily the column_prefix and my code is full of it, as
in:

 mapper(Anagrafica,
tbl['anagrafica'],
column_prefix = 'anagrafica_',
extension=History(),
properties = {
'comune' : relation( Comune, primaryjoin  =
tbl['anagrafica'].c.id_comune == tbl['comune'].c.id ),
'nazione': relation( Nazione, primaryjoin =
tbl['anagrafica'].c.cod_cittadinanza ==
tbl['nazione'].c.codic...
'comune_nascita' : relation( Comune, primaryjoin  =
tbl['anagrafica'].c.id_comune_nascita == tbl['comune'].c.id),
'nazione_nascita': relation( Nazione, primaryjoin =
tbl['anagrafica'].c.cod_nazione_nascita ==
tbl['nazione'].c.co...
'professione': relation( Professione ),
'titolo_studio'  : relation( TitoloStudio ),
}
 )



column_prefix remains available.  That above code is compatible with
0.6.

  

I tried upgrade from 0.3 to 0.4 and if I remember well, there wasn't
column_prefix in 0.4.
Are you suggest me to migrate directly from 0.3 to 0.6 instead of a
gradual migration?



if it were me I'd certainly go straight to 0.6.   I'd be curious to know
what kinds of code patterns you think would be difficult to migrate (I
haven't used 0.3 in years obviously).


  
Well, I need to accommodate SQLAlchemy with an old versione of 
Turbogears, and it is not so easy,

anyway, I think I'll get another try.
I hope you can help me to solve some possible trouble. :-)

j



--
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] TypeError: unsupported operand type(s) for +: 'Decimal' and 'float'

[jo]

this is the 0.3 syntax, and now it works.

class MyType(TypeDecorator):
impl = Numeric
def convert_bind_param(self, value, engine):
return decimal.Decimal(str(value))
def convert_result_value(self, value, engine):
return decimal.Decimal(str(value))

Thank you very much, Michael. :-)

j

jo wrote:

Michael Bayer wrote:

jose soares wrote:
  

Michael Bayer wrote:


jose soares wrote:

  

Hi all,

I'm using Oracle and PostgreSQL with SQLAlchemy and I have some
troubles
to make the code compatible  with both of them.
Numeric sa type returns a different type with oracle and pg.

For example, in the following table I'm using the Column 'importo' with
type Numeric as:


tbl['prestazione'] = Table('prestazione', database.metadata,
Column('id', Integer, Sequence('prestazione_seq'),
nullable=False, primary_key=True),
Column('data', Date, nullable=False),
Column('quantita', Numeric(15,3)),
Column('importo', Numeric(12,3))
)

while oracle returns a float type as:

prestazione.c.importo = 12.0


postgres returns a Decimal type as:

prestazione.c.importo = Decimal(0.000)

and I have difficulties to make code compatible, because sometimes the
program raises a TypeError error as:

TypeError: unsupported operand type(s) for +: 'Decimal' and 'float'

Is there some w.a. to avoid this thing?



if you're on 0.6 (or even 0.5 for that matter) the Numeric type should
be
returning Decimal in all cases from result sets.

  

I'm using 0.3.10 , I know I must to upgrade but it's not so easy. :-[
Is there something that I can do to avoid this error in 0.3.10?



if its 0.3 probably use TypeDecorator.
  

Your suggest is fine, Michael,
I tried this...but...


class MyType(types.TypeDecorator):
impl = types.Numeric
def process_bind_param(self, value, dialect):
return decimal.Decimal(str(value))

def process_result_value(self, value, dialect):
return decimal.Decimal(str(value))

def copy(self):
return MyType(self.impl.length)

.
test = Table('test', database.metadata,
Column('id', Integer, nullable=False, primary_key=True),
Column('data', Date, nullable=False),
Column('importo', MyType(12,3))
)



insert into test(data,id,importo) values ('2009-01-01',2,32.331)
select * from test

id | data | importo
-- + --- + ---
2 | 2009-01-01 00:00:00 | 32.331


$tg_admin shell

In [36]: test
Out[36]: 
Table('test',ThreadLocalMetaData(),Column('id',Integer(),primary_key=True,nullable=False),Column('data',Date(),nullable=False),Column('importo',MyType()),schema=None)


In [37]: 
type(dict(test.select(test.c.id==2).execute().fetchone())['IMPORTO'])


SELECT test.id, test.data, test.importo
FROM test
WHERE test.id = :test_id

{'test_id': 2}
Out[37]: type 'float'

-

What's wrong?
j

--
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.



--
Jose Soares
Sferacarta Net 
Via Bazzanese 69

40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
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] SQLAlchemy.func.max()

[jo]

Here another difference between Oracle and PostgreSQL
The SQLAlchemy.func.max() on a column date, returns a datetime.date in 
pg but a datetime.datetime in oracle...

Why this difference?


take a look:

Bolletta = Table('bolletta', database.metadata,
   Column('id',   Integer, nullable=False, primary_key=True),
   Column('data', Date)
   )


postgres session   | oracle 
session

---|---
In [1]: from sqlalchemy import select, func| In [1]: 
from sqlalchemy import select, func

  |
In [2]: select([Bolletta.c.data]).execute().scalar()   | In [2]: 
select([Bolletta.c.data]).execute().scalar()
SELECT bolletta.data FROM bolletta | SELECT 
bolletta.data FROM bolletta

  |
Out[2]: datetime.date(2007, 12, 31)| Out[2]: 
datetime.date(2010, 4, 7)

  |
In [3]: select([func.max(Bolletta.c.data)]).execute().scalar() | In [3]: 
select([func.max(Bolletta.c.data)]).execute().scalar()
SELECT max(bolletta.data) FROM bolletta| SELECT 
max(bolletta.data) FROM bolletta

  |
Out[3]: datetime.date(2010, 4, 7)  | Out[3]: 
*datetime.datetime(*2010, 4, 7, 0, 0),)

---|---


j

--
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] SQLAlchemy.func.max()

[jo]

Michael Bayer wrote:

  jo wrote:
  
  
Here another difference between Oracle and PostgreSQL
The SQLAlchemy.func.max() on a column date, returns a datetime.date in
pg but a datetime.datetime in oracle...
Why this difference?

  
  
Oracle doesn't have a "date" type.this is another thing the types
system smoothes out (can't speak for 0.3 though).

If you're using Oracle, I can't recommend upgrading to 0.6 more strongly -
Oracle was an almost completely untested alpha dialect in 0.3 and has
continued to have major issues throughout 0.5, until we finally had the
resources to get 100% test coverage in 0.6.
  

I would love to upgrade to 0.4, 0.5, 0.6, but I can't.

I was using heavily the column_prefix and my code is full of it, as in:

mapper(Anagrafica,
 tbl['anagrafica'],
 column_prefix = 'anagrafica_',
 extension=History(),
 properties = {
 'comune' : relation( Comune, primaryjoin =
tbl['anagrafica'].c.id_comune == tbl['comune'].c.id ),
 'nazione' : relation( Nazione, primaryjoin =
tbl['anagrafica'].c.cod_cittadinanza == tbl['nazione'].c.codic...
 'comune_nascita' : relation( Comune, primaryjoin =
tbl['anagrafica'].c.id_comune_nascita == tbl['comune'].c.id),
 'nazione_nascita': relation( Nazione, primaryjoin =
tbl['anagrafica'].c.cod_nazione_nascita == tbl['nazione'].c.co...
 'professione' : relation( Professione ),
 'titolo_studio' : relation( TitoloStudio ),
 }
 )

and again I was using 
from sqlalchemy.ext.selectresults import SelectResults
and I see these things are incompatible with new releases.

I tried more than once, migrating to 0.4 but at end I had to give up
because new releases are to different.

j



  



  
  

take a look:

Bolletta = Table('bolletta', database.metadata,
Column('id',   Integer, nullable=False, primary_key=True),
Column('data', Date)
)


postgres session   | oracle
session
---|---
In [1]: from sqlalchemy import select, func| In [1]:
from sqlalchemy import select, func
   |
In [2]: select([Bolletta.c.data]).execute().scalar()   | In [2]:
select([Bolletta.c.data]).execute().scalar()
SELECT bolletta.data FROM bolletta | SELECT
bolletta.data FROM bolletta
   |
Out[2]: datetime.date(2007, 12, 31)| Out[2]:
datetime.date(2010, 4, 7)
   |
In [3]: select([func.max(Bolletta.c.data)]).execute().scalar() | In [3]:
select([func.max(Bolletta.c.data)]).execute().scalar()
SELECT max(bolletta.data) FROM bolletta| SELECT
max(bolletta.data) FROM bolletta
   |
Out[3]: datetime.date(2010, 4, 7)  | Out[3]:
*datetime.datetime(*2010, 4, 7, 0, 0),)
---|---


j

--
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.



  
  
  



-- 
Jose Soares
Sferacarta Net 
Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, lintegrit e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.






-- 
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 g

[sqlalchemy] identifier is too long

[jo]

Hi all,

I have some troubles creating my db schema with Oracle. The problem is 
on this column:


Column('cod_caratteristica_rischio', Unicode(10), index=True, 
nullable=False)


It works fine in PostgreSQL but when I try it on Oracle sa tries to 
create an index with a name too long ( 30 char).



tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata,
Column('id', Integer, Sequence('rischio_attivita_seq'), primary_key=True),
Column('cod_attivita', Unicode(10),nullable=False),
Column('cod_caratteristica_rischio', Unicode(10), index=True, 
nullable=False),

Column('tipo_calcolo', Integer),
Column('algoritmo', Unicode(200)),
Column('fattore_x', Float, PassiveDefault('1.0')),
ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']),
ForeignKeyConstraint(['cod_caratteristica_rischio'], 
['caratteristica_rischio.codice'])

)


File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 
599, in _execute

raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00972: identifier is 
too long
'CREATE INDEX ix_rischio_attivita_cod_caratteristica_rischio ON 
rischio_attivita (cod_caratteristica_rischio)' {}



--
Jose Soares
Sferacarta Net 
Via Bazzanese 69

40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--
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] identifier is too long

[jo]

I see, thank you, Mariano.
j
Mariano Mara wrote:

Excerpts from jo's message of Tue Mar 30 03:25:18 -0300 2010:
  

Hi all,

I have some troubles creating my db schema with Oracle. The problem is 
on this column:


Column('cod_caratteristica_rischio', Unicode(10), index=True, 
nullable=False)


It works fine in PostgreSQL but when I try it on Oracle sa tries to 
create an index with a name too long ( 30 char).



tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata,
Column('id', Integer, Sequence('rischio_attivita_seq'), primary_key=True),
Column('cod_attivita', Unicode(10),nullable=False),
Column('cod_caratteristica_rischio', Unicode(10), index=True, 
nullable=False),

Column('tipo_calcolo', Integer),
Column('algoritmo', Unicode(200)),
Column('fattore_x', Float, PassiveDefault('1.0')),
ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']),
ForeignKeyConstraint(['cod_caratteristica_rischio'], 
['caratteristica_rischio.codice'])

)


File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 
599, in _execute

raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00972: identifier is 
too long
'CREATE INDEX ix_rischio_attivita_cod_caratteristica_rischio ON 
rischio_attivita (cod_caratteristica_rischio)' {}





Hi there, instead of creating the index as part of the Column
expression, use the Index[1] statement and assign a name yourself.

[1]
http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalchemy.schema.Index

  


--
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] ArgumentError: Only '='/'!=' operators can be used with NULL

[jo]

Hi all,

I'm trying to solve this error...

File 
/home/sfera/release/sicer/BASE/controller/controlli/sopralluogo.py, 
line 645, in verifiche
Piano.c.data_inizio =data.get('data_sop') ,
File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 1294, in 
__le__
return self._compare('=', other)
File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 1423, in 
_compare
raise exceptions.ArgumentError(Only '='/'!=' operators can be used with 
NULL)
ArgumentError: Only '='/'!=' operators can be used with NULL

-

here the code that originates the above error:

sr = sa.and_(Piano.c.data_inizio =data.get('data_sop') )

I suppose data.get('data_sop') returns a None value, thus, sa compiles 
the query like so:

where piano.data_inizio = NULL

Could someone give me, some suggest how to solve this problem?
Thank you

j

-- 
Jose Soares
Sferacarta Net 
Via Bazzanese 69
40033 Casalecchio di Reno
Bologna - Italy
Ph  +39051591054
fax +390516131537
web:www.sferacarta.com

Le informazioni contenute nella presente mail ed in ogni eventuale file 
allegato sono riservate e, comunque, destinate esclusivamente alla persona o 
ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La 
diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di 
qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, 
l’integrità e la sicurezza della presente mail non possono essere garantite. Se 
avete ricevuto questa mail per errore, Vi preghiamo di contattarci 
immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to 
legislative decree 30 June 2003, n. 196. It may contain confidential or 
privileged information. You should not copy or use it to disclose its contents 
to any other person. Transmission cannot be guaranteed to be error-free, 
complete and secure. If you are not the intended recipient and receive this 
communication unintentionally, please inform us immediately and then delete 
this message from your system. Thank you.

--

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] ArgumentError: Only '='/'!=' operators can be used with NULL

[jo]

Mike Conley wrote:
 On Tue, Dec 22, 2009 at 2:34 AM, jo jose.soa...@sferacarta.com 
 mailto:jose.soa...@sferacarta.com wrote:

 Hi all,

 I'm trying to solve this error...

 File
 /home/sfera/release/sicer/BASE/controller/controlli/sopralluogo.py,
 line 645, in verifiche
 Piano.c.data_inizio =data.get('data_sop') ,
 File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line
 1294, in
 __le__
 return self._compare('=', other)
 File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line
 1423, in
 _compare
 raise exceptions.ArgumentError(Only '='/'!=' operators can be
 used with
 NULL)
 ArgumentError: Only '='/'!=' operators can be used with NULL

 -

 here the code that originates the above error:

 sr = sa.and_(Piano.c.data_inizio =data.get('data_sop') )

 I suppose data.get('data_sop') returns a None value, thus, sa compiles
 the query like so:

 where piano.data_inizio = NULL

 Could someone give me, some suggest how to solve this problem?
 Thank you


  This is correct behavior for a SQL query since the only legal 
 behavior for NULL is IS NULL or IS NOT NULL.

 First question to answer is what should your application do when 
 data_sop is NULL?
 If you want to treat NULL same as zero, use a case() function to force 
 null to zero in the comparison. If NULL should not participate at all, 
 put a !=None check first in the and_condition to skip rows with NULL 
 values. There are probably other alternatives, but it all comes down 
 to desired behavior when the column is NULL.
Thank you, for the clue, Mike.
j

--

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] self.data[i] index out of range

[jo]

Hia all,

I'm dogged by this error for months, 
could someone, please, explain me what it means and how to avoid it.


  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/attributes.py, line 
532, in __getitem__
return self.data[i]
IndexError: list index out of range

thanks for any help.

j


--~--~-~--~~~---~--~~
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] AttributeError: 'MetaData' object has no attribute 'engine'

[jo]

Hi all,

I'm trying migrate from 0.3.10 to 0.4 and there's the following error:

...   engine  = database.metadata.engine
AttributeError: 'MetaData' object has no attribute 'engine'

I was reading the section 0.3 to 0.4 Migration of  
http://www.sqlalchemy.org/docs/04/intro.html#overview_migration,
but sincerly I didn't understand where engine is rigth now.

-

this is my old code:
-
from turbogears import database
from sqlalchemy import *
from sqlalchemy.ext.assignmapperimport assign_mapper
from sqlalchemy.ext.selectresults   import SelectResultsExt
database.bind_meta_data()
session = database.session
engine  = database.metadata.engine
context = database.session.context

user=Table('user',database.metadata,
Column('code', Unicode(6),primary_key=True,nullable=False),
Column('name', Unicode(200)),
Column('address', Unicode(200)),
)

Please help me to migrate this code to 0.4.

j


--~--~-~--~~~---~--~~
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: changing instance identity

[jo]

Michael Bayer ha scritto:
 that is your own application changing the primary key of a loaded  
 instance, which is a feature very old versions of SQLAlchemy did not  
 support.  Upgrade to any recent version of 0.4 or 0.5 and you wont  
 have that issue anymore.
   
I think I don't want to change this behavior , Michael,
Simply I would like to realize that changes was done at current loaded 
instance
to avoid flush it.

Here another similar error when I try to flush an instance already 
flushed...

  File /usr/lib/python2.4/site-packages/sqlalchemy/ext/assignmapper.py, line 
7, in do
return getattr(query, name)(*args, **kwargs)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 114, in 
get
key = self.mapper.identity_key(ident)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py, line 992, 
in identity_key
return self.identity_key_from_primary_key(primary_key)
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py, line 959, 
in identity_key_from_primary_key
return (self.class_, tuple(util.to_list(primary_key)), self.entity_name)
TypeError: iteration over non-sequence


Is there any property in the instance to see its state before flushing it?

j


 On May 20, 2009, at 1:45 AM, jo wrote:

   
 Hello all,

 I have the following problem.
 While I'm working in my session, someone change my instance identity.

 I would like to avoid this.
 Is there a way to understand if was there any change before flushing?

 This is the message:


 File /usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py,  
 line 1078, in save_obj
raise exceptions.FlushError(Can't change the identity of  
 instance %s in session (existing identity: %s; new identity: %s) %  
 (mapperutil.instance_str(obj), obj._instance_key, instance_key))
 FlushError: Can't change the identity of instance spe...@0x5cc4590  
 in session
 (existing identity: (class  
 'sicer.BASE.model.tabelleCodifica.specie.Specie', (u'0141',), None);
 new identity: (class  
 'sicer.BASE.model.tabelleCodifica.specie.Specie', (u'08',), None))


 thank you for any help

 j


 


 
   


--~--~-~--~~~---~--~~
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: changing instance identity

[jo]

Glauco wrote:
 jo ha scritto:
   
 Michael Bayer ha scritto:
   
 
 that is your own application changing the primary key of a loaded  
 instance, which is a feature very old versions of SQLAlchemy did not  
 support.  Upgrade to any recent version of 0.4 or 0.5 and you wont  
 have that issue anymore.
   
 
   
 I think I don't want to change this behavior , Michael,
 Simply I would like to realize that changes was done at current loaded 
 instance
 to avoid flush it.

   
 
 cut

 Yes you have it,
 cls._state['original'].data

 but probably you must work over object stored in session


 Gla
   

cls._state['original'].data returns the instance data.

What I need instead is how to retrieve
the value of 'existing identity' and 'new identity' to compare them before
sa raises the exception FlushError.

Something like:

if  obj._instance_key  instance_key:  do_not_flush

to avoid the following error:

raise exceptions.FlushError(Can't change the identity of instance %s in 
session (existing identity: %s; new identity: %s) % 
(mapperutil.instance_str(obj), obj._instance_key, instance_key))
FlushError: Can't change the identity of instance spe...@0xa6e046c in 
session (existing identity: (class 
'sicer.BASE.model.tabelleCodifica.specie.Specie', (u'0141',), None); 
new identity: (class 'sicer.BASE.model.tabelleCodifica.specie.Specie', 
(u'08',), None))


--~--~-~--~~~---~--~~
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: changing instance identity

[jo]

Michael Bayer wrote:
 On May 21, 2009, at 2:04 AM, jo wrote:

   
 Michael Bayer ha scritto:
 
 that is your own application changing the primary key of a loaded
 instance, which is a feature very old versions of SQLAlchemy did not
 support.  Upgrade to any recent version of 0.4 or 0.5 and you wont
 have that issue anymore.

   
 Is there any property in the instance to see its state before  
 flushing it?
 

 here are the ways to check for changes, if thats what it is you're  
 looking for.  Most of them will still require that you upgrade from  
 your very old version of SQLAlchemy:

 obj in session.dirty
 session.is_modified(obj)

 from sqlalchemy.orm import attributes
 history = attributes.get_history(obj, attrname)

   
I see, Michael, you are right, I need to upgrade my SQLAlchemy version.

In the meanwhile I did a last try, to trap the FlushError exception.

raise exceptions.FlushError(Can't change the identity of instance %s in 
session (existing identity: %s; new identity: %s) % 
(mapperutil.instance_str(obj), obj._instance_key, instance_key))
FlushError: Can't change the identity of instance spe...@0x9f4b9ac in session 
(existing identity: (class 'sicer.BASE.model.tabelleCodifica.specie.Specie', 
(u'0141',), None); new identity: (class 
'sicer.BASE.model.tabelleCodifica.specie.Specie', (u'08',), None))


in this way:

from sqlalchemy.exceptions import FlushError
try:
 session.flush()
except FlushError:
 session.expunge(obj)

... but, it seems an error from TG. What exactly do session.expunge ?

Page handler: bound method Controller.save of 
sicer.BASE.controller.tabelleCodifica.specie.Controller instance at 
0x9e9f30c

Traceback (most recent call last):
  File /var/lib/python-support/python2.4/cherrypy/_cphttptools.py, line 105, 
in _run
self.main()
  File /var/lib/python-support/python2.4/cherrypy/_cphttptools.py, line 254, 
in main
body = page_handler(*virtual_path, **self.params)
  File string, line 3, in save
  File 
/usr/lib/python2.4/site-packages/TurboGears-1.0.3.2-py2.4.egg/turbogears/identity/conditions.py,
 line 235, in require
return fn(self, *args, **kwargs)
  File string, line 3, in save
  File 
/usr/lib/python2.4/site-packages/TurboGears-1.0.3.2-py2.4.egg/turbogears/controllers.py,
 line 342, in expose
output = database.run_with_transaction(
  File string, line 5, in run_with_transaction
  File 
/usr/lib/python2.4/site-packages/TurboGears-1.0.3.2-py2.4.egg/turbogears/database.py,
 line 362, in sa_rwt
retval = dispatch_exception(e,args,kw)
  File 
/usr/lib/python2.4/site-packages/TurboGears-1.0.3.2-py2.4.egg/turbogears/database.py,
 line 360, in sa_rwt
req.sa_transaction.commit()
  File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 73, 
in commit
t[1].commit()
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 670, 
in commit
raise exceptions.InvalidRequestError(This transaction is inactive)
InvalidRequestError: This transaction is inactive


j


--~--~-~--~~~---~--~~
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] changing instance identity

[jo]

Hello all,

I have the following problem.
While I'm working in my session, someone change my instance identity.

I would like to avoid this.
Is there a way to understand if was there any change before flushing?

This is the message:

 
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/mapper.py, line 1078, in 
save_obj
raise exceptions.FlushError(Can't change the identity of instance %s in 
session (existing identity: %s; new identity: %s) % 
(mapperutil.instance_str(obj), obj._instance_key, instance_key))
FlushError: Can't change the identity of instance spe...@0x5cc4590 in session 
(existing identity: (class 'sicer.BASE.model.tabelleCodifica.specie.Specie', 
(u'0141',), None); 
new identity: (class 'sicer.BASE.model.tabelleCodifica.specie.Specie', 
(u'08',), None))


thank you for any help

j


--~--~-~--~~~---~--~~
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: null() and notnull()

[jo]

Michael Bayer ha scritto:
 On Mar 19, 2009, at 12:34 PM, jo wrote:

   
 Michael Bayer wrote:
 
 why dont you just stick with None instead of nn ?   then you just
 write:

 if v is None:
clause.append(self.c.field != v)
 else:
   clause.append(self.c.field == v)
   
 It could be an idea but not intuitive and unnatural

 because None   = IS NOT NULL  (very ugly) :-(
 and null() = IS NULL
 

 well the logic you're doing above seems unnatural all by itself, that  
 if v is NULL you want a NOT NULL.  that would seem just as  
 unnaturual using null() as None.

 just make yourself a notnull = not_(null()) and problem solved.

   
the hint you suggested me doesn't work because SQLAlchemy translate it 
to  = (NOT NULL)  which is invalid sql...

print Tabella.search(tabella_id=not_(null())).compile()

SELECT tabella.id, ...
FROM tabella
WHERE tabella.id = (NOT NULL)

ERROR:  operator does not exist: integer = boolean
LINE 3: WHERE tabella.id = (NOT NULL);
 ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.

 
   


--~--~-~--~~~---~--~~
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] null() and notnull()

[jo]

Hi all,

I would like to know if there's a notnull() function in sqlalchemy 
similar to null()
to avoid things like not_(null()) ?

thank you

j


--~--~-~--~~~---~--~~
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: null() and notnull()

[jo]

Well, MIchael, in my case a notnull() function could be very interesting because
I'm using it in a function, and would like pass values as parameters in such 
way:


def search( self, **kw  ):
  by_where_clause = []
  for k,v in kw.items():
   if k == 'myfield1':
 if v == 'nn':   # I use 'nn' to generate a NOT NULL 
because we don't have a notnull() function
 by_where_clause.append( self.c.field1  None)
 else:
 by_where_clause.append( self.c.field1 == v)

   elif k == 'myfield2':
 if v == 'nn':   # I use 'nn' to generate a NOT NULL 
because we don't have a notnull() function
 by_where_clause.append( self.c.field2  None)
 else:
 by_where_clause.append( self.c.field2 == v)

   elif k == 'myfield3':
 if v == 'nn':   # I use 'nn' to generate a NOT NULL 
because we don't have a notnull() function
 by_where_clause.append( self.c.field3  None)
 else:
 by_where_clause.append( self.c.field3 == v)
   ...
 



Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
Mytb.search(myfield='nn') -- generates WHERE myfield IS NOT NULL



if we have a notnull() function these thing could be easier:

def search( self, **kw  ):
  by_where_clause = {}
  for k,v in kw.items():
 by_where_clause[ k ] = v


Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
Mytb.search(myfield=notnull()) -- generates WHERE myfield IS NOT NULL

Michael Bayer wrote:
 well usually null() and not_(null()) aren't needed as explicit  
 constructs.comparisons like somecol == None and somecol != None  
 will generate the appropriate NULL/NOT NULL expression.


 On Mar 19, 2009, at 4:48 AM, jo wrote:

   
 Hi all,

 I would like to know if there's a notnull() function in sqlalchemy
 similar to null()
 to avoid things like not_(null()) ?

 thank you

 j


 


 
   


--~--~-~--~~~---~--~~
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: null() and notnull()

[jo]

 

a...@svilendobrev.com wrote:
 def search( self, **kw  ):
   by_where_clause = {}
   for k,v in kw.items():
  by_where_clause[ k ] = v
 
 i guess u want to do query.filter_by(**by_where_clause) after that?
   
This is the final result, and I'm not problems about it.
what I'm looking for is a function which returns the opposite of null() 
to use as a function parameter

null() translates to 'IS NULL'
my need is a functions which translates to 'IS NOT NULL', something like 
not_null() / notnull()


j





 it's just a syntax sugar over .filter(). so

by_where_clause = []
for k,v in kw.items():
   col = getattr(self.c,k)
   if v == 'nn':   # I use 'nn' to generate a NOT NULL
  by_where_clause.append( c  None)
   else:
  by_where_clause.append( c == v)
query.filter(and_(*by_where_clause))
 that's 3 lines more ..

 svil

 On Thursday 19 March 2009 17:43:22 jo wrote:
   
 Well, MIchael, in my case a notnull() function could be very
 interesting because I'm using it in a function, and would like pass
 values as parameters in such way:


 def search( self, **kw  ):
   by_where_clause = []
   for k,v in kw.items():
if k == 'myfield1':
  if v == 'nn':   # I use 'nn' to generate a NOT
 NULL because we don't have a notnull() function
  by_where_clause.append( self.c.field1 
 None) else:
  by_where_clause.append( self.c.field1 ==
 v)

elif k == 'myfield2':
  if v == 'nn':   # I use 'nn' to generate a NOT
 NULL because we don't have a notnull() function
  by_where_clause.append( self.c.field2 
 None) else:
  by_where_clause.append( self.c.field2 ==
 v)

elif k == 'myfield3':
  if v == 'nn':   # I use 'nn' to generate a NOT
 NULL because we don't have a notnull() function
  by_where_clause.append( self.c.field3 
 None) else:
  by_where_clause.append( self.c.field3 ==
 v) ...




 Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
 Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
 Mytb.search(myfield='nn') -- generates WHERE myfield IS NOT NULL



 if we have a notnull() function these thing could be easier:

 def search( self, **kw  ):
   by_where_clause = {}
   for k,v in kw.items():
  by_where_clause[ k ] = v


 Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
 Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
 Mytb.search(myfield=notnull()) -- generates WHERE myfield IS NOT
 NULL

 Michael Bayer wrote:
 
 well usually null() and not_(null()) aren't needed as explicit
 constructs.comparisons like somecol == None and somecol !=
 None will generate the appropriate NULL/NOT NULL expression.

 On Mar 19, 2009, at 4:48 AM, jo wrote:
   
 Hi all,

 I would like to know if there's a notnull() function in
 sqlalchemy similar to null()
 to avoid things like not_(null()) ?

 thank you

 j
 
 


 
   


--~--~-~--~~~---~--~~
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: null() and notnull()

[jo]

Michael Bayer wrote:
 why dont you just stick with None instead of nn ?   then you just  
 write:

 if v is None:
 clause.append(self.c.field != v)
 else:
clause.append(self.c.field == v)
It could be an idea but not intuitive and unnatural

because None   = IS NOT NULL  (very ugly) :-(
and null() = IS NULL

j





 On Mar 19, 2009, at 11:43 AM, jo wrote:

   
 Well, MIchael, in my case a notnull() function could be very  
 interesting because
 I'm using it in a function, and would like pass values as parameters  
 in such way:


 def search( self, **kw  ):
  by_where_clause = []
  for k,v in kw.items():
   if k == 'myfield1':
 if v == 'nn':   # I use 'nn' to generate a NOT NULL
 because we don't have a notnull() function
 by_where_clause.append( self.c.field1  None)
 else:
 by_where_clause.append( self.c.field1 == v)

   elif k == 'myfield2':
 if v == 'nn':   # I use 'nn' to generate a NOT NULL
 because we don't have a notnull() function
 by_where_clause.append( self.c.field2  None)
 else:
 by_where_clause.append( self.c.field2 == v)

   elif k == 'myfield3':
 if v == 'nn':   # I use 'nn' to generate a NOT NULL
 because we don't have a notnull() function
 by_where_clause.append( self.c.field3  None)
 else:
 by_where_clause.append( self.c.field3 == v)
   ...




 Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
 Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
 Mytb.search(myfield='nn') -- generates WHERE myfield IS NOT NULL



 if we have a notnull() function these thing could be easier:

 def search( self, **kw  ):
  by_where_clause = {}
  for k,v in kw.items():
 by_where_clause[ k ] = v


 Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
 Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
 Mytb.search(myfield=notnull()) -- generates WHERE myfield IS NOT NULL

 Michael Bayer wrote:
 
 well usually null() and not_(null()) aren't needed as explicit
 constructs.comparisons like somecol == None and somecol != None
 will generate the appropriate NULL/NOT NULL expression.


 On Mar 19, 2009, at 4:48 AM, jo wrote:


   
 Hi all,

 I would like to know if there's a notnull() function in sqlalchemy
 similar to null()
 to avoid things like not_(null()) ?

 thank you

 j



 
   
 


 
   


--~--~-~--~~~---~--~~
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: MapperExtension before_update problem

[jo]

Great, now it works. :-)
Thank you very much, Michael.

j

Michael Bayer ha scritto:
 use the connection object passed to the before_update() method to  
 execute all SQL.   do not use implicit execution as you are doing  
 (i.e. the execute() method on ClauseElements).   the usage of multiple  
 connections is likely creating a deadlock, and in any case violates  
 the integrity of the transaction.


 On Mar 7, 2009, at 1:26 PM, jo wrote:

   
 Hi all,

 I have a strange behavior using MapperExtension before_update.
 I created two tables; anag and azi, azi have a foreign key to anag.
 When I try to change some column value on anag table the
 HistoryAnag.before_update is correctly triggered and it returns  
 EXT_PASS,
 but when I try to change some column value on both tables the
 HistoryAnag.before_update is correctly triggered and it returns  
 EXT_PASS
 then HistoryAzie.before_update is triggered but it stucks and it do  
 not
 returns EXT_PASS, it goes in loop somewhere.
 I'm using SQLAlchemy 0.3.10 and turbogears 1.0.3.2

 Is there someone experimenting some behavior?
 ---
  anag=Table('anag',database.metadata,
Column('id', Integer, Sequence('anag_seq'), primary_key=True,
 nullable=False),
Column('name', Unicode(200), nullable=False, index=True),
Column('id_prec', Integer),
Column('status', Unicode(1), PassiveDefault('A'))
 )

  azi=Table('azi',database.metadata,
Column('id', Integer, Sequence('azi_seq'), primary_key=True,
 nullable=False),
Column('id_anag', Integer, nullable=False),
Column('id_prec', Integer),
Column('status', Unicode(1), PassiveDefault('A')),
ForeignKeyConstraint(['id_anag'],['anag.id'])
)


 from sqlalchemy.orm import MapperExtension, EXT_PASS
 class HistoryAnag(MapperExtension):
def __init__(self):
MapperExtension.__init__(self)
self.methods = ('before_update',)

def before_update(self, mapper, connection, instance):
from turbogears import identity
rec = anag.select(anag.c.id ==
 instance.anag_id).execute().fetchone()
dd=dict(rec)
dd.pop('id',None)
dd['status'] = 'M'
ret = anag.insert(values=dd).execute()
lastid = ret.last_inserted_ids()[0]
instance.anag_id_prec = lastid
return EXT_PASS
 class Anag():
  pass
 assign_mapper(context,
  Anag,
  anag,
  extension=HistoryAnag()
  )

 class HistoryAzi(MapperExtension):
def __init__(self):
MapperExtension.__init__(self)
self.methods = ('before_update',)

def before_update(self, mapper, connection, instance):
from turbogears import identity
rec = azi.select(azi.c.id ==  
 instance.azi_id).execute().fetchone()
dd=dict(rec)
dd.pop('id',None)
dd['status'] = 'M'
ret = azi.insert(values=dd).execute()
lastid = ret.last_inserted_ids()[0]
instance.azi_id_prec = lastid
return EXT_PASS

 class Azi():
  pass
 assign_mapper(context,
  Azi,
  azi,
  extension=HistoryAzi()
  )

 Thank you for any help.
 j


 


 
   


--~--~-~--~~~---~--~~
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] MapperExtension before_update problem

[jo]

Hi all,

I have a strange behavior using MapperExtension before_update.
I created two tables; anag and azi, azi have a foreign key to anag.
When I try to change some column value on anag table the 
HistoryAnag.before_update is correctly triggered and it returns EXT_PASS,
but when I try to change some column value on both tables the 
HistoryAnag.before_update is correctly triggered and it returns EXT_PASS
then HistoryAzie.before_update is triggered but it stucks and it do not 
returns EXT_PASS, it goes in loop somewhere.
I'm using SQLAlchemy 0.3.10 and turbogears 1.0.3.2

Is there someone experimenting some behavior?
---
  anag=Table('anag',database.metadata,
Column('id', Integer, Sequence('anag_seq'), primary_key=True, 
nullable=False),
Column('name', Unicode(200), nullable=False, index=True),
Column('id_prec', Integer),
Column('status', Unicode(1), PassiveDefault('A'))
 )
 
  azi=Table('azi',database.metadata,
Column('id', Integer, Sequence('azi_seq'), primary_key=True, 
nullable=False),
Column('id_anag', Integer, nullable=False),
Column('id_prec', Integer),
Column('status', Unicode(1), PassiveDefault('A')),
ForeignKeyConstraint(['id_anag'],['anag.id'])
)


from sqlalchemy.orm import MapperExtension, EXT_PASS
class HistoryAnag(MapperExtension):
def __init__(self):
MapperExtension.__init__(self)
self.methods = ('before_update',)

def before_update(self, mapper, connection, instance):
from turbogears import identity
rec = anag.select(anag.c.id == 
instance.anag_id).execute().fetchone()
dd=dict(rec)
dd.pop('id',None)
dd['status'] = 'M'
ret = anag.insert(values=dd).execute()
lastid = ret.last_inserted_ids()[0]
instance.anag_id_prec = lastid
return EXT_PASS
class Anag():
  pass
assign_mapper(context,
  Anag,
  anag,
  extension=HistoryAnag()
  )
 
class HistoryAzi(MapperExtension):
def __init__(self):
MapperExtension.__init__(self)
self.methods = ('before_update',)

def before_update(self, mapper, connection, instance):
from turbogears import identity
rec = azi.select(azi.c.id == instance.azi_id).execute().fetchone()
dd=dict(rec)
dd.pop('id',None)
dd['status'] = 'M'
ret = azi.insert(values=dd).execute()
lastid = ret.last_inserted_ids()[0]
instance.azi_id_prec = lastid
return EXT_PASS

class Azi():
  pass
assign_mapper(context,
  Azi,
  azi,
  extension=HistoryAzi()
  )

Thank you for any help.
j


--~--~-~--~~~---~--~~
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] TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

[jo]

Hi all,

I wonder what this message mean:

TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed 
out, timeout 30

any ideas?

jo



--~--~-~--~~~---~--~~
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: DataError inserting CURRVAL in an inline query with SQLAlchemy and SQLSoup

[jo]

You cannot pass currval('users_user_id_seq') as a parameter value, you 
have to pass an integer value instead.
I solved this problem in this way:

INSERT INTO employees 

(employee_user_id, employee_id, employee_first_name,
employee_last_name, employee_address, employee_city, employee_state,
employee_zip, employee_extension) 

VALUES 

( currval('users_user_id_seq'), %(employee_id)s, %(employee_first_name)s, 
%(employee_last_name)s, %
(employee_address)s, %(employee_city)s, %(employee_state)s, %
(employee_zip)s, %(employee_extension)s)

% 

{'employee_first_name': u'Jeff', 'employee_city': u'Olathe', 'employee_state': 
u'KS',
'employee_address': u'150', 'employee_id': 3L, 'employee_extension':
u'1112', 'employee_user_id': nextval('users_user_id_seq'),
'employee_zip': u'66062', 'employee_last_name': u'Poller'
}


j


Jeff Cook wrote:
 Hi all,

 I want to use the return value of a CURRVAL call as the value of a
 column in a row I'm inserting, to link together related records. I'm
 using Pylons with SQLAlchemy and SQLSoup. SQLAlchemy spits back at me
 a DataError because I'm trying to place CURRVAL in an integer field.
 How do I get the thing to reference the integer instead of taking my
 words as the literal field value?

 This is the error I receive from Pylons:
 class 'sqlalchemy.exc.DataError': (DataError) invalid input syntax
 for integer: CURRVAL('users_user_id_seq') 'INSERT INTO employees
 (employee_id, employee_user_id, employee_first_name,
 employee_last_name, employee_address, employee_city, employee_state,
 employee_zip, employee_extension) VALUES (%(employee_id)s, %
 (employee_user_id)s, %(employee_first_name)s, %(employee_last_name)s, %
 (employee_address)s, %(employee_city)s, %(employee_state)s, %
 (employee_zip)s, %(employee_extension)s)' {'employee_first_name':
 u'Jeff', 'employee_city': u'Olathe', 'employee_state': u'KS',
 'employee_address': u'150', 'employee_id': 3L, 'employee_extension':
 u'1112', 'employee_user_id': 'CURRVAL(users_user_id_seq)',
 'employee_zip': u'66062', 'employee_last_name': u'Poller'}

 Thanks in advance. : )

 Signed
 Jeff

 
   


--~--~-~--~~~---~--~~
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: sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00942: table or view does not exist

[jo]

Michael Bayer ha scritto:
 On Jan 2, 2009, at 11:51 AM, jo wrote:

   
 Seems SA checks if table already exist in all_tables system table...
 and creates it only if it doesn't exist.

 In my case...

 SQL select table_name from all_tables where
 table_name='FUNZIONE_CALCOLO', returns:
 TABLE_NAME
 --
 FUNZIONE_CALCOLO

 SQL select table_name from all_tables where table_name='TARIFFA';
 no rows selected

 instead :

 SQL desc FUNZIONE_CALCOLO
 ERROR:
 ORA-04043: object FUNZIONE_CALCOLO does not exist

 SQL desc TARIFFA
 ERROR:
 ORA-04043: object TARIFFA does not exist
 ---
 probably it is because I droped the table funzione_calcolo.
 Is it regular that FUNZIONE_CALCOLO is in all_tables yet?
 

 no this indicates something is wrong on the Oracle side.   Perhaps a  
 table named funzione_calcolo exists under a different username ?
   
yes,  Michael ...

SQL select table_name,owner from all_tables where
table_name='FUNZIONE_CALCOLO', returns:
TABLE_NAMEOWNER
- --
FUNZIONE_CALCOLO  SYS
FUNZIONE_CALCOLO  JO

Sorry, I'm newbie to oracle.  :-[  


   

 
   


--~--~-~--~~~---~--~~
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] sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00942: table or view does not exist

[jo]

hi all,

I'm having problems with database.metadata.create_all.
The code works perfectly with PostgreSQL but with Oracle it 
doesn't...and it exits with the following error:

...
self._execute_raw(context)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 581, in _execute_raw
self._execute(context)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00942: table or view 
does not exist
 '\nCREATE TABLE tariffa (\n\tid INTEGER NOT NULL, 
\n\tcod_funzione_calcolo VARCHAR(5) NOT NULL, \n\tPRIMARY KEY (id), \n\t 
FOREIGN KEY(cod_funzione_calcolo) REFERENCES funzione_calcolo 
(codice)\n)\n\n' {}


here the source...
---
from turbogears import database
from sqlalchemy import *
from sqlalchemy.ext.assignmapperimport assign_mapper
from sqlalchemy.ext.selectresults   import SelectResultsExt
database.bind_meta_data()
session = database.session
engine  = database.metadata.engine
context = database.session.context
tbl = {}

tbl['funzione_calcolo']=Table('funzione_calcolo',database.metadata,
Column('codice', Unicode(5),nullable=False,primary_key=True),
Column('descrizione', Unicode(200), nullable=False),
)
tbl['tariffa']=Table('tariffa',database.metadata,
Column('id', Integer, Sequence('tariffa_seq'), nullable=False, 
primary_key=True),
Column('cod_funzione_calcolo', Unicode(5), nullable=False),

ForeignKeyConstraint(['cod_funzione_calcolo'],['funzione_calcolo.codice']),
)
database.metadata.create_all(engine)
---

what's wrong ?

jo

ps:
I'm using SA 0.3.10


--~--~-~--~~~---~--~~
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: sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00942: table or view does not exist

[jo]

Seems SA checks if table already exist in all_tables system table...
and creates it only if it doesn't exist.

In my case...

SQL select table_name from all_tables where 
table_name='FUNZIONE_CALCOLO', returns:
TABLE_NAME
--
FUNZIONE_CALCOLO

SQL select table_name from all_tables where table_name='TARIFFA';
no rows selected

instead :

SQL desc FUNZIONE_CALCOLO
ERROR:
ORA-04043: object FUNZIONE_CALCOLO does not exist

SQL desc TARIFFA
ERROR:
ORA-04043: object TARIFFA does not exist
---
probably it is because I droped the table funzione_calcolo.
Is it regular that FUNZIONE_CALCOLO is in all_tables yet?


-
2.4.4 (#2, Apr 15 2008, 23:43:20)
[GCC 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)]

select table_name from all_tables where table_name=:name

{'name': 'FUNZIONE_CALCOLO'}

select table_name from all_tables where table_name=:name

{'name': 'TARIFFA'}

CREATE TABLE tariffa (
id INTEGER NOT NULL,
cod_funzione_calcolo VARCHAR(5) NOT NULL,
PRIMARY KEY (id),
 FOREIGN KEY(cod_funzione_calcolo) REFERENCES funzione_calcolo 
(codice)
)



None

ROLLBACK
Traceback (most recent call last):
  File ./start-sicer.py, line 16, in ?
from sicer.controllers import Root
  File /home/sfera/release/sicer/controllers/__init__.py, line 3, in ?
from errorhandling   import ErrorCatcher
  File /home/sfera/release/sicer/controllers/errorhandling.py, line 
16, in ?
from sicer.model import Anagrafica
  File /home/sfera/release/sicer/model/__init__.py, line 1, in ?
from sql import *
  File /home/sfera/release/sicer/model/sql.py, line 21, in ?
database.metadata.create_all(engine)
  File /usr/lib/python2.4/site-packages/sqlalchemy/schema.py, line 
1239, in create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 699, in create
self._run_visitor(self.dialect.schemagenerator, entity, 
connection=connection, **kwargs)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 729, in _run_visitor
element.accept_visitor(visitorcallable(conn, **kwargs))
  File /usr/lib/python2.4/site-packages/sqlalchemy/schema.py, line 
1266, in accept_visitor
visitor.visit_metadata(self)
  File /usr/lib/python2.4/site-packages/sqlalchemy/ansisql.py, line 
798, in visit_metadata
table.accept_visitor(self)
  File /usr/lib/python2.4/site-packages/sqlalchemy/sql.py, line 2713, 
in accept_visitor
visitor.visit_table(self)
  File /usr/lib/python2.4/site-packages/sqlalchemy/ansisql.py, line 
833, in visit_table
self.execute()
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 1215, in execute
return self.connection.execute(self.buffer.getvalue())
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 517, in execute
return Connection.executors[c](self, object, *multiparams, **params)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 532, in execute_text
self._execute_raw(context)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 581, in _execute_raw
self._execute(context)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00942: table or view 
does not exist
 '\nCREATE TABLE tariffa (\n\tid INTEGER NOT NULL, 
\n\tcod_funzione_calcolo VARCHAR(5) NOT NULL, \n\tPRIMARY KEY (id), \n\t 
FOREIGN KEY(cod_funzione_calcolo) REFERENCES funzione_calcolo 
(codice)\n)\n\n' {}


jo ha scritto:
 hi all,

 I'm having problems with database.metadata.create_all.
 The code works perfectly with PostgreSQL but with Oracle it 
 doesn't...and it exits with the following error:

 ...
 self._execute_raw(context)
   File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
 line 581, in _execute_raw
 self._execute(context)
   File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, 
 line 599, in _execute
 raise exceptions.SQLError(context.statement, context.parameters, e)
 sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-00942: table or view 
 does not exist
  '\nCREATE TABLE tariffa (\n\tid INTEGER NOT NULL, 
 \n\tcod_funzione_calcolo VARCHAR(5) NOT NULL, \n\tPRIMARY KEY (id), \n\t 
 FOREIGN KEY(cod_funzione_calcolo) REFERENCES funzione_calcolo 
 (codice)\n)\n\n' {}


 here the source...
 ---
 from turbogears import database
 from sqlalchemy import *
 from sqlalchemy.ext.assignmapperimport assign_mapper
 from sqlalchemy.ext.selectresults   import SelectResultsExt
 database.bind_meta_data()
 session = database.session
 engine  = database.metadata.engine
 context = database.session.context
 tbl

[sqlalchemy] Re: Variable_TypeByValue(): unhandled data type unicode

[jo]

Michael Bayer ha scritto:
 Look into using the Unicode type for python unicode values.

 http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html?highlight=unicode#sqlalchemy.Unicode
   

This is the 0.5 documentation
I'm using SQLAlchemy version 0.3.10.
and cx_Oracle 4.3.1

Seems cx_Oracle isn't able to convert unicode values into integer.
Take a look:

In [32]: print Azienda.get(1).azienda_id
1


In [33]: print Azienda.id(u'1').azienda_id
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data 
type unicode 'SELECT azienda.id AS azienda_id  \nFROM azienda \nWHERE 
azienda.id = :azienda_id ORDER BY azienda.rowid' {'azienda_id': u'1'}

j
 

 On Dec 11, 2008, at 12:01 PM, jo wrote:

   
 Hi all,

 I'm using SA with cx_Oracle. In some queries it raises the following  
 error:


 File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py,  
 line 581, in _execute_raw
self._execute(context)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py,  
 line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
 SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data  
 type unicode
 'SELECT comune.data_fine AS comune_data_fine, comune.id AS  
 comune_id, comune.auto_sync_bdn AS comune_auto_sync_bdn,
 comune.cod_provincia AS comune_cod_provincia, comune.istat AS  
 comune_istat, comune.data_inizio AS comune_data_inizio,
 comune.cap AS comune_cap, comune.codice_erariale AS  
 comune_codice_erariale, comune.bdn_id AS comune_bdn_id,
 comune.nome AS comune_nome
 \nFROM comune
 \nWHERE comune.id = :comune_id ORDER BY comune.rowid' {'comune_id':  
 u'2895'}


 Someone could help me?

 j



 


 
   


--~--~-~--~~~---~--~~
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: Variable_TypeByValue(): unhandled data type unicode

[jo]

Michael Bayer ha scritto:
 On Dec 12, 2008, at 5:19 AM, jo wrote:

   
 Michael Bayer ha scritto:
 
 Look into using the Unicode type for python unicode values.

 http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html?highlight=unicode#sqlalchemy.Unicode

   
 This is the 0.5 documentation
 I'm using SQLAlchemy version 0.3.10.
 and cx_Oracle 4.3.1

 Seems cx_Oracle isn't able to convert unicode values into integer.
 Take a look:

 In [32]: print Azienda.get(1).azienda_id
 1


 In [33]: print Azienda.id(u'1').azienda_id
 SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
 type unicode 'SELECT azienda.id AS azienda_id  \nFROM azienda \nWHERE
 azienda.id = :azienda_id ORDER BY azienda.rowid' {'azienda_id': u'1'}
 


 0.3 has the Unicode type as well, the same usage rules apply.

 However in this case you are supplying a string for an integer value  
 in a query.  you have to supply an int, not a string.
   
Yes, I'm supplying a value directly from the url which is a text value 
as in:

www.myserver/azienda?id=1

I haven't this problem using the postgres adapter because psycopg 
casting automatically the value,
instead cx_Oracle needs an explicit casting.
I wonder if there's some parameter to tell cx_Oracle to do it in implict 
way.
 
j





 
   


--~--~-~--~~~---~--~~
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: Oracle sql syntax

[jo]

You are right, Michael,
1=0 works.
thank you,
j

Michael Bayer ha scritto:
 I'd try 1 = 0  I dont think oracle has boolean keywords at least  
 last I checked...


 On Dec 11, 2008, at 4:09 AM, jo wrote:

   
 Hi all,

 I'm trying sqlalchemy with Oracle. Seems Oracle doesn't understand  
 the syntax  True = False

 ...
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py,  
 line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
 SQLError: (DatabaseError) ORA-00904: FALSE: invalid identifier
 'SELECT count(permesso.codice) \nFROM permesso \nWHERE True =  
 False' {}


 any ideas?

 j



 


 
   


--~--~-~--~~~---~--~~
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] Variable_TypeByValue(): unhandled data type unicode

[jo]

Hi all,

I'm using SA with cx_Oracle. In some queries it raises the following error:


 File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 581, 
in _execute_raw
self._execute(context)
  File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 599, 
in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data type 
unicode 
'SELECT comune.data_fine AS comune_data_fine, comune.id AS comune_id, 
comune.auto_sync_bdn AS comune_auto_sync_bdn, 
comune.cod_provincia AS comune_cod_provincia, comune.istat AS comune_istat, 
comune.data_inizio AS comune_data_inizio, 
comune.cap AS comune_cap, comune.codice_erariale AS comune_codice_erariale, 
comune.bdn_id AS comune_bdn_id, 
comune.nome AS comune_nome 
\nFROM comune 
\nWHERE comune.id = :comune_id ORDER BY comune.rowid' {'comune_id': u'2895'}


Someone could help me?

j



--~--~-~--~~~---~--~~
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: case_sensitive

[jo]

Maybe I'm using the 'case_sensitive' in a wrong way.
Here what I want to reach :

create unique index myname on mytable (lower(mycolumn));

How can I create it on sqlalachemy?

j


Glauco ha scritto:
 jo ha scritto:
   
 Hi all,

 Trying to migrate from 0.3.10 to 0.5 I have this error:

 sqlalchemy.exc.ArgumentError: Unknown UniqueConstraint argument(s):
 'case_sensitive'

 how can I define the case_sensitive=True for a unique constraint?

 thank you,
 j
   
 
 http://www.sqlalchemy.org/trac/browser/sqlalchemy/tags/rel_0_4_8/CHANGES


 case_sensitive=(True|False) setting removed from schema items, since
 checking this state added a lot of method call overhead and there was no
 decent reason to ever set it to False.  Table and column names which are
 all lower case will be treated as case-insenstive (yes we adjust for
 Oracle's UPPERCASE style too).


 Glauco

   


--~--~-~--~~~---~--~~
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('last_updated', onupdate=func.current_timestamp())

[jo]

Hi all,

I created a table with the following column:

Column('last_updated', DateTime, PassiveDefault(func.current_timestamp
()), onupdate=func.current_timestamp())

Maybe I don't understand how onupdate works.
I would like to have this column to be changed every time the row is
updated,
but it doesn't work.

thank you for any help

j







--~--~-~--~~~---~--~~
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] func.max()

[jo]

Hi all,

How would I build the following query using SA?


select max(numero) from bolletta where anno=2006;

this is my table

tbl=[]
tbl[name] = Table(name, database.metadata, autoload=True)
class Bolletta(DomainObject):
def __str__(self):
return self.numero or repr(self)
assign_mapper(context, Bolletta, tbl['bolletta'])

jo


--~--~-~--~~~---~--~~
 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
-~--~~~~--~~--~--~---