Обсуждение: Passing in parameters enclosed in double quotes

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])))

Hope this helps,
federico

--
Federico Di Gregorio                                       fog@initd.org
 But not all bugs are an interesting challenge. Some are just a total
  waste of my time, which usually is much more valuable than the time of
  the submitter.                                                   -- Md

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

On 18/11/11 17:43, Adrian Klaver wrote:
> The only way I could get the substitution to work is:
> cur.execute("""select pg_get_serial_sequence(%s,%s)""",('"test 1"','id'))

This seems to be the only correct way to do it.

You want to call a function that accepts two text parameters, so you
need to use two Python strings as parameters. The first parameter is
'"test 1"' that is, a Python string containing "test 1". The second is
'id', that is a Python string containing id.

Cheers,
Jan

On 18/11/11 17:43, Adrian Klaver wrote:
> On Friday, November 18, 2011 7:37:32 am Federico Di Gregorio wrote:
>> > On 18/11/11 16:31, Brent Hoover wrote:
[snip]
> 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"',)]

Given your example query in psql that's correct.

--
Federico Di Gregorio                                       fog@initd.org
  "Yes, your honour, I have RSA encryption code tattood on my penis.
   Shall I show the jury?"                                     -- <dark>

On Friday, November 18, 2011 8:58:10 am Federico Di Gregorio wrote:

> > 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"',)]
>
> Given your example query in psql that's correct.

It is only an issue if you actually need to double quote a table name to
preserve case, a space, a key word, etc.

--
Adrian Klaver
adrian.klaver@gmail.com