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(dot)klaver(at)gmail(dot)com