Thread: [SQLObject] Using sqlbuilder.func
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Jorge G. <jg...@gm...> - 2007-04-22 17:06:36
|
Hi! I've written a function that I want to use in a query. When I write the query using SQL Object + SQL Builder I get wrong SQL generated. The correct output would be (wrapped and with parens and quotes fixed from SO's generated code): ================================================================================ SELECT neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.cliente_id, neolab.v_resultados_resumo.nao_conformidade_id, neolab.v_resultados_resumo.segmento_id, neolab.v_resultados_resumo.cliente_nome, neolab.v_resultados_resumo.material_coletado_id, neolab.v_resultados_resumo.material_coletado, neolab.v_resultados_resumo.amostra_id, neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.analise, neolab.v_resultados_resumo.situacao, neolab.v_resultados_resumo.data_cadastro, neolab.v_resultados_resumo.previsao_entrega, neolab.v_resultados_resumo.entregue_em, neolab.v_resultados_resumo.recebido_em, neolab.v_resultados_resumo.ref_externa, neolab.v_resultados_resumo.nome_paciente, neolab.v_resultados_resumo.nome_paciente_normalizado, neolab.v_resultados_resumo.autenticacao, neolab.v_resultados_resumo.emitir_laudo FROM neolab.v_resultados_resumo WHERE ((((neolab.v_resultados_resumo.segmento_id) = (1)) AND (((neolab.v_resultados_resumo.data_cadastro) >= ('2006-04-17 00:00:00')) AND ((neolab.v_resultados_resumo.data_cadastro) <= ('2007-04-23 00:00:00')))) AND ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE (f_v_texto('jo%')))) ORDER BY id; ================================================================================ The query starts like this: ================================================================================ amostras = model.VResultadoResumo.select( AND(model.VResultadoResumo.q.segmentoID == toxicologia.id, model.VResultadoResumo.q.dataCadastro >= data_inicial, model.VResultadoResumo.q.dataCadastro <= data_final)) if situacao == u'pendentes': amostras = amostras.filter( model.VResultadoResumo.q.entregueEm==None) elif situacao == u'prontos': amostras = amostras.filter( model.VResultadoResumo.q.entregueEm!=None) if nome_paciente: amostras = amostras.filter( model.VResultadoResumo.q.nomePacienteNormalizado.startswith( func.f_v_texto(nome_paciente))) ================================================================================ Here, my function on the databse is "f_v_texto" at PostgreSQL's public schema. nome_paciente is a unicode string containing, for this test case, 'jo'. >From PostgreSQL's log the generated query is (just wrapped to fit on the screen): ================================================================================ SELECT neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.cliente_id, neolab.v_resultados_resumo.nao_conformidade_id, neolab.v_resultados_resumo.segmento_id, neolab.v_resultados_resumo.cliente_nome, neolab.v_resultados_resumo.material_coletado_id, neolab.v_resultados_resumo.material_coletado, neolab.v_resultados_resumo.amostra_id, neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.analise, neolab.v_resultados_resumo.situacao, neolab.v_resultados_resumo.data_cadastro, neolab.v_resultados_resumo.previsao_entrega, neolab.v_resultados_resumo.entregue_em, neolab.v_resultados_resumo.recebido_em, neolab.v_resultados_resumo.ref_externa, neolab.v_resultados_resumo.nome_paciente, neolab.v_resultados_resumo.nome_paciente_normalizado, neolab.v_resultados_resumo.autenticacao, neolab.v_resultados_resumo.emitir_laudo FROM neolab.v_resultados_resumo WHERE ((((neolab.v_resultados_resumo.segmento_id) = (1)) AND (((neolab.v_resultados_resumo.data_cadastro) >= ('2006-04-17 00:00:00')) AND ((neolab.v_resultados_resumo.data_cadastro) <= ('2007-04-23 00:00:00')))) AND ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE ('_v_texto('jo'%'))) ORDER BY id ================================================================================ It is missing the 'f' in 'f_v_texto' and misses one closing parenthesis. Also, I don't know why it is quoting the function name. Did I do something wrong? Is it a bug? Should I change my code somehow to have it working? Just to be sure to have all what is available for SQL Object in, I've updated to 0.10dev_r2576 and the problem is still there. I'll go back to 0.8.2 for production. -- Jorge Godoy <jg...@gm...> |
From: Jorge G. <jg...@gm...> - 2007-04-22 17:18:34
|
Jorge Godoy <jg...@gm...> writes: If I change: > ================================================================================ > if nome_paciente: > amostras = amostras.filter( > model.VResultadoResumo.q.nomePacienteNormalizado.startswith( > func.f_v_texto(nome_paciente))) > ================================================================================ into: ================================================================================ # LIKE = sqlbuilder.LIKE if nome_paciente: amostras = amostras.filter(LIKE( model.VResultadoResumo.q.nomePacienteNormalizado, func.f_v_texto(nome_paciente))) ================================================================================ it works, but then I have to add the '%' sign by myself. With that new syntax the generated code is: ================================================================================ SELECT neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.cliente_id, neolab.v_resultados_resumo.nao_conformidade_id, neolab.v_resultados_resumo.segmento_id, neolab.v_resultados_resumo.cliente_nome, neolab.v_resultados_resumo.material_coletado_id, neolab.v_resultados_resumo.material_coletado, neolab.v_resultados_resumo.amostra_id, neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.analise, neolab.v_resultados_resumo.situacao, neolab.v_resultados_resumo.data_cadastro, neolab.v_resultados_resumo.previsao_entrega, neolab.v_resultados_resumo.entregue_em, neolab.v_resultados_resumo.recebido_em, neolab.v_resultados_resumo.ref_externa, neolab.v_resultados_resumo.nome_paciente, neolab.v_resultados_resumo.nome_paciente_normalizado, neolab.v_resultados_resumo.autenticacao, neolab.v_resultados_resumo.emitir_laudo FROM neolab.v_resultados_resumo WHERE ((((neolab.v_resultados_resumo.segmento_id) = (1)) AND (((neolab.v_resultados_resumo.data_cadastro) >= ('2006-04-17 00:00:00')) AND ((neolab.v_resultados_resumo.data_cadastro) <= ('2007-04-23 00:00:00')))) AND (neolab.v_resultados_resumo.nome_paciente_normalizado LIKE (f_v_texto('jo')))) ORDER BY id; ================================================================================ And the "correct" code would have 'jo%' instead of 'jo'. Is there something that I can use to make SQL Object / sqlbuilder add that to me or should I append that to my query string "by hand"? -- Jorge Godoy <jg...@gm...> |
From: Oleg B. <ph...@ph...> - 2007-04-22 17:27:03
|
On Sun, Apr 22, 2007 at 02:05:36PM -0300, Jorge Godoy wrote: > model.VResultadoResumo.q.nomePacienteNormalizado.startswith( > func.f_v_texto(nome_paciente))) [skip] > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE > ('_v_texto('jo'%'))) ORDER BY id > ================================================================================ > > It is missing the 'f' in 'f_v_texto' and misses one closing parenthesis. > Also, I don't know why it is quoting the function name. This is the result of sqlbuilder._LikeQuoted.__sqlrepr__() that incorrectly processes its expression. Instead of removing quotes it removes the first letter and adds excessive quotes. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-04-23 15:44:32
|
On Sun, Apr 22, 2007 at 09:27:02PM +0400, Oleg Broytmann wrote: > On Sun, Apr 22, 2007 at 02:05:36PM -0300, Jorge Godoy wrote: > > model.VResultadoResumo.q.nomePacienteNormalizado.startswith( > > func.f_v_texto(nome_paciente))) > [skip] > > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE > > ('_v_texto('jo'%'))) ORDER BY id > > ================================================================================ > > > > It is missing the 'f' in 'f_v_texto' and misses one closing parenthesis. > > Also, I don't know why it is quoting the function name. > > This is the result of sqlbuilder._LikeQuoted.__sqlrepr__() that > incorrectly processes its expression. Instead of removing quotes it removes > the first letter and adds excessive quotes. Jorge, can you replace sqlbuilder._LikeQuoted.__sqlrepr__() with the following and test if works for you? def __sqlrepr__(self, db): s = self.expr if isinstance(s, SQLExpression): values = [] if self.prefix: values.append(sqlrepr(self.prefix, db)) s = _quote_percent(sqlrepr(s, db), db) values.append(s) if self.postfix: values.append(sqlrepr(self.postfix, db)) if db == "mysql": return "CONCAT(%s)" % ", ".join(values) else: return " || ".join(values) else: # assuming s is a string s = _quote_percent(s, db) return "'%s%s%s'" % (self.prefix, s, self.postfix) def _quote_percent(s, db): if db in ('postgres', 'mysql'): s = s.replace('%', '\\%') else: s = s.replace('%', '%%') return s Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge G. <jg...@gm...> - 2007-04-23 19:15:42
|
Oleg Broytmann <ph...@ph...> writes: > On Sun, Apr 22, 2007 at 09:27:02PM +0400, Oleg Broytmann wrote: >> On Sun, Apr 22, 2007 at 02:05:36PM -0300, Jorge Godoy wrote: >> > model.VResultadoResumo.q.nomePacienteNormalizado.startswith( >> > func.f_v_texto(nome_paciente))) >> [skip] >> > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE >> > ('_v_texto('jo'%'))) ORDER BY id >> > ================================================================================ >> > >> > It is missing the 'f' in 'f_v_texto' and misses one closing parenthesis. >> > Also, I don't know why it is quoting the function name. >> >> This is the result of sqlbuilder._LikeQuoted.__sqlrepr__() that >> incorrectly processes its expression. Instead of removing quotes it removes >> the first letter and adds excessive quotes. > > Jorge, can you replace sqlbuilder._LikeQuoted.__sqlrepr__() with the > following and test if works for you? I'll do that tonight. I'm working at my client's office now and as soon as I get to my office I'll test it for you. -- Jorge Godoy <jg...@gm...> |
From: Oleg B. <ph...@ph...> - 2007-04-23 19:18:35
|
On Mon, Apr 23, 2007 at 04:13:57PM -0300, Jorge Godoy wrote: > > Jorge, can you replace sqlbuilder._LikeQuoted.__sqlrepr__() with the > > following and test if works for you? > > I'll do that tonight. I'm working at my client's office now and as soon > as I get to my office I'll test it for you. Ok, waiting. It works for me, and I added a few tests. Just wanted to do an additional beta test. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge G. <jg...@gm...> - 2007-04-23 22:13:39
|
Oleg Broytmann <ph...@ph...> writes: > On Mon, Apr 23, 2007 at 04:13:57PM -0300, Jorge Godoy wrote: >> > Jorge, can you replace sqlbuilder._LikeQuoted.__sqlrepr__() with the >> > following and test if works for you? >> >> I'll do that tonight. I'm working at my client's office now and as soon >> as I get to my office I'll test it for you. > > Ok, waiting. It works for me, and I added a few tests. Just wanted to > do an additional beta test. Sure! And it worked perfectly fine here. Including the use of my custom function. The generated query was now: ================================================================================ SELECT neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.cliente_id, neolab.v_resultados_resumo.nao_conformidade_id, neolab.v_resultados_resumo.segmento_id, neolab.v_resultados_resumo.cliente_nome, neolab.v_resultados_resumo.material_coletado_id, neolab.v_resultados_resumo.material_coletado, neolab.v_resultados_resumo.amostra_id, neolab.v_resultados_resumo.id, neolab.v_resultados_resumo.analise, neolab.v_resultados_resumo.situacao, neolab.v_resultados_resumo.data_cadastro, neolab.v_resultados_resumo.previsao_entrega, neolab.v_resultados_resumo.entregue_em, neolab.v_resultados_resumo.recebido_em, neolab.v_resultados_resumo.ref_externa, neolab.v_resultados_resumo.nome_paciente, neolab.v_resultados_resumo.nome_paciente_normalizado, neolab.v_resultados_resumo.autenticacao, neolab.v_resultados_resumo.emitir_laudo FROM neolab.v_resultados_resumo WHERE ((((neolab.v_resultados_resumo.segmento_id) = (1)) AND (((neolab.v_resultados_resumo.data_cadastro) >= ('2006-04-18 00:00:00')) AND ((neolab.v_resultados_resumo.data_cadastro) <= ('2007-04-24 00:00:00')))) AND ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE (f_v_texto('jose') || '%'))) ORDER BY id ================================================================================ My SQL Object code that generated this was: ================================================================================ if nome_paciente: amostras = amostras.filter( model.VResultadoResumo.q.nomePacienteNormalizado.startswith( func.f_v_texto(nome_paciente))) ================================================================================ Where model.VResultadoResumo points to a VIEW and func.f_v_texto is a function at PostgreSQL's public schema. It is perfectly as I expected it to be. ;-) So you have a "go" from me. It worked flawless. Thank you very much, Oleg! You help us writing beautiful code. ;-) -- Jorge Godoy <jg...@gm...> |
From: Oleg B. <ph...@ph...> - 2007-04-24 06:53:37
|
On Mon, Apr 23, 2007 at 07:11:04PM -0300, Jorge Godoy wrote: > So you have a "go" from me. It worked flawless. Thank you! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-04-22 17:39:05
|
In any case, even when I fix sqlbuilder, this > model.VResultadoResumo.q.nomePacienteNormalizado.startswith( > func.f_v_texto(nome_paciente))) doesn't mean > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE > (f_v_texto('jo%')))) It means ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE (f_v_texto('jo'))) + '%') Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge G. <jg...@gm...> - 2007-04-22 17:57:30
|
Oleg Broytmann <ph...@ph...> writes: > In any case, even when I fix sqlbuilder, this > >> model.VResultadoResumo.q.nomePacienteNormalizado.startswith( >> func.f_v_texto(nome_paciente))) > > doesn't mean > >> ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE >> (f_v_texto('jo%')))) > > It means > > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE > (f_v_texto('jo'))) + '%') That's OK. My function isn't used in any direct query, it just prepares the string to be used against one specific index. I believe that you meant: ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE (f_v_texto('jo'))) || '%') since '+' for concatenating strings throws an error: ================================================================================ neo=# SELECT neolab.v_busca_analises_toxicologia_texto.id, neolab.v_busca_analises_toxicologia_texto.mnemonico, neolab.v_busca_analises_toxicologia_texto.mnemonico_normalizado, neolab.v_busca_analises_toxicologia_texto.nome, neolab.v_busca_analises_toxicologia_texto.nome_normalizado FROM neolab.v_busca_analises_toxicologia_texto WHERE (neolab.v_busca_analises_toxicologia_texto.nome_normalizado LIKE (f_v_texto('acet')+'%'::text)) ORDER BY neolab.v_busca_analises_toxicologia_texto.nome; ERRO: operador não existe: text + text LINE 1: ...gia_texto.nome_normalizado LIKE (f_v_texto('acet')+'%'::text... ^ HINT: Nenhum operador corresponde com o nome e os tipos dos argumentos informados. Você precisa adicionar conversões de tipo explícitas. neo=# SELECT neolab.v_busca_analises_toxicologia_texto.id, neolab.v_busca_analises_toxicologia_texto.mnemonico, neolab.v_busca_analises_toxicologia_texto.mnemonico_normalizado, neolab.v_busca_analises_toxicologia_texto.nome, neolab.v_busca_analises_toxicologia_texto.nome_normalizado FROM neolab.v_busca_analises_toxicologia_texto WHERE (neolab.v_busca_analises_toxicologia_texto.nome_normalizado LIKE (f_v_texto('acet')||'%'::text)) ORDER BY neolab.v_busca_analises_toxicologia_texto.nome; id | mnemonico | mnemonico_normalizado | nome | nome_normalizado ----+-----------+-----------------------+---------+------------------ 2 | ACET | acet | ACETONA | acetona (1 row) neo=# ================================================================================ The rewritten form will make use of the special indices that I created at the database, so this will work for me. :-) -- Jorge Godoy <jg...@gm...> |
From: Oleg B. <ph...@ph...> - 2007-04-22 18:06:47
|
On Sun, Apr 22, 2007 at 02:54:02PM -0300, Jorge Godoy wrote: > > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE > > (f_v_texto('jo'))) + '%') > > That's OK. I will think on how to fix _LikeQuoted... > I believe that you meant: > > ((neolab.v_resultados_resumo.nome_paciente_normalizado) LIKE > (f_v_texto('jo'))) || '%') > > since '+' for concatenating strings throws an error: Probably. I didn't write SQL for quite a lot of time. :) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge G. <jg...@gm...> - 2007-04-22 18:15:26
|
Oleg Broytmann <ph...@ph...> writes: > I will think on how to fix _LikeQuoted... Thanks! As a temporary fix I've appended the percent sign myself. > Probably. I didn't write SQL for quite a lot of time. :) It is fun ;-) But I prefer writing Python inside the database as well. :-) -- Jorge Godoy <jg...@gm...> |
From: Oleg B. <ph...@ph...> - 2007-04-22 18:22:14
|
On Sun, Apr 22, 2007 at 03:12:05PM -0300, Jorge Godoy wrote: > I prefer writing Python inside the database as > well. :-) Postgres allows it, with some limitations as far as I heard... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge G. <jg...@gm...> - 2007-04-22 18:42:33
|
Oleg Broytmann <ph...@ph...> writes: > On Sun, Apr 22, 2007 at 03:12:05PM -0300, Jorge Godoy wrote: >> I prefer writing Python inside the database as >> well. :-) > > Postgres allows it, with some limitations as far as I heard... I haven't noticed the limitations :-) And it works very well. It even allows loading external modules :-) -- Jorge Godoy <jg...@gm...> |