Strange SQL error when selecting with a filter in SQLAlchemy

Question

Edit I found the answer and it's not related to how the model is setup. I was trying to pass the request.form from flask to a function that actually creates the address. Apparently converting that to kwargs causes a problem. Manually specifying each of the arguments made everything work correctly.

I'm new to SQLAlchemy and things have been going pretty well so far but I've hit a problem and I'm completely stumped so I'm hoping someone here knows what i'm doing wrong!

I have an address model and i'm trying to select from it. The model looks like this:

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    comment = Column(String(255))
    address1 = Column(String(100))
    address2 = Column(String(100))
    city = Column(String(45))
    state = Column(String(2))
    zip = Column(String(5))
    zip4 = Column(String(4))
    lat = Column(Float)
    lon = Column(Float)

I'm trying to match elements of an address entered through my application to see if I can find the lat and longitude without geocoding. I've used filter_by in other queries that worked so I am trying the same thing here: (the address dict comes from post parameters to my application)

matched_address = session.query(Address).filter_by(
    address1=address['address1'],
    city=address['city'],
    state=address['state'],
    zip=address['zip']
).one()

Unfortunately this is failing with a SQL Error:

ProgrammingError: (ProgrammingError) (1064, 'You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right 
syntax to use near \') AND address.state = ("\'XX\'",) AND address.zip = 
("\'XXXXX\'",) AND address.city \' at line 3')

This looks very very ugly! Where are the extra () come from? I turned on echo=True in my call to create_engine() and it prints a query that seems correct (I tried it in the mysql client with no problems):

SELECT address.id AS address_id, address.comment AS address_comment, 
    address.address1 AS address_address1, address.address2 AS address_address2, 
    address.city AS address_city, address.state AS address_state, 
    address.zip AS address_zip, address.zip4 AS address_zip4, 
    address.lat AS address_lat, address.lon AS address_lon 
FROM address 
WHERE address.address1 = %s AND address.state = %s AND address.zip = %s AND 
    address.city = %s 
LIMIT %s

But the parameters it says it's outputting seems a little odd, though I'm not sure if this is just the python object or if it's really going to MySQL this way: (note the X characters are just replacements for my real address)

([u'1111 XXXXXX St'], [u'XX'], [u'XXXXX'], [u'XXXXX'], 1)

Just for fun I tried using filter() instead of filter_by() but I get the same errors:

matched_address = database.session.query(Address).filter(
    and_(Address.address1==address['address1'], Address.city==address['city'],    
    Address.state==address['state'],Address.zip==address['zip'])).one()

Sorry if this is a basic question. Thanks in advance for any help!

Answer 1

I found the answer and it's not related to how the model is setup. I was trying to pass the request.form from flask to a function that actually creates the address. Apparently converting that to kwargs causes a problem. Manually specifying each of the arguments made everything work correctly.

Answer 2

Have you tried printing out the value of address['address1'], etc? The repr would probably be clearest.

[EDIT]

After a look at the documentation, I wonder whether you should be doing it this way:

from sqlalchemy import and_
filter(and_(address1 == address['address1'], city == address['city'], ...))

Answer 3

Your class definition is working for me, modulo the large amount of setup code you didn't provide. Apologies for the volume of code that follows. First, the definitions:

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    comment = Column(String(255))
    address1 = Column(String(100))
    address2 = Column(String(100))
    city = Column(String(45))
    state = Column(String(2))
    zip  = Column(String(5))
    zip4 = Column(String(4))
    lat = Column(Float)
    lon = Column(Float)

    #note I am only initializing the querying columns. i am lazy   
    def __init__(self, cm, a1, ct, st):
        self.comment = cm
        self.address1 = a1
        self.city = ct
        self.state = st

Put that class definition into a file MyAddr.py and import it, then fire up python. Note I'm using sqlite per the SA demo.

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=False)
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)
>>> from MyAddr import Base, Address
>>> Base.metadata.create_all(engine)
>>> 
>>> a = '1234 Main St.'
>>> c = 'Anywhere'
>>> st= 'MO'
>>> cm= 'Blah blah blah blah blah'
>>> 
>>> addr = Address(cm, a, c, st)
>>> 
>>> s = Session()
>>> s.add(addr)
>>> s.commit()
>>> 
>>> q = s.query(Address).filter_by(address1=a,city=c)
>>> 
>>> q.one()
<op.Address object at 0x10d158a50>
>>> q = s.query(Address).filter_by(address1=a,city=c,state=st)
>>> q.one()
<op.Address object at 0x10d158a50>
>>> abc = q.one()
>>> abc
<op.Address object at 0x10d158a50>
>>> abc.city
u'Anywhere'
>>> abc.state
u'MO'
>>> abc.address1
u'1234 Main St.'

Since your class definition can work, there isn't enough information in your question (for me) to know why it is not working. Perhaps it is something with your input data? Your error message contains two sets of quotes, one escaped. Does your input dict contain quoted strings?