building a sqlalchemy postgresql cidr contains query

[Raymond Burkholder]

I have a model defined with:

from app import db
from sqlalchemy.dialects import postgresql

class TableIpAddress(db.Model):
__tablename__ = 'ipaddress'
idipaddress = db.Column( postgresql.UUID, primary_key=True )
ipaddress = db.Column( postgresql.CIDR, index=True, nullable=False )

I would like to do something like the following:

ip = '192.168.0.0/16'
db.session.query( TableIpAddress.ipaddress.op('<<')(ip) ).all()

The table's ipaddress field is CIDR. But in the first line of the error
message following, it is trying to coerce to INET. It should parameterize
as CIDR? What be the proper way of making this query function properly?

sqlalchemy.exc.DBAPIError: (ParameterError) could not pack parameter
$1::pg_catalog.inet for transfer
CODE: --PIO
LOCATION: CLIENT
DETAIL: '192.168.0.0/16'
HINT: Try casting the parameter to 'text', then to the target type.
POSITION: 0
STATEMENT: [prepared]
sql_parameter_types: ['pg_catalog.inet']
results: ('anon_1' 'BOOLEAN')
statement_id: py:0x7fc6b125e2e8
string:
SELECT ipaddress.ipaddress << $1 AS anon_1
FROM ipaddress
CONNECTION: [idle in block]
client_address: 127.0.0.1/32
client_port: 45620
version:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
CONNECTOR: [IP4] pq://polestar:***@127.0.0.1:5432/polestar
category: None
DRIVER: postgresql.driver.pq3.Driver 'SELECT ipaddress.ipaddress <<
%(ipaddress_1)s AS anon_1 \nFROM ipaddress' {'ipaddress_1':
'192.168.0.0/16'}




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

[Michael Bayer]

in your stack trace is this weird string “postgresql.driver.pq3”, googling it reveals this is the py-postgresql driver. This is not at all a well-known driver and I suggest switching to psycopg2, which will probably just work in this case.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Raymond Burkholder]

>
> in your stack trace is this weird string "postgresql.driver.pq3", googling
it
> reveals this is the py-postgresql driver. This is not at all a well-known
driver
> and I suggest switching to psycopg2, which will probably just work in this
> case.
>

Does psycopg2 parameterize it's queries, and forward the binary data, or
does it just build up concatenated strings and pass them to the database
client? From my reading, it appeared as though py-postgresql would be a
superior driver, as it did parameterization and transmitted parameters in
their native binary format.

[Michael Bayer]

psycopg2 doesn’t standardize on prepared statements so i think it does string parameterization. I don’t believe postgresql offers much advantage to native parameters.