Re: Passing in parameters enclosed in double quotes

On Friday, November 18, 2011 7:37:32 am Federico Di Gregorio wrote:
> On 18/11/11 16:31, Brent Hoover wrote:
> > I am sure this is in the documentation somewhere, but I am stumped as to
> > where.
> >
> > I am trying to pass in a table name to reset a series of sequences.
> >
> > conn_cursor.execute("""SELECT setval(pg_get_serial_sequence("%s", %s),
> > 1, false);""", ( _column[0]), _column[1],))
> >
> > where _column[0] is a table name, and _column[1] is a column name. So
> > the table name needs to be directly enclosed in double-quotes, but the
> > psycopg2 adapter is adding single quotes inside that. So instead of
> > getting "table_name" I get "'table_name'" which does not work. I feel
> > like is probably an issue of escaping the quotes somehow but I cannot
> > figure out how. Psycopg2's behavior is completely correct here, it sees
> > a string and wraps it in quotes, but this case of wanting to access a
> > table name is somewhat of a special case.
> >
> > Thanks so much for such a great piece of software.
>
> Use the AsIs adapter:
>
> from psycopg2.extensions import AsIs
>
> conn_cursor.execute(
>   """SELECT setval(pg_get_serial_sequence("%s", %s), 1, false);""",
>   (AsIs(_column[0]), AsIs(_column[1])))


I was following along and tried the above and it did not work for me.

On Postgres end:

CREATE table "test 1" (id serial, fld_1 text);

test(5432)aklaver=>\d "test 1"
                          Table "public.test 1"
 Column |  Type   |                       Modifiers
--------+---------+-------------------------------------------------------
 id     | integer | not null default nextval('"test 1_id_seq"'::regclass)
 fld_1  | text    |



test(5432)aklaver=>select pg_get_serial_sequence('"test 1"','id');
 pg_get_serial_sequence
------------------------
 public."test 1_id_seq"


On Psycopg2 end:

cur.execute("""select pg_get_serial_sequence(%s,%s)""",(AsIs('"test 1"'),'id'))
ProgrammingError: column "test 1" does not exist
LINE 1: select pg_get_serial_sequence("test 1",E'id')

cur.execute("""select pg_get_serial_sequence(%s,%s)""",(AsIs("test 1"),'id'))
ProgrammingError: syntax error at or near "1"
LINE 1: select pg_get_serial_sequence(test 1,E'id')

The only way I could get the substitution to work is:
cur.execute("""select pg_get_serial_sequence(%s,%s)""",('"test 1"','id'))
rs=cur.fetchall()
rs
 [('public."test 1_id_seq"',)]



>
> Hope this helps,
> federico

--
Adrian Klaver
adrian.klaver@gmail.com