psycopg2

[Andre' John]

Hi

I am trying to do this for a Postgresql database:

conn = psycopg2.connect('host=localhost')
cur = conn.cursor()
cur.execute("SELECT * FROM names WHERE name=%s", ['S'])

, which doesn't work, and neither does

cur.execute("SELECT * FROM names WHERE name='%s'", ['S'])

or

cur.execute("SELECT * FROM names WHERE name='S'")

work. It always returns:

Traceback (most recent call last):

File "<stdin>", line 1, in <module>

psycopg2.ProgrammingError: array value must start with ?{½ or
dimension information


Though, when doing

cur.execute("SELECT * FROM names")

it works.
I am totally helpless here. Does anyone have an idea?

Cheers

Andre

 

[Steve Holden]

Hi

I am trying to do this for a Postgresql database:

conn = psycopg2.connect('host=localhost')
cur = conn.cursor()
cur.execute("SELECT * FROM names WHERE name=%s", ['S'])

, which doesn't work, and neither does

cur.execute("SELECT * FROM names WHERE name='%s'", ['S'])

or

cur.execute("SELECT * FROM names WHERE name='S'")

work.

I'm more inclined to believe the first two than the third, but I suppose
if you are telling the truth (House: "Patients always lie") then I am
guessing you have defined your table's "names" columns to be an array type.

I haven't worked with those (since they don't confirm to the strict
relational model I prefer to work with), but I am guessing you might try


cur.execute("SELECT * FROM names WHERE name='%s'", (['S'], ))

as this provides the necessary tuple as the second argument to execute,
and the on;y element of the tuple is a list of a single element.

> It always returns:

Traceback (most recent call last):

File "<stdin>", line 1, in <module>

psycopg2.ProgrammingError: array value must start with ?{½ or
dimension information


Though, when doing

cur.execute("SELECT * FROM names")

it works.
I am totally helpless here. Does anyone have an idea?

If my suggestion doesn't work, you should probably let us know more
about the structure of your table.

regards
Steve

 

[Andre' John]

Thanks very much, this was indeed the problem. That column was an array.
I created it via GUI and was wondering why it did append a '[]' to the
variable type, but didn't bother, because there was no other varchar
available.

Cheers

Andre

PS: Weird though how few results turn up when running that through
Google.

Hi

I am trying to do this for a Postgresql database:

conn = psycopg2.connect('host=localhost')
cur = conn.cursor()
cur.execute("SELECT * FROM names WHERE name=%s", ['S'])

, which doesn't work, and neither does

cur.execute("SELECT * FROM names WHERE name='%s'", ['S'])

or

cur.execute("SELECT * FROM names WHERE name='S'")

work.

I'm more inclined to believe the first two than the third, but I suppose if
you are telling the truth (House: "Patients always lie") then I am guessing
you have defined your table's "names" columns to be an array type.

I haven't worked with those (since they don't confirm to the strict relational
model I prefer to work with), but I am guessing you might try


cur.execute("SELECT * FROM names WHERE name='%s'", (['S'], ))

as this provides the necessary tuple as the second argument to execute, and
the on;y element of the tuple is a list of a single element.

It always returns:

Traceback (most recent call last):

File "<stdin>", line 1, in <module>

psycopg2.ProgrammingError: array value must start with ?{½ or dimension
information


Though, when doing

cur.execute("SELECT * FROM names")

it works.
I am totally helpless here. Does anyone have an idea?

If my suggestion doesn't work, you should probably let us know more about the
structure of your table.

regards
Steve

 

[Steve Holden]

Thanks very much, this was indeed the problem. That column was an array.
I created it via GUI and was wondering why it did append a '[]' to the
variable type, but didn't bother, because there was no other varchar
available.

If you're using pgAdmin then try "character varying" - then your
original SQL should work correctly!

regards
Steve

 

[Steve Holden]

Thanks very much, this was indeed the problem. That column was an array.
I created it via GUI and was wondering why it did append a '[]' to the
variable type, but didn't bother, because there was no other varchar
available.

If you're using pgAdmin then try "character varying" - then your
original SQL should work correctly!

regards
Steve

 

[Tim Roberts]

I am trying to do this for a Postgresql database:

conn = psycopg2.connect('host=localhost')
cur = conn.cursor()
cur.execute("SELECT * FROM names WHERE name=%s", ['S'])

, which doesn't work, and neither does

cur.execute("SELECT * FROM names WHERE name='%s'", ['S'])

Psycopg requires that the parameters be passed as a tuple, not a list.
Also, psycopg will do the quoting for you. You don't do it. So this is
what you want:

cur.execute("SELECT * FROM names WHERE name=%s", ('S',) )

Note that the extra comma is required in Python to make a one-element
tuple.

 

[DouhetSukd]

Also, psycopg will do the quoting for you. You don't do it. So this is
what you want:

Second the above, it is much cleaner to leave the quoting to
psycopg2. I know, I wrote my own quoting logic for dynamically
generated queries and I was happy as happy as a turkey on boxing day
to dump that code overboard when I realized I didn't need it anymore.

However, I think you are better off using dictionary based
substitutions:

cur.execute("SELECT * FROM names WHERE name= %(name)s ",
{"name":"S"} )

On small and simple queries it doesn't matter much and using dicts is
actually a bit harder. On complex queries you may find:

- lots of binds.
- the same bind variable being used in several places
- optimization/adjustments may require you to rewrite the query
several times and shuffling the binds around into different
positions.

Maintaining positional binds is then a huge hassle compared to name-
based binds.

For the lazy, using dicts has another advantage:

name = "S"
firstname = "F"
cur.execute("SELECT * FROM names WHERE name = %(name)s and firstname =
%(firstname)s ", locals() )

Both MySQL and postgresql can work from the above examples as they
both accept name-binds and dicts. Something which is not all that
clear in the docs. Not all dbapi implementations allow this though.

Last but definitely not least, using dicts allows you to re-use bind
data more efficiently/painlessly:

di_cust = {"cust_id":
mynewcustid,"another_field_only_on_customer_table":3}

execute("insert into customer (cust_id....) values (%
(cust_id)s...)",di_cust)

for order in get_order_lines():
di_order = {"order_id"rder.order_id}

#grab shared field data from the customer dict.
another_field_only_on_customer_table will be ignored later.
di_order.update(di_cust)

execute("insert into customer_orders (cust_id,order_id....) values (%
(cust_id)s, %(order_id)s...)",di_order)

None of this is very attractive if you believe in only using ORMs, but
it is bread and butter to looking after complex sql by yourself.

Cheers