Re: [sqlalchemy] python customer function
thanks for point me to this docs, Jonathan, I'm going to take a look at it. j On 08/05/2015 23:30, Jonathan Vanasco wrote: Would you be able to use a TypeDecorator? http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator That will allow you to define a function for handling how sqlalchemy inserts and accesses the data from sql. -- 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. -- 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] python customer function
Hi Mike, thanks to reply my question. In my case the extract function is unuseful because it needs a datetime to extract parts of it but I don't have a datetime but a codified date. I need to manage a string which is a personal code with info about name, birthday, birth place and gender. Infact the birth month is represented by a single letter (not in sequential order) A=jan B=feb C=mar D=apr E=may F= - G= - H=jun I= - J= - K= - L=jul M=aug N= - O= - P=sep Q= - R=oct S=nov T=dec the day contains also the gender, (female is incremented of 40) the birth year has only 2 digits. Examples: 60R11 = 1960-10-11 male 60R51 = 1960-10-11 female 00T41 = 1900-12-01 female 00T01 = 1900-12-01 male I'm looking for a function to decode such string. I thought I could create a python function to manage it. j On 07/05/2015 22:01, Mike Bayer wrote: On 5/7/15 9:10 AM, jo wrote: 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? look into using the extract() function: http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html?highlight=extract#sqlalchemy.sql.expression.extract it mostly works the same on all backends, here's PG's docs: http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT 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 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. -- 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. -- 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] is True vs ==True
Hi all, While I changed some obsolete syntax as defined in (https://www.python.org/dev/peps/pep-0008/) like (is True instead of ==True) also False and None. I realized that sqlalchemy do not support them What can I do to avoid this behavior? -- print session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo == True) SELECT caratteristica_rischio.codice AS caratteristica_rischio_codice FROM caratteristica_rischio WHERE caratteristica_rischio.peso_gruppo = true print session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo is True) SELECT caratteristica_rischio.codice AS caratteristica_rischio_codice FROM caratteristica_rischio WHERE false 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] is True vs ==True
Hmm! in this case we must distinguish between the python syntax and the sqlalchemy syntax.:-( j On 30/03/2015 12:37, Simon King wrote: On Mon, Mar 30, 2015 at 10:59 AM, Jose Soares jose.soa...@sferacarta.com wrote: Hi all, While I changed some obsolete syntax as defined in (https://www.python.org/dev/peps/pep-0008/) like (is True instead of ==True) also False and None. I realized that sqlalchemy do not support them What can I do to avoid this behavior? -- print session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo == True) SELECT caratteristica_rischio.codice AS caratteristica_rischio_codice FROM caratteristica_rischio WHERE caratteristica_rischio.peso_gruppo = true print session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo is True) SELECT caratteristica_rischio.codice AS caratteristica_rischio_codice FROM caratteristica_rischio WHERE false I don't think you can. SQLAlchemy expressions define an __eq__ method to enable expression == value-style constructs. There is no equivalent hook in Python for the is operator, so there is no way SQLAlchemy could use it. Simon -- 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] is True vs ==True
Yeah, this is a good solution. Thanks for the tip. j On 30/03/2015 16:33, Richard Gerd Kuesters | Pollux wrote: there's this: * import sqlalchemy as sa** ** sa.sql.null()** **sqlalchemy.sql.elements.Null object at 0x7f8a70065c50** ** sa.sql.false()** **sqlalchemy.sql.elements.False_ object at 0x7f8a6fb13050** ** sa.sql.true()** **sqlalchemy.sql.elements.True_ object at 0x7f8a70065c50** * so, you can still use __eq__ and pep-8 will not complain, because there's no way to implement is True or is None. then you'll have this: *session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo == sa.**sql.true()**)* :) On 03/30/2015 10:52 AM, Jose Soares wrote: Hmm! in this case we must distinguish between the python syntax and the sqlalchemy syntax.:-( j On 30/03/2015 12:37, Simon King wrote: On Mon, Mar 30, 2015 at 10:59 AM, Jose Soares jose.soa...@sferacarta.com wrote: Hi all, While I changed some obsolete syntax as defined in (https://www.python.org/dev/peps/pep-0008/) like (is True instead of ==True) also False and None. I realized that sqlalchemy do not support them What can I do to avoid this behavior? -- print session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo == True) SELECT caratteristica_rischio.codice AS caratteristica_rischio_codice FROM caratteristica_rischio WHERE caratteristica_rischio.peso_gruppo = true print session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo is True) SELECT caratteristica_rischio.codice AS caratteristica_rischio_codice FROM caratteristica_rischio WHERE false I don't think you can. SQLAlchemy expressions define an __eq__ method to enable expression == value-style constructs. There is no equivalent hook in Python for the is operator, so there is no way SQLAlchemy could use it. Simon -- Richard Gerd Kuesters Pollux Automation rich...@pollux.com.br mailto:rich...@pollux.com.br | www.pollux.com.br http://www.pollux.com.br •Linhas de Montagem •Inspeção e Testes •Robótica •Identificação e Rastreabilidade •Software para Manufatura -- 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. -- 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] DefaultClause
I see, thus, this definition:
Column('abc', Unicode(20), server_default='abc')
Column('adef', Numeric(12,3), server_default=text('1.5')),
is equivalent to this one:
Column('abc', Unicode(20), DefaultClause('abc'))
Column('def', Numeric(12,3), DefaultClause(text('1.5'))),
But what about ColumnDefault and default as explained at:
http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html#server-side-defaults
(Scalar Defaults and Default Objects API)
I tried both, but I can't understand how it works.
I tried this definition:
test = Table('test', database.metadata,
Column('def', Integer, ColumnDefault(12)),
Column('rbc', Integer, default=12),
)
which produce this table (with no defaults):
CREATE TABLE test (
def INTEGER,
rbc INTEGER
)
j
On 01/09/2015 04:55 PM, Michael Bayer wrote:
you can use DefaultClause if you give it a text() object so that the quoting
behavior is defined. server_default is a shortcut for that.
jo jose.soa...@sferacarta.com wrote:
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 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.
To post to this group, send email to sqlal...@googlegroups.com.
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.
--
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
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+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.
--
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] UNION howto
Hi all, Could someone help me to define in sqlalchemy the following query: sql=SELECT count(*) FROM (SELECT cod_sticker AS bruciato FROM scadenziario UNION SELECT cod_sticker AS bruciato FROM sopralluogo UNION SELECT sticker_checklist AS bruciato FROM sopralluogo UNION SELECT protocollo AS bruciato FROM prestazione) AS foo WHERE bruciato='E1212'; thank 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.
[sqlalchemy] VARCHAR(None CHAR)
Hi all,
I have a query generated by sqlalchemy like this:
SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || '/' || CAST(fattura_master.anno AS
VARCHAR(None CHAR)) || '/' || CAST(fattura_master.numero AS VARCHAR(None
CHAR)) AS pk FROM fattura_master;
which generate the following error:
DatabaseError: (DatabaseError) ORA-00910: specified length too long for
its datatype
I can't understand the syntax: VARCHAR(None CHAR). I suppose the error
is generated by it. Isn't it?
It workd fine in PostgreSQL:
Take a look:
-
using PostgreSQL:
In [1]: qry = session.query(Master.c.tipo_documento,
(Master.c.sezionale+'|'+sa.cast(Master.c.anno,sa.String)+'|'+sa.cast(Master.c.numero,sa.String)).label('pk'))
In [2]: print qry
SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || %(sezionale_1)s || CAST(fattura_master.anno
AS VARCHAR) || %(param_1)s || CAST(fattura_master.numero AS VARCHAR) AS pk
FROM fattura_master
In [3]: qry.count()
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/default.py:518:
SAWarning: Unicode type received non-unicode bind param value.
param[key.encode(encoding)] = processors[key](compiled_params[key])
SELECT count(1) AS count_1
FROM (SELECT fattura_master.tipo_documento AS
fattura_master_tipo_documento, fattura_master.sezionale ||
%(sezionale_1)s || CAST(fattura_master.anno AS VARCHAR) || %(param_1)s
|| CAST(fattura_master.numero AS VARCHAR) AS pk
FROM fattura_master) AS anon_1
{'sezionale_1': '|', 'param_1': '|'}
Out[4]: 63195L
--
using Oracle:
In [1]: qry = session.query(Master.c.tipo_documento,
(Master.c.sezionale+'|'+sa.cast(Master.c.anno,sa.String)+'|'+sa.cast(Master.c.numero,sa.String)).label('pk'))
In [2]: print qry
SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || :sezionale_1 || CAST(fattura_master.anno AS
VARCHAR(None CHAR)) || :param_1 || CAST(fattura_master.numero AS
VARCHAR(None CHAR)) AS pk
FROM fattura_master
In [3]: qry.count()
DatabaseError: (DatabaseError) ORA-00910: specified length too long for
its datatype
'SELECT count(1) AS count_1 \nFROM (SELECT
fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || :sezionale_1 || CAST(fattura_master.anno AS
VARCHAR(None CHAR)) || :param_1 || CAST(fattura_master.numero AS
VARCHAR(None CHAR)) AS pk \nFROM fattura_master) anon_1' {'sezionale_1':
'|', 'param_1': '|'}
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/groups/opt_out.
Re: [sqlalchemy] VARCHAR(None CHAR)
Ok. It works,
thanks, Michael
j
On 11/22/2013 04:22 PM, Michael Bayer wrote:
On Nov 22, 2013, at 4:50 AM, Jose Soares jose.soa...@sferacarta.com wrote:
Hi all,
I have a query generated by sqlalchemy like this:
SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || '/' || CAST(fattura_master.anno AS VARCHAR(None
CHAR)) || '/' || CAST(fattura_master.numero AS VARCHAR(None CHAR)) AS pk FROM
fattura_master;
which generate the following error:
DatabaseError: (DatabaseError) ORA-00910: specified length too long for its
datatype
I can't understand the syntax: VARCHAR(None CHAR). I suppose the error is
generated by it. Isn't it?
seems like even in master it’s still doing that so this is a new issue
http://www.sqlalchemy.org/trac/ticket/2870.
give it a length for now.
cast(x, String(100))
It workd fine in PostgreSQL:
Take a look:
-
using PostgreSQL:
In [1]: qry = session.query(Master.c.tipo_documento,
(Master.c.sezionale+'|'+sa.cast(Master.c.anno,sa.String)+'|'+sa.cast(Master.c.numero,sa.String)).label('pk'))
In [2]: print qry
SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || %(sezionale_1)s || CAST(fattura_master.anno AS
VARCHAR) || %(param_1)s || CAST(fattura_master.numero AS VARCHAR) AS pk
FROM fattura_master
In [3]: qry.count()
/home/jose/buildout/eggs/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/engine/default.py:518:
SAWarning: Unicode type received non-unicode bind param value.
param[key.encode(encoding)] = processors[key](compiled_params[key])
SELECT count(1) AS count_1
FROM (SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || %(sezionale_1)s || CAST(fattura_master.anno AS
VARCHAR) || %(param_1)s || CAST(fattura_master.numero AS VARCHAR) AS pk
FROM fattura_master) AS anon_1
{'sezionale_1': '|', 'param_1': '|'}
Out[4]: 63195L
--
using Oracle:
In [1]: qry = session.query(Master.c.tipo_documento,
(Master.c.sezionale+'|'+sa.cast(Master.c.anno,sa.String)+'|'+sa.cast(Master.c.numero,sa.String)).label('pk'))
In [2]: print qry
SELECT fattura_master.tipo_documento AS fattura_master_tipo_documento,
fattura_master.sezionale || :sezionale_1 || CAST(fattura_master.anno AS
VARCHAR(None CHAR)) || :param_1 || CAST(fattura_master.numero AS VARCHAR(None
CHAR)) AS pk
FROM fattura_master
In [3]: qry.count()
DatabaseError: (DatabaseError) ORA-00910: specified length too long for its
datatype
'SELECT count(1) AS count_1 \nFROM (SELECT fattura_master.tipo_documento AS
fattura_master_tipo_documento, fattura_master.sezionale || :sezionale_1 ||
CAST(fattura_master.anno AS VARCHAR(None CHAR)) || :param_1 ||
CAST(fattura_master.numero AS VARCHAR(None CHAR)) AS pk \nFROM fattura_master)
anon_1' {'sezionale_1': '|', 'param_1': '|'}
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/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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] distinct on
Thanks for reply, Mariano. j On 05/23/2013 12:37 PM, Mariano Mara wrote: On 05/23/2013 04:42 AM, jo wrote: |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 Yes, it is entirely possible. Something like this should do the trick (not tested): from sqlalchemy import select, func from sqlalchemy.sql.expression import over q = select([tmp.c.id.distinct(), over(func.first_value(tmp.c.cid), partition_by=tmp.c.id, order_by=tmp.c.name.asc())]) print(q) SELECT DISTINCT user.id, first_value(user.cid) OVER (PARTITION BY user.id ORDER BY user.name ASC) AS anon_1 FROM user This chapter of the documentation will help with these features and much more: http://docs.sqlalchemy.org/en/latest/core/expression_api.html -- 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] query.filter AND ( ... OR ... OR ) how to?
Hi all, I'm trying to compile a query to avoid Oracle limit of 1000 in IN(): def chunks(l, n): Yield successive n-sized chunks from l. for i in xrange(0, len(l), n): yield l[i:i+n] qry=session.query(Azienda).fiter(Azienda.c.cap=='') val=[1,3,3,4,3,23,2,4,5,6,7,8,9,90,34,2] for jj in list(chunks(val, 5)): qry = qry.filter(sa.or_(Azienda.c.id.in_( jj ))) I expected a query like this one: SELECT * FROM azienda WHERE cap = '' AND ( azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s) OR azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s) OR azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s, %(id_15)s) OR azienda.id IN (%(id_16)s) ) instead I got this one: SELECT * FROM azienda WHERE azienda.cap = %(cap)s AND azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s) AND azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s) AND azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s, %(id_15)s) AND azienda.id IN (%(id_16)s) How can I do this in the right way? thanks for your 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] query.filter AND ( ... OR ... OR ) how to?
It works, thanks Simon. j On 01/23/2013 12:53 PM, Simon King wrote: On Wed, Jan 23, 2013 at 11:30 AM, Jose Soares jose.soa...@sferacarta.com wrote: Hi all, I'm trying to compile a query to avoid Oracle limit of 1000 in IN(): def chunks(l, n): Yield successive n-sized chunks from l. for i in xrange(0, len(l), n): yield l[i:i+n] qry=session.query(Azienda).fiter(Azienda.c.cap=='') val=[1,3,3,4,3,23,2,4,5,6,7,8,9,90,34,2] for jj in list(chunks(val, 5)): qry = qry.filter(sa.or_(Azienda.c.id.in_( jj ))) I expected a query like this one: SELECT * FROM azienda WHERE cap = '' AND ( azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s) OR azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s) OR azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s, %(id_15)s) OR azienda.id IN (%(id_16)s) ) instead I got this one: SELECT * FROM azienda WHERE azienda.cap = %(cap)s AND azienda.id IN (%(id_1)s, %(id_2)s, %(id_3)s, %(id_4)s, %(id_5)s) AND azienda.id IN (%(id_6)s, %(id_7)s, %(id_8)s, %(id_9)s, %(id_10)s) AND azienda.id IN (%(id_11)s, %(id_12)s, %(id_13)s, %(id_14)s, %(id_15)s) AND azienda.id IN (%(id_16)s) How can I do this in the right way? thanks for your help. j Successive calls to query.filter() always use AND. Each call to query.filter() further restricts the results that would be returned from the query. The or_ function is meant to take multiple conditions and OR them together, so something like this should do what you want: conditions = [] for jj in list(chunks(val, 5)): conditions.append(Azienda.c.id.in_( jj )) qry = qry.filter(sa.or_(*conditions)) Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] NotSupportedError
Yes, now it works, thanks a lot Michael. :-)
j
Michael Bayer wrote:
func.cast() is not correct. Use the cast() function which handles this special
syntax:
from sqlalchemy import cast, Integer
from sqlalchemy.sql import column
from sqlalchemy.dialects import oracle
print cast(column('x'), Integer).compile(dialect=oracle.dialect())
CAST(x AS INTEGER)
On Apr 18, 2012, at 10:30 AM, jo wrote:
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.
--
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
Michael Bayer wrote: On Dec 17, 2011, at 1:52 AM, jo wrote: 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 er, without the /db or port, host becomes the tns name, as in the example: sqlalchemy.dburi=oracle://user:password@SHELL SHELL is a tns name, cx_oracle looks it up in the local tnsnames file to get all the correct connection information. I tried as you said Michael and this is the error message: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12154: TNS:could not resolve the connect identifier specified I tried like so: sqlalchemy.dburi=oracle://user:password@SHELL and I also tried using the port number: sqlalchemy.dburi=oracle://user:password@SHELL:1521 but the error is always the same. 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
Michael Bayer wrote:
On Dec 19, 2011, at 3:28 AM, jose soares wrote:
I tried as you said Michael and this is the error message:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12154: TNS:could not resolve
the connect identifier specified
I tried like so:
sqlalchemy.dburi=oracle://user:password@SHELL
and I also tried using the port number:
sqlalchemy.dburi=oracle://user:password@SHELL:1521
but the error is always the same.
j
OK, but if you connect with cx_oracle:
cx_Oracle.connect(user='user', password='password', dsn='SHELL')
then it connects ? it should be the exact same thing.
not in this way:
cx_Oracle.connect(user='user', password='password', dsn='SHELL')
but in this way:
cx_Oracle.connect(user='user', password='password',
dsn=cx_Oracle.makedsn('myhost',1521,'SHELL').replace('SID','SERVICE_NAME'))
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
Michael Bayer wrote:
On Dec 19, 2011, at 10:17 AM, jose soares wrote:
Michael Bayer wrote:
On Dec 19, 2011, at 3:28 AM, jose soares wrote:
I tried as you said Michael and this is the error message:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12154: TNS:could not resolve
the connect identifier specified
I tried like so:
sqlalchemy.dburi=oracle://user:password@SHELL
and I also tried using the port number:
sqlalchemy.dburi=oracle://user:password@SHELL:1521
but the error is always the same.
j
OK, but if you connect with cx_oracle:
cx_Oracle.connect(user='user', password='password', dsn='SHELL')
then it connects ? it should be the exact same thing.
not in this way:
cx_Oracle.connect(user='user', password='password', dsn='SHELL')
but in this way:
cx_Oracle.connect(user='user', password='password',
dsn=cx_Oracle.makedsn('myhost',1521,'SHELL').replace('SID','SERVICE_NAME'))
this is weird. What's in your tnsnames.ora ?Is there the chance that just
modifying tnsnames.ora or adding a new entry would solve this ?
Sorry for all the questions I just need to determine what SQLAlchemy should do
here, either add some new connect styles or just tell users to modify their tns
files
this is my tnsnames.ora:
# 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_SICER =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.net )(PORT = 1521))
SFERACARTA_CONN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost.net )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SHELL))
)
--
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
Michael Bayer wrote: On Dec 19, 2011, at 10:43 AM, jose soares wrote: this is my tnsnames.ora: # 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_SICER = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.net )(PORT = 1521)) SFERACARTA_CONN = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.net )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SHELL)) ) so if you added (SID = SHELL) underneath the SERVICE_NAME, what happens ? I've used Oracle for well over twelve years and still have no clue what the difference between SERVICE_NAME and SID is. if I change SERVICE_NAME with SID the error returns: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor How SID is different from SERVICE_NAME in oracle tnsnames.ora? In short: SID = the unique name of your DB, Service_name = the alias used when connecting http://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora -- 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
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 /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/strategies.py, line 82, in connect return dialect.connect(*cargs, **cparams) File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/default.py, line 249, in connect return self.dbapi.connect(*cargs, **cparams) sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor None None Michael Bayer wrote: On Dec 13, 2011, at 11:16 AM, jose soares wrote: Hi all, I'm trying to connect to an oracle db using sqlalchemy with turbogears1 and I get this error: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor I tried making the connection using cs_Oracle and it works: import cx_Oracle as cx conn = USERNAME/passw...@myserver.net/SID cc = cx.connect(conn) cc.version '11.2.0.3.0' cc.close() You aren't showing us how you're connecting with SQLAlchemy, not even the URL you're using, so it's difficult to say what the problem is. The URL for the above would be: oracle://username:passw...@myserver.net/sid if you still get an error then ensure ORACLE_HOME and such are set correctly when the program runs. Here is an actual demonstration: Python 2.7 (r27:82500, Sep 16 2010, 18:03:06) [GCC 4.5.1 20100907 (Red Hat 4.5.1-3)] on linux2 Type help, copyright, credits or license for more information. import cx_Oracle import sqlalchemy e = sqlalchemy.create_engine(oracle://scott:tiger@localhost/xe) c = e.connect() c2 = cx_Oracle.connect(scott/tiger@localhost/xe) c.scalar(select 1 from dual) 1 cursor = c2.cursor(); cursor.execute(select 1 from dual); cursor.fetchall() __builtin__.OracleCursor on cx_Oracle.Connection to scott@localhost/xe [(1,)] but sa ... File ./start-sicer.py, line 14, in main from sicer.BASE.controller import Root File /home/admin/buildout/release/sicer/BASE/controller/__init__.py, line 2, in module from sicer.BASE.model.varie.sessione import Sessione File /home/admin/buildout/release/sicer
Re: [sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
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 /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/strategies.py, line 82, in connect return dialect.connect(*cargs, **cparams) File /home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/default.py, line 249, in connect return self.dbapi.connect(*cargs, **cparams) sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor None None Michael Bayer wrote: On Dec 13, 2011, at 11:16 AM, jose soares wrote: Hi all, I'm trying to connect to an oracle db using sqlalchemy with turbogears1 and I get this error: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor I tried making the connection using cs_Oracle and it works: import cx_Oracle as cx conn
[sqlalchemy] sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Hi all,
I'm trying to connect to an oracle db using sqlalchemy with turbogears1
and I get this error:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener
does not currently know of SID given in connect descriptor
I tried making the connection using cs_Oracle and it works:
import cx_Oracle as cx
conn = USERNAME/passw...@myserver.net/SID
cc = cx.connect(conn)
cc.version
'11.2.0.3.0'
cc.close()
but sa ...
File ./start-sicer.py, line 14, in main
from sicer.BASE.controller import Root
File /home/admin/buildout/release/sicer/BASE/controller/__init__.py,
line 2, in module
from sicer.BASE.model.varie.sessione import Sessione
File
/home/admin/buildout/release/sicer/BASE/model/varie/sessione.py, line
1, in module
from sicer.BASE.model.domain import DomainObject
File /home/admin/buildout/release/sicer/BASE/model/domain.py, line
7, in module
from sicer.BASE.model.sql import tbl, session
File /home/admin/buildout/release/sicer/BASE/model/sql.py, line
2468, in module
createdb() # crea lo schema del db
File /home/admin/buildout/release/sicer/BASE/model/sql.py, line 692,
in createdb
if dbtools.exist_table('ruolo_permesso'): #creazione virtuale della
foreign key permesso.codice
File /home/admin/buildout/release/sicer/lib/dbtools.py, line 67, in
exist_table
if engine.execute(sql).fetchone()[0]:
File
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
line 1787, in execute
connection = self.contextual_connect(close_with_result=True)
File
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py,
line 1829, in contextual_connect
self.pool.connect(),
File
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/pool.py,
line 182, in connect
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
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/strategies.py,
line 82, in connect
return dialect.connect(*cargs, **cparams)
File
/home/admin/buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/default.py,
line 249, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener
does not currently know of SID given in connect descriptor
None None
Any ideas?
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] MultipleResultsFound
Hi all, I got, for the first time the following error: ../lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/query.py, line 1417, in one Multiple rows were found for one()) MultipleResultsFound: Multiple rows were found for one() Does anyone know what that means? 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] MultipleResultsFound
Mike Conley wrote: You issued a query with a .one() qualifier and there is more than one row in the database satisfying the condition. Example: 2 names in a table firstname=pete, lastname=smith firstname=john, lastname=smith query for rows lastname=smith with .one() will fail because there are 2 smith in database -- Mike Conley but I'm not explicit using this .one() in my code... Traceback (most recent call last): File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/cherrypy/_cphttptools.py, line 121, in _run self.main() File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/cherrypy/_cphttptools.py, line 264, in main body = page_handler(*virtual_path, **self.params) File string, line 3, in index File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/TurboGears-1.1.1-py2.6.egg/turbogears/identity/conditions.py, line 246, in require predicate.eval_with_object(current, errors): File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/TurboGears-1.1.1-py2.6.egg/turbogears/identity/conditions.py, line 88, in eval_with_object if p.eval_with_object(obj, None): File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/TurboGears-1.1.1-py2.6.egg/turbogears/identity/conditions.py, line 171, in eval_with_object if self.permission_name in identity.permissions: File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/TurboGears-1.1.1-py2.6.egg/turbogears/identity/base.py, line 173, in __getattr__ return getattr(identity, name) File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/TurboGears-1.1.1-py2.6.egg/turbogears/identity/saprovider.py, line 76, in permissions p.permission_name for p in self.user.permissions) File /home/users/admin/release/sicer/BASE/controller/errorhandling.py, line 33, in _get_user return saprovider.user_class.query.get(self.visit_link.user_id) File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/query.py, line 549, in get return self._get(key, ident) File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/query.py, line 1675, in _get return q.one() File /home/users/admin/.VE-SICER2.x-x86_64-19.04.2010/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/query.py, line 1417, in one Multiple rows were found for one()) MultipleResultsFound: Multiple rows were found for one() 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] joinedload alias
Hi all,
I'm trying to use joinedload('specie') in a query but it makes an
unexpected alias of table name to 'specie_1' and it conflict with passed
orderby column specie.descrizione, as in:
ProgrammingError: ('(ProgrammingError) invalid reference to FROM-clause
entry for table specie\nLINE 2: ..._1 ON specie_1.codice =
razza.cod_specie ORDER BY specie.des...\n ^\nHINT: Perhaps you meant to
reference the table alias specie_1.\n', bound method Controller.index
of sicer.BASE.controller.tabelleCodifica.razza.Controller object at
0x72cc1d0) 'SELECT razza.codice AS razza_codice, razza.descrizione AS
razza_descrizione, razza.cod_specie AS razza_cod_specie, specie_1.codice
AS specie_1_codice, specie_1.descrizione AS specie_1_descrizione,
specie_1.cod_gruppo_specie AS specie_1_cod_gruppo_specie,
specie_1.categoria_prodotto AS specie_1_categoria_prodotto \nFROM razza
LEFT OUTER JOIN specie AS specie_1 ON specie_1.codice = razza.cod_specie
ORDER BY specie.descrizione ASC, razza.descrizione ASC \n LIMIT 12
OFFSET 0' {}
Is there a way to tell joinedload to not make the alias or a way to pass
it the alias name?
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] ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1
Hi all, Someone knows what this error mean? ... File /home/ve/sfera/release/sicer/BASE/model/anagraficaAlta/unita_aziendale.py, line 154, in aggiorna_capi_bovini x.flush() File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/ext/assignmapper.py, line 20, in do return getattr(session, name)(self, *args, **kwargs) File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/session.py, line 319, in flush self.uow.flush(self, objects) File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 210, in flush flush_context.execute() File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 400, in execute UOWExecutor().execute(self, head) File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 1020, in execute self.execute_delete_steps(trans, task) File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 1043, in execute_delete_steps self.delete_objects(trans, task) File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 1026, in delete_objects task.mapper.delete_obj(task.polymorphic_todelete_objects, trans) File /home/ve/lib/python2.5/site-packages/SQLAlchemy-0.3.10-py2.5.egg/sqlalchemy/orm/mapper.py, line 1322, in delete_obj raise exceptions.ConcurrentModificationError(Updated rowcount %d does not match number of objects updated %d % (c.rowcount, len(delete))) -- 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] CheckConstraint compatibility
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?
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] about commit()
Mariano Mara wrote:
Excerpts from jo's message of Fri Apr 23 03:16:21 -0300 2010:
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 forgot to add the instance to the session before the commit. See
http://www.sqlalchemy.org/docs/ormtutorial.html#adding-new-objects
Thank you Marionao for replay to my question.
I tried as you suggest, but now it raises an InvalidRequestError, take a
look:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(autoflush=True)
session = Session()
#insert a new row... it doesn't work
new=Specie(
specie_codice='C',
specie_descrizione='cat'
)
session.add(new)
session.commit()
InvalidRequestError: Object 'Specie at 0x14ca650' is already attached
to session '54658512' (this is '21800720')
--
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] about commit()
jo wrote:
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
in my disperation, I tried also the following, but without success: :-(
from sqlalchemy.orm.session import Session
session=Session(autoflush=True,autocommit=True)
class Gruppo:
pass
mapper(Gruppo,
tbl['gruppo'],
column_prefix = 'gruppo_',
)
session.begin()
sqlalchemy.orm.session.SessionTransaction object at 0x28a9710
new=Gruppo( gruppo_id = 1, gruppo_descrizione='cat')
session.commit()
print Gruppo.get(1)
SELECT gruppo.id AS gruppo_id, gruppo.descrizione AS gruppo_descrizione
FROM gruppo
WHERE gruppo.id = %(param_1)s
Col ('gruppo_id', 'gruppo_descrizione')
None
I don't understand what's wrong. I can't INSERT a new record into a table.
Could someone, give me some 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] about commit()
Lance Edgar wrote:
On 4/23/2010 9:19 AM, jose soares wrote:
jo wrote:
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
in my disperation, I tried also the following, but without success: :-(
from sqlalchemy.orm.session import Session
session=Session(autoflush=True,autocommit=True)
class Gruppo:
pass
mapper(Gruppo,
tbl['gruppo'],
column_prefix = 'gruppo_',
)
session.begin()
sqlalchemy.orm.session.SessionTransaction object at 0x28a9710
new=Gruppo( gruppo_id = 1, gruppo_descrizione='cat')
session.commit()
print Gruppo.get(1)
SELECT gruppo.id AS gruppo_id, gruppo.descrizione AS
gruppo_descrizione
FROM gruppo
WHERE gruppo.id = %(param_1)s
Col ('gruppo_id', 'gruppo_descrizione')
None
I don't understand what's wrong. I can't INSERT a new record into a
table.
Could someone, give me some help?
session.commit() raises an UnBoundExecutionError:
Would this (not) work?
from sqlalchemy import *
from sqlalchemy.orm import mapper
metadata = MetaData()
groups = Table('groups', metadata, Column('id', Integer,
primary_key=True), Column('name', String(25)))
class Group(object):
pass
mapper(Group, groups)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session = Session()
group = Group()
group.name = 'cat'
session.add(group)
session.commit()
UnboundExecutionError: Could not locate a bind configured on mapper
Mapper|Group|groups or this Session
session.expunge_all()
group = session.query(Group).first()
print group.id
session.close()
--
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] about commit()
Yes Lance, now it works, thank you v.m. :-)
j
Lance Edgar wrote:
On 4/23/2010 9:19 AM, jose soares wrote:
jo wrote:
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
in my disperation, I tried also the following, but without success: :-(
from sqlalchemy.orm.session import Session
session=Session(autoflush=True,autocommit=True)
class Gruppo:
pass
mapper(Gruppo,
tbl['gruppo'],
column_prefix = 'gruppo_',
)
session.begin()
sqlalchemy.orm.session.SessionTransaction object at 0x28a9710
new=Gruppo( gruppo_id = 1, gruppo_descrizione='cat')
session.commit()
print Gruppo.get(1)
SELECT gruppo.id AS gruppo_id, gruppo.descrizione AS
gruppo_descrizione
FROM gruppo
WHERE gruppo.id = %(param_1)s
Col ('gruppo_id', 'gruppo_descrizione')
None
I don't understand what's wrong. I can't INSERT a new record into a
table.
Could someone, give me some help?
Would this (not) work?
from sqlalchemy import *
from sqlalchemy.orm import mapper
metadata = MetaData()
groups = Table('groups', metadata, Column('id', Integer,
primary_key=True), Column('name', String(25)))
class Group(object):
pass
mapper(Group, groups)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session = Session()
group = Group()
group.name = 'cat'
session.add(group)
session.commit()
session.expunge_all()
group = session.query(Group).first()
print group.id
session.close()
--
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] cls._state / cls._state.get('original') class 'sqlalchemy.orm.attributes.CommittedState'
jo wrote:
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
To explain better my problem, in version 0.3 my models have the
attribute _state where I find the
class 'sqlalchemy.orm.attributes.CommittedState'
(Pdb) self._state
{'original': CommittedState: {'anagrafica_dato_fiscale':
u'02241850367/02241850367', 'anagrafica_id_operatore': 1,
'anagrafica_cap': None, 'anagrafica_telefono': None, 'anagrafica_email':
None, 'anagrafica_nome': u'AZ.USL MODENA distr. PAVULLO',
'anagrafica_cod_nazione_nascita': None, 'anagrafica_localita': None,
'anagrafica_tipo': u'S', 'anagrafica_cod_cittadinanza': None,
'anagrafica_tipo_persona': u'S', 'anagrafica_ts_ultima_modifica':
datetime.datetime(2010, 3, 3, 16, 4, 22, 50891,
tzinfo=psycopg2.tz.FixedOffsetTimezone object at 0xc13488c),
'anagrafica_id': 8, 'anagrafica_data_nascita': None,
'anagrafica_id_comune_nascita': None, 'anagrafica_id_comune': 15090,
'anagrafica_cod_professione': None, 'anagrafica_indirizzo': u'?',
'anagrafica_cod_titolo_studio': None}, 'modified': False}
I can't find this attribute anymore on version 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] cls._state / cls._state.get('original') class 'sqlalchemy.orm.attributes.CommittedState'
King Simon-NFHD78 wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of jose soares
Sent: 16 April 2010 11:03
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] cls._state /
cls._state.get('original') class
'sqlalchemy.orm.attributes.CommittedState'
jo wrote:
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
To explain better my problem, in version 0.3 my models have the
attribute _state where I find the
class 'sqlalchemy.orm.attributes.CommittedState'
(Pdb) self._state
{'original': CommittedState: {'anagrafica_dato_fiscale':
u'02241850367/02241850367', 'anagrafica_id_operatore': 1,
'anagrafica_cap': None, 'anagrafica_telefono': None,
'anagrafica_email':
None, 'anagrafica_nome': u'AZ.USL MODENA distr. PAVULLO',
'anagrafica_cod_nazione_nascita': None, 'anagrafica_localita': None,
'anagrafica_tipo': u'S', 'anagrafica_cod_cittadinanza': None,
'anagrafica_tipo_persona': u'S', 'anagrafica_ts_ultima_modifica':
datetime.datetime(2010, 3, 3, 16, 4, 22, 50891,
tzinfo=psycopg2.tz.FixedOffsetTimezone object at 0xc13488c),
'anagrafica_id': 8, 'anagrafica_data_nascita': None,
'anagrafica_id_comune_nascita': None, 'anagrafica_id_comune': 15090,
'anagrafica_cod_professione': None, 'anagrafica_indirizzo': u'?',
'anagrafica_cod_titolo_studio': None}, 'modified': False}
I can't find this attribute anymore on version 0.6
j
I haven't used this, so I don't know if this helps, but you may be
interested in a couple of the functions described at
http://www.sqlalchemy.org/docs/reference/orm/mapping.html#attribute-util
ities
In particular, either the instance_state() function or the get_history()
function might be useful. I'm not sure there's much documentation for
either the History class or the InstanceState class so you'll have to
read the code in sqlalchemy.orm.state and sqlalchemy.orm.attributes
Hope that helps,
Simon
Thank you for replay my question, Simon,
but I can't find the 'original' state there.
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] column_prefix
I am sorry Michael,
Maybe the problem is not in the column_prefix,
The thing that I don't understand in this query is why sa tries to change the
primary key of this row.
I changed in my form only the value of id_operator,
thus I expected a query like:
UPDATE anagrafica SET id_operatore=1695
WHERE id = 141092
instead of:
UPDATE anagrafica SET id=NULL,
telefono=NULL,
ts_ultima_modifica=NULL,
id_operatore=1695,
tipo=NULL
WHERE anagrafica.id = 141092
Michael Bayer wrote:
If you could send code examples in a readable format, that would be helpful.
Here is column_prefix working as documented:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata = MetaData()
t = Table('foo', metadata,
Column('id', Integer, primary_key=True),
Column('data', String),
)
class Foo(object):
pass
mapper(Foo, t, column_prefix=bar_)
metadata.create_all(engine)
sess = sessionmaker(engine)()
f1 = Foo()
f1.bar_id = 1
f1.bar_data = some data
sess.add(f1)
sess.commit()
assert engine.execute(select * from foo).fetchall() == [(1, 'some data')]
sess.expunge_all()
f1 = sess.query(Foo).first()
assert f1.bar_id == 1
assert f1.bar_data == some data
z-eeks-Computer-3:sqlalchemy classic$ python test.py
2010-04-15 11:15:16,472 INFO sqlalchemy.engine.base.Engine.0x...b990 PRAGMA
table_info(foo)
2010-04-15 11:15:16,472 INFO sqlalchemy.engine.base.Engine.0x...b990 ()
2010-04-15 11:15:16,472 INFO sqlalchemy.engine.base.Engine.0x...b990
CREATE TABLE foo (
id INTEGER NOT NULL,
data VARCHAR,
PRIMARY KEY (id)
)
2010-04-15 11:15:16,473 INFO sqlalchemy.engine.base.Engine.0x...b990 ()
2010-04-15 11:15:16,473 INFO sqlalchemy.engine.base.Engine.0x...b990 COMMIT
2010-04-15 11:15:16,474 INFO sqlalchemy.engine.base.Engine.0x...b990 BEGIN
2010-04-15 11:15:16,475 INFO sqlalchemy.engine.base.Engine.0x...b990 INSERT
INTO foo (id, data) VALUES (?, ?)
2010-04-15 11:15:16,475 INFO sqlalchemy.engine.base.Engine.0x...b990 (1, 'some
data')
2010-04-15 11:15:16,475 INFO sqlalchemy.engine.base.Engine.0x...b990 COMMIT
2010-04-15 11:15:16,476 INFO sqlalchemy.engine.base.Engine.0x...b990 select *
from foo
2010-04-15 11:15:16,476 INFO sqlalchemy.engine.base.Engine.0x...b990 ()
2010-04-15 11:15:16,477 INFO sqlalchemy.engine.base.Engine.0x...b990 BEGIN
2010-04-15 11:15:16,478 INFO sqlalchemy.engine.base.Engine.0x...b990 SELECT foo.id AS foo_id, foo.data AS foo_data
FROM foo
LIMIT 1 OFFSET 0
2010-04-15 11:15:16,478 INFO sqlalchemy.engine.base.Engine.0x...b990 ()
On Apr 15, 2010, at 11:00 AM, jo wrote:
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
Re: [sqlalchemy] cls._state / cls._state.get('original') class 'sqlalchemy.orm.attributes.CommittedState'
Yes, it works, :-)
Thank you Simon,
j
King Simon-NFHD78 wrote:
Jo wrote:
Thank you for replay my question, Simon,
but I can't find the 'original' state there.
j
Here's an example that shows the results of the get_history function:
#
import sqlalchemy as sa
import sqlalchemy.orm as saorm
import sqlalchemy.ext.declarative as decl
from sqlalchemy.orm.attributes import get_history
Base = decl.declarative_base()
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
def display_history(prefix, obj, attr):
history = get_history(obj, attr)
print '%16s: added=%r, unchanged=%r, deleted=%r' % (prefix,
history.added,
history.unchanged,
history.deleted)
engine = sa.create_engine('sqlite://')
Base.metadata.create_all(bind=engine)
Session = saorm.sessionmaker(bind=engine)
sess = Session()
u = User(name='jose')
sess.add(u)
display_history('Before commit', u, 'name')
sess.commit()
u = sess.query(User).get(1)
display_history('After load', u, 'name')
u.name = 'simon'
display_history('Modified once', u, 'name')
u.name = 'sqlalchemy rules'
display_history('Modified twice', u, 'name')
sess.commit()
display_history('After commit', u, 'name')
#
And here are the results:
Before commit: added=['jose'], unchanged=(), deleted=()
After load: added=(), unchanged=[u'jose'], deleted=()
Modified once: added=['simon'], unchanged=(), deleted=[u'jose']
Modified twice: added=['sqlalchemy rules'], unchanged=(),
deleted=[u'jose']
After commit: added=(), unchanged=(), deleted=()
So when you change a scalar attribute, the old value appears in the
'deleted' list, and the new value in the 'added' list. I'm not sure
exactly when the unchanged attribute is used - I would guess it normally
contains the value loaded from the database as long as it hasn't been
modified. I expect it is empty in the 'After commit' line because the
instance has been expired.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] TypeError: synonym() got an unexpected keyword argument
Hi all,
seems synonym in version 0.6 don't have proxy parameter.
'user_name' : synonym('logname', proxy=True),
TypeError: synonym() got an unexpected keyword argument 'proxy'
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] Re: TypeError: synonym() got an unexpected keyword argument
Yes I see, now, thank you, Williams.
j
GHZ wrote:
Hi,
http://www.sqlalchemy.org/changelog/CHANGES_0_6beta3
* 'proxy' argument on synonym() is removed. This flag
did nothing throughout 0.5, as the proxy generation
behavior is now automatic.
On 14 apr, 13:16, jose soares jose.soa...@sferacarta.com wrote:
Hi all,
seems synonym in version 0.6 don't have proxy parameter.
'user_name' : synonym('logname', proxy=True),
TypeError: synonym() got an unexpected keyword argument 'proxy'
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()
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?
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] TypeError: unsupported operand type(s) for +: 'Decimal' and 'float'
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?
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] TypeError: unsupported operand type(s) for +: 'Decimal' and 'float'
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?
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] UniqueConstraint case sensitive
Hi all, I would like to create an UniqueConstraint like this one: CREATE UNIQUE INDEX uniqinx ON prod(lower(name)) Could you help me to translate it to SQLAlchemy using UniqueConstraint ? 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] incoherent behavior between oracle and postgres on engine.rowcount
Hi all, I wonder why there's such difference between oracle and pg: oracle: (Pdb) engine.connect().execute(sql).fetchone() select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None (1273, 'SYSADMIN', 'TIPO_FIGURA', 1, 1, 1, 1) (Pdb) engine.connect().execute(sql).rowcount select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None 0 --- zero??!! === postgres: (Pdb) engine.connect().execute(sql).fetchone() select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None (1533, 'SYSADMIN', 'TIPO_FIGURA', True, True, True, True) (Pdb) engine.connect().execute(sql).rowcount select * from ruolo_permesso where cod_ruolo = 'SYSADMIN' and cod_permesso='TIPO_FIGURA' and inserimento='1' None 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: lower / upper case
Disrupt07 ha scritto:
I have a users table and I want to query the usernames column. I want
my query to ignore the upper/lower casing.
So the following searches should all match John: john, jOhn,
johN, JOhn, and so on.
My query at the moment is a follows:
names = queryselect(users.c.username.startswith(john))
It depends on which db you're using.
If you are using PostgreSQL for example you can use the ilike operator as:
users.c.username.op('ilike')('%'+'john'+'%')
jo
How can I modify the query to obtain the above results?
Thanks
--~--~-~--~~~---~--~~
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] select() got multiple values for keyword argument 'from_obj'
Hi all, I'm trying to create the following query using SA: SELECT DISTINCT operatore.id, anagrafica.nome, acl.cod_ruolo FROM operatore JOIN anagrafica ON operatore.id_anagrafica = anagrafica.id LEFT OUTER JOIN acl ON acl.id_operatore = operatore.id LEFT OUTER JOIN ruolo_permesso ON ruolo_permesso.cod_ruolo = acl.cod_ruolo WHERE (ruolo_permesso.cod_permesso = 'CTR' AND acl.id_asl IS NOT NULL AND operatore.data_fine_attivita IS NULL) - select([Operatore.c.id, Anagrafica.c.nome],Acl.c.cod_ruolo, and_(RuoloPermesso.c.cod_permesso=='CTR', Acl.c.id_aslNone, Operatore.c.data_fine_attivita==None), from_obj=[Operatore.mapper.mapped_table.join(Anagrafica.mapper.mapped_table, Operatore.c.id_anagrafica == Anagrafica.c.id ).outerjoin(Acl.mapper.mapped_table, Acl.c.id_operatore == Operatore.c.id ).outerjoin(RuoloPermesso.mapper.mapped_table, RuoloPermesso.c.cod_ruolo == Acl.c.cod_ruolo)], distinct=True) - ...but it gives me this error: *exceptions.TypeError: (select() got multiple values for keyword argument 'from_obj', bound method Controller.index of sicer.controllers.veterinario_azienda.Controller instance at 0xb65c41ac)* 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: select() got multiple values for keyword argument 'from_obj'
King Simon-NFHD78 ha scritto: Shouldn't acl.cod_ruolo be inside the [] - part of the first parameter to 'select'? The parameters to select are 'columns=None, whereclause=None, from_obj=[], **kwargs', so your 'and_' part is going in as the from_obj parameter, and then you are supplying another from_obj, hence the error message. Hope that helps, Simon oops... :-[ my fault, thank you Simon. 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 -~--~~~~--~~--~--~---
[sqlalchemy] use_labels =30 vs MAX_LABEL_LENGTH
Hi Michael, I see that sql.py uses a limit of 30 characters to create the column label when use_labels is set to True. If name is greater than 30 char long, the label is trunked at position 24 and is appended a random integer to it. Since the name created in this way is less useful, I would like to sugest you to customize the maxlength of column names. (PostgreSQL accepts until 63 characters for names, with SQLite you may use very, very long names) def _get_label(self): if self.__label is None: if self.table is not None and self.table.named_with_column(): self.__label = self.table.name + _ + self.name #if self.table.c.has_key(self.__label) or len(self.__label) = 30: if self.table.c.has_key(self.__label) or len(self.__label) = MAX_LABEL_LENGTH: self.__label = self.__label[0:24] + _ + hex(random.randint(0, 65535))[2:] else: self.__label = self.name self.__label = .join([x for x in self.__label if x in legal_characters]) --~--~-~--~~~---~--~~ 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: error compile
Sébastien LELONG ha scritto: Seems that SA compiles in a wrong way my query... Can't what's wrong is happening... subvet appers to be a sub-select, so probably SA made some optimizations. You should print the whole query (print sql) and not the sub-query (as in your code: print subvet) to check if your query is actually compiled the wrong way. Seb sql=select([unita_aziendale.c.id], and_(unita_aziendale.c.id.in_( select([azienda_veterinario.c.id_unita_aziendale], and_(azienda_veterinario.c.id_veterinario==3, azienda_veterinario.c.id_unita_aziendale==unita_aziendale.c.id, azienda_veterinario.c.data_inizioNone, azienda_veterinario.c.data_fine==None ), from_obj=[ azienda_veterinario, unita_aziendale], ), )), ) print sql SELECT unita_aziendale.id FROM unita_aziendale WHERE unita_aziendale.id IN (SELECT azienda_veterinario.id_unita_aziendale AS id_unita_aziendale FROM azienda_veterinario WHERE azienda_veterinario.id_veterinario = %(azienda_veterinario_id_veterinario)s AND azienda_veterinario.id_unita_aziendale = unita_aziendale.id AND azienda_veterinario.data_inizio IS NOT NULL AND azienda_veterinario.data_fine IS NULL) -- As you can see the from_obj of subselect is wrong, the FROM should be: FROM azienda_veterinario, unita_aziendale 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: error compile
Sébastien LELONG ha scritto: As you can see the from_obj of subselect is wrong, the FROM should be: FROM azienda_veterinario, unita_aziendale OK, I see... You probably mean that since your sub-select occurs on two tables, those have to be present in the FROM clause. I've tested this kind on query (select a from A where a in (select b from B where a = b)) and this works ok (MySQL). Your sub-query becomes invalid if taken alone, but the whole keeps correct. What's the error when performing manually the query (using mysql client or the like) ? Seb I know it works but the problem is the performance. this query takes 0m31.073s: SELECT unita_aziendale.id FROM unita_aziendale WHERE unita_aziendale.id IN (SELECT azienda_veterinario.id_unita_aziendale AS id_unita_aziendale FROM azienda_veterinario WHERE azienda_veterinario.id_veterinario = 3 AND azienda_veterinario.id_unita_aziendale = unita_aziendale.id AND azienda_veterinario.data_inizio IS NOT NULL AND azienda_veterinario.data_fine IS NULL) this one takes 0m0.686s SELECT unita_aziendale.id FROM unita_aziendale WHERE unita_aziendale.id IN (SELECT azienda_veterinario.id_unita_aziendale AS id_unita_aziendale FROM azienda_veterinario, unita_aziendale WHERE azienda_veterinario.id_veterinario = 3 AND azienda_veterinario.id_unita_aziendale = unita_aziendale.id AND azienda_veterinario.data_inizio IS NOT NULL AND azienda_veterinario.data_fine IS NULL) 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 -~--~~~~--~~--~--~---
[sqlalchemy] SA skips integrity referential?
Hi all,
I wonder how SA could delete a row of my table (postgresql) linked with
another table.
Take a look...
pg= select * from attivita where cod_specie='33';
codice | descrizione | cod_specie
+-+
21311 | Sezionamento selvaggina allevata | 33
pg= select * from specie where codice='33';
codice | descrizione
---+-
33 | Selvaggina
(1 row)
sfera= delete from specie where codice='33';
ERROR: update or delete on specie violates foreign key constraint
attivita_cod_specie_fkey on attivita
DETAIL: Key (codice)=(33) is still referenced from table attivita.
--
let's try now using SA:
tg-admin shell
In [1] aa=Specie.get_by(codice='33')
In [3]: aa.delete
Out[3]: bound method Specie.do of Specie 33
In [4]: aa.delete()
In [5]: aa.flush()
2007-02-16 15:30:31,955 sqlalchemy.engine.base.Engine.0x..f4 INFO BEGIN
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO SELECT
attivita.cod_specie AS attivita_cod_specie, attivita.attivo AS
attivita_attivo, attivita.cod_attivita_istat AS
attivita_cod_attivita_istat, attivita.descrizione AS
attivita_descrizione, attivita.cod_prodotto AS attivita_cod_prodotto,
attivita.cod_tipologia_struttura AS attivita_cod_tipologia_s_2e27,
attivita.cod_organizzazione AS attivita_cod_organizzazione,
attivita.cod_orientamento_produttivo AS attivita_cod_orientament_583a,
attivita.codice AS attivita_codice
FROM attivita
WHERE %(lazy_b4ba)s = attivita.cod_specie ORDER BY attivita.codice
2007-02-16 15:30:31,958 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'lazy_b4ba': '33'}
2007-02-16 15:30:31,979 sqlalchemy.engine.base.Engine.0x..f4 INFO UPDATE
attivita SET cod_specie=%(cod_specie)s WHERE attivita.codice =
%(attivita_codice)s
2007-02-16 15:30:31,980 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'cod_specie': None, 'attivita_codice': '01302'}
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO DELETE
FROM specie WHERE specie.codice = %(codice)s
2007-02-16 15:30:31,986 sqlalchemy.engine.base.Engine.0x..f4 INFO
{'codice': '33'}
2007-02-16 15:30:31,990 sqlalchemy.engine.base.Engine.0x..f4 INFO COMMIT
pg= select * from specie where codice='33';
codice | descrizione
---+-
pg= select * from specie where codice='33';
codice | descrizione
---+-
(0 row)
--~--~-~--~~~---~--~~
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] iteration over mapper
Hi all, Probably this is a stupid question, :-[ but I don't understand how to iterate an object mapper to get fields value. --- user = session.query(User).select(id=1) for j in user.c: print j.name logname id password for j in user.c: print j.value 'Column' object has no attribute 'value' 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 -~--~~~~--~~--~--~---
[sqlalchemy] date format
Hi all, I would like to display my dates with format '%d/%m/%Y' instead of ISO format. qry = session.query(Nazione).select(Nazione.c.codice=='201') qry[0].data_inizio print qry[0].data_inizio 2006-01-14 Is there a way to set it in SA without using a customer function ? 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: InvalidRequestError: This transaction is inactive
Jose Soares ha scritto:
Michael Bayer ha scritto:
not sure about that, i thought maybe the multiple flush()es are
breaking something but I just added a test case and it doesnt
reproduce. make sure youre on the most recent versions since that was
broken a few versions back...
This error happens some times, not every time.
my versione is SQLAlchemy-0.3.0
jo
Hi Mike,
I upgraded to 0.3.4
but this function works only without create_transaction()...
def add_user(self,data):
#transaction = session.create_transaction()
try:
anagrafica = Anagrafica(
nome = data.get('display_name'),
email= data.get('email'),
)
session.save(anagrafica)
session.flush()
user = User(
id_anagrafica= anagrafica.id, #ultimo record
inserito in anagrafica
data_inizio_attivita = data.get('data_inizio_attivita'),
data_fine_attivita = data.get('data_fine_attivita'),
)
for item in data.get('dettaglio'):
if item.get('cod_ruolo'):
user.ruoli.append(UserGroup(
id_operatore = item.get('id_operatore'),
group_id = item.get('cod_ruolo'),
id_asl = item.get('id_asl'),
)
)
session.save(user)
session.flush()
#transaction.commit()
return inserito il record %d!%user.id
except:
#transaction.rollback()
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
-~--~~~~--~~--~--~---
[sqlalchemy] serial
Hi all, I would like to know how the postgresl serial type field works in sa? SA doesn't increment the serial column if of my group_permission table: gp=GroupPermission() for x in Permission.select(): gp.cod_ruolo = 'sysadmin' gp.cod_permesso = x.codice session.save(gp) print 'inserted: ', gp.cod_permesso, gp.cod_ruolo, gp.id _this field doesn't change !!!_ session.flush() this is my table: table group_permission( id serial primary key, cod_ruolotext not null, cod_permesso text not null ) and this is my mapper: class GroupPermission(DomainObject): pass assign_mapper(context, GroupPermission, tbl['group_permission'],) 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: serial
sorry for this message, my fault, I have to assign the GroupPermission instance inside the loop. Anyway I see that SA compile the INSERTs with serials as in: INSERT INTO (id, cod_ruolo, cod_permesso ) VALUES (%(id)s, %(cod_ruolo)s, %(cod_permesso)s); I suppose it should be like this: INSERT INTO (cod_ruolo, cod_permesso ) VALUES (%(cod_ruolo)s, %(cod_permesso)s); because it is a postgresql responsability to assign value to id where serial is triggered. jo Jose Soares ha scritto: Hi all, I would like to know how the postgresl serial type field works in sa? SA doesn't increment the serial column if of my group_permission table: gp=GroupPermission() for x in Permission.select(): gp.cod_ruolo = 'sysadmin' gp.cod_permesso = x.codice session.save(gp) print 'inserted: ', gp.cod_permesso, gp.cod_ruolo, gp.id _this field doesn't change !!!_ session.flush() this is my table: table group_permission( id serial primary key, cod_ruolotext not null, cod_permesso text not null ) and this is my mapper: class GroupPermission(DomainObject): pass assign_mapper(context, GroupPermission, tbl['group_permission'],) 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column aliases
I think I have a similar trouble, although the symptoms are different.
I have a PostgreSQL table named 'acl' mapped as UserGroup.
Table acl
Column| Type
--+-
id | integer
id_operatore | integer
cod_ruolo| text
id_asl | integer
id_azienda | integer
I overriding some columns as: (take a look specially to group_id =
cod_ruolo):
class UserGroup(DomainObject):
pass
assign_mapper(context, UserGroup, tbl['acl'],
properties = { 'user_id' : tbl['acl'].c.id_operatore,
'id_operatore': tbl['acl'].c.id_operatore,
'group_id': tbl['acl'].c.cod_ruolo,
'cod_ruolo' : tbl['acl'].c.cod_ruolo})
I have this problem, triyng to insert data...
the first line works but the second one, doesn't inser any data into
column cod_ruolo.
1. id_operatore = item.get('id_operatore'), group_id =
item.get('cod_ruolo'))
2. id_operatore = item.get('id_operatore'), cod_ruolo =
item.get('cod_ruolo'))
Any ideas?
jo
Marco Mariani ha scritto:
Hi there
This relates to Turbogears, but is really a SA question.
I've customized TG authentication authorization to use my autloaded
tables in Postgres and SqlAlchemy 0.3.3.
In my schema, I have User.c.uid, the login name of the users, as a
primary key
TG uses a User mapper with two distinct columns: User.c.user_id (the
primary key) and User.c.user_name (the logname).
Since I am an avid fan of meaningful primary keys (and have a legacy db
to support) I want to keep things my way, but TG does some user handling
that I have to fix.
So, to avoid patches to the TG source or useless sub-classing, I'd like
to access the same column by any of the three names.
I cannot do that with a python property on the mapper because TG should
be able to use get_by and friends.
I've come up with:
assign_mapper(context, User, tbl['users'], properties = {
'user_id': tbl['users'].c.uid, # alias for SqlAlchemyIdentity
'user_name': tbl['users'].c.uid, # alias for SqlAlchemyIdentity
'uid': tbl['users'].c.uid,
})
This seems to work (I added the third property to make 'uid' reappear!)
, but makes it impossible, for instance, to create new users:
In [1]: user = User(uid='xxx')
In [2]: session.flush()
[...]
SQLError: (IntegrityError) null value in column uid violates not-null
constraint
'INSERT INTO users (uid, nome, cognome, codice_fiscale) VALUES
(%(uid)s, %(name)s, %(surname)s)' {'surname': None, 'uid': None, 'name':
None}
In [3]: user = User(user_id='xxx')
In [4]: session.flush()
[...]
SQLError: (IntegrityError) null value in column uid violates not-null
constraint
'INSERT INTO users (uid, nome, cognome, codice_fiscale) VALUES
(%(uid)s, %(name)s, %(surname)s)' {'surname': None, 'uid': None, 'name':
None}
I reckon I should probably go ahead and patch TG, but maybe there is a
clean way to do what I have in mind?
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: left join help
Hello Michael, the query is perfect, with every JOIN and LEFT JOIN, thank you very much. jo Michael Bayer ha scritto: hey jose - was waiting to see if anyone jumped on this for you. alas, no responses. making some assumptions about your Table objects, your query above would look something like (forgiving my occasional misspellings of italian words): select([operatore.c.id, anagrafica.c.nome], and_(acl.c.cod_ruolo=='vet', operatore.c.data_fine_attivita==None), from_obj=[operatore.join(anagrafica, operatore.c.id_anagrafica==anagrafica.c.id).outerjoin(acl, acl.c.id_operatore==operatore.c.id)], distinct=True, order_by=[operatore.c.id, anagrafica.c.nome]) --~--~-~--~~~---~--~~ 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: How do I do append_whereclause() using the ORM?
Robin Munn ha scritto:
Maybe this is in the documentation, but if so, I've missed it.
I'd like to do something like the stmt = users.select();
stmt.append_whereclause(...) example found in
http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_building , but
using a Query object taken from an ORM mapper via session.query(User).
Is this possible? How would I do it?
I'm using it in this way
---
qryBoll = session.query(Bolletta)
statements = [ Bolletta.c.anno == 2006,
Bolletta.c.sezionale == '2006-01-01'
]
statements.append(Bolletta.c.numero == 123)
try:
boll = qryBoll.selectone_by(*boll_statements)
except InvalidRequestError:
flash('error!')
-
I hope this help you...
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: CASE , CAST, COALESCE
Great! jo Michael Bayer ha scritto: we have case(): select(case([(table.c.x==5, 5), (table.c.y==7, 12)], else_=7)) and cast(): select([cast(table.c.x, Numeric)]) which are in the generated documentation for sql.py. for coalesce() you can call that as func.coalesce() for now. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
