Preface: I'm converting an app from MySQL/MySQLdb to
PostgreSQL/psycopg2/SQLAlchemy. My general SQL experience is limited,
and this is my first time using SQLAlchemy. So far, the conversion
process has been mostly smooth and I've been able to clean up a lot of
things.
I've just started getting into the ORM aspects of SQLAlchemy and I'm
trying to do something fairly complicated (I guess):
- The user enters a free-form street address, which may be only partly
specified (that is, certain parts, like city or zip code might be
missing).
- The app looks for all streets (AKA edges) in the database that match
what the user entered and returns a list of street objects. Here's the
query I'm trying to use to accomplish this:
edges = query.select(and_(
func.least(streets.c.addr_f, streets.c.addr_t) <= num,
num <= func.greatest(streets.c.addr_f, streets.c.addr_t),
street_names.c.prefix == street_name.prefix,
street_names.c.name == street_name.name,
street_names.c.sttype == street_name.sttype,
street_names.c.suffix == street_name.suffix
cities.c.city == place.city,
states.c.id == place.state_id,
or_(c.zip_code_l == place.zip_code, c.zip_code_r == place.zip_code),
))
The problem is, in cases where there should be only one result, there
are, for example, 38. The part that seems to be borked is the
street_names stuff (something to do with the way the tables are
joined???).
The streets table has a foreign key pointer, street_name_id, to the
street names table (see table defs below). If I get the
street_name_ids in a separate query and do the following, I get the
expected number of results:
edges = query.select(and_(
func.least(streets.c.addr_f, streets.c.addr_t) <= num,
num <= func.greatest(streets.c.addr_f, streets.c.addr_t),
streets.c.street_name_id.in_(*street_name_ids),
cities.c.city == place.city,
states.c.id == place.state_id,
or_(c.zip_code_l == place.zip_code, c.zip_code_r == place.zip_code),
))
Here's how things are set up (with some stuff left out for clarity):
Tables
===================
streets = Table(
'layer_streets',
metadata,
Column('id', Integer, primary_key=True),
Column('geom', Geometry, nullable=False),
Column('node_f_id', Integer,
ForeignKey('layer_nodes.id'),
nullable=False),
Column('node_t_id', Integer,
ForeignKey('layer_nodes.id'),
nullable=False),
Column('addr_f', Integer),
Column('addr_t', Integer),
Column('even_side', CHAR(1)),
Column('street_name_id', Integer, ForeignKey('street_names.id'),),
Column('city_l_id', Integer, ForeignKey('cities.id')),
Column('city_r_id', Integer, ForeignKey('cities.id')),
Column('state_l_id', CHAR(2), ForeignKey('states.id'),
nullable=False),
Column('state_r_id', CHAR(2), ForeignKey('states.id'),
nullable=False),
Column('zip_code_l', Integer),
Column('zip_code_r', Integer),
schema=schema,
),
street_names = Table(
'street_names',
metadata,
Column('id', Integer, primary_key=True),
Column('prefix', String(2)),
Column('name', String, nullable=False),
Column('sttype', String(4)),
Column('suffix', String(2)),
schema=schema,
),
Objects
===================
class Street:
"""Things and stuff which is not relevant to this discussion."""
class StreetName:
"""Things and stuff which is not relevant to this discussion."""
Mappers
===================
street_name_mapper = orm.mapper(
StreetName,
street_names,
)
edge_mapper = orm.mapper(
Street,
layer_streets,
properties={
'node_f': orm.relation(
Node,
primaryjoin=layer_streets.c.node_f_id ==
layer_nodes.c.id,
),
'node_t': orm.relation(
Node,
primaryjoin=layer_streets.c.node_t_id ==
layer_nodes.c.id,
),
'street_name': orm.relation(
StreetName,
),
'city_l': orm.relation(
City,
primaryjoin=layer_streets.c.city_l_id ==
cities.c.id,
),
'city_r': orm.relation(
City,
primaryjoin=layer_streets.c.city_r_id ==
cities.c.id,
),
'state_l': orm.relation(
State,
primaryjoin=layer_streets.c.state_l_id ==
states.c.id,
),
'state_r': orm.relation(
State,
primaryjoin=layer_streets.c.state_r_id ==
states.c.id,
),
}
)
So, the question is, then, of course, what am I doing wrong? Is it the
query? The mapper? Should I define another mapper for this purpose?
Would this be a lot easier if I just knew more about SQL in general?
Any tips, pointers, or hints will be most appreciated.
--
Wyatt Baldwin
byCycle.org
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
