Thread: Passing in parameters enclosed in double quotes
Hello,
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.
Brent Hoover
Computer Scientist
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