Re: About mogrify

Federico Di Gregorio escribió:
> Il giorno lun, 22/09/2008 alle 12.54 +0200, Oswaldo Hernández ha
...
> Why do you need to call mogrify here?
> 
Thanks for your reply Federico.
I try to explain because i need mogriy().
Note: My english is bad, if you don't understand something, tell me and i try to rewrite better.

I'm working on a sql generator. The user, in a graphical way, can select tables, fields, make joins 
and create conditions.

For cheking the SQL, i create, in the database side, a pl/psql function than receives the SQL as a 
string, and returns a setof of field types, names, etc.., or raise an error if the SQL is malformed 
or has sintax errors.

If the SQL has no parameters (user conditions) there no problem, y can do:

 >>> sqluser = "select referencia, descripcion from v_productos"
 >>> argsuser = ()
 >>> sqlcheck = "select nombre, tipo_base from describe_campos('%s')"
 >>> c.execute(sqlcheck % sqluser, argsuser)
 >>> c.fetchall()
[('Referencia', 'varchar'), ('Descripci\xc3\xb3n', 'varchar')]


The problem is when the sql have parameters:

 >>> sqluser = "select referencia, descripcion from v_productos where referencia=%s"
 >>> argsuser = ('333',)
 >>> sqlcheck = "select nombre, tipo_base from describe_campos('%s')"
 >>> c.execute(sqlcheck % sqluser, argsuser)
Traceback (most recent call last):
   File "<input>", line 1, in <module>
ProgrammingError: error de sintaxis en o cerca de «333»
LINE 1: ...ncia, descripcion from v_productos where referencia=E'333'')

if use mogrify to debug ;) the result is malfomed bacause the user sql is yet enclosed with '

 >>> con.cursor().mogrify(sqlcheck % sqluser, argsuser)
"select nombre, tipo_base from describe_campos('select referencia, descripcion from v_productos 
where referencia=E'333'')"


The best way that i found to avoid these is using mogrify in the user sql and pass the result as 
parameter in the execute:

 >>> sqluser = "select referencia, descripcion from v_productos where referencia=%s"
 >>> argsuser = ('333',)
 >>> sqlcheck = "select nombre, tipo_base from describe_campos(%s)"
 >>> sqluser = sqluser.encode(con.encoding)
 >>> sqluser = con.cursor().mogrify(sqluser, argsuser)
 >>> c.execute(sqlcheck, (sqluser,))
 >>> c.fetchall()
[('Referencia', 'varchar'), ('Descripci\xc3\xb3n', 'varchar')]


I appreciate any suggestion.

Regards.

-- 
*****************************************
Oswaldo Hernández
oswaldo (@) soft-com (.) es
*****************************************
PD:
Antes de imprimir este mensaje, asegúrese de que es necesario.
El medio ambiente está en nuestra mano.
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg