SQLAlchemy and Can't Adapt.

Question

Hi. I have the following exception when using sqlalchemy on postgres:

raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)

ProgrammingError: (ProgrammingError) can't adapt 'UPDATE doc_data SET content=%(content)s WHERE doc_data.serial_id = %(doc_data_serial_id)s' {'content': 'Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English \xc2\xa92010 - Privacidad', 'doc_data_serial_id': 3181L, 'title': 'Google'}

The text is the one that appears in www.google.com.ar (at the bottom)... "Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English ©2010 - Privacidad". The content is unicode....

The model is:

class DocData(Data):  
    __tablename__ = 'doc_data'

    serial_id = Column(Integer, ForeignKey('data.serial_id', ondelete='cascade'),
                          primary_key=True)
    content = Column(UnicodeText)

And the database is:

CREATE TABLE doc_data  
(  
  serial_id integer NOT NULL,  
  "content" text  
)  
WITH (OIDS=FALSE);  

Any ideas of why the error take place? When doing the same query on postgres I get a warning: "HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.". BUt I don't understand why that happens because I am using the model to make the query, so SQLALchemy (i think) should escape the carahcters.

Thanks in advance

Answer 1

I answered this on the ML the other day and the issue is likely that you need to enable "utf-8" client encoding for your postgresql database.

Answer 2

Below is a test case that issues your UPDATE statement in three different ways, the third of which also reproduces the odd extra "title" column in your bind parameters. All complete properly. Please ensure that you're on a recent SQLAlchemy (0.5.8 is current, nothing before 0.5.5 please) as well as a recent psycopg2. We will need to see a full test case illustrating how you're getting an error.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

Base = declarative_base()

class DocData(Base):  
    __tablename__ = 'doc_data'

    serial_id = Column(Integer, primary_key=True)
    content = Column(UnicodeText)

Base.metadata.create_all(engine)

data = 'Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English \xc2\xa92010 - Privacidad'.decode('utf-8')

sess = sessionmaker(engine)()

doc = DocData(serial_id=3181, content=None)
sess.add(doc)
sess.flush()

doc.content = data
sess.commit()

engine.execute(
    DocData.__table__.update().where(DocData.__table__.c.serial_id==3181),
    {'content':data,
    'title':'google'}
)

engine.execute("UPDATE doc_data SET content=%(content)s WHERE doc_data.serial_id = %(doc_data_serial_id)s", 
{'content': 'Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English \xc2\xa92010 - Privacidad', 'doc_data_serial_id': 3181L, 'title': 'Google'})

result:

select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
{'name': 'doc_data'}

CREATE TABLE doc_data (
serial_id SERIAL NOT NULL, 
content TEXT, 
PRIMARY KEY (serial_id)
)
{}
COMMIT
BEGIN
INSERT INTO doc_data (serial_id, content) VALUES (%(serial_id)s, %(content)s)
{'content': None, 'serial_id': 3181}
UPDATE doc_data SET content=%(content)s WHERE doc_data.serial_id = %(doc_data_serial_id)s
{'content': 'Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English \xc2\xa92010 - Privacidad', 'doc_data_serial_id': 3181}
COMMIT
UPDATE doc_data SET content=%(content)s WHERE doc_data.serial_id = %(serial_id_1)s
{'content': 'Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English \xc2\xa92010 - Privacidad', 'serial_id_1': 3181}
COMMIT
UPDATE doc_data SET content=%(content)s WHERE doc_data.serial_id = %(doc_data_serial_id)s
{'content': 'Programas de publicidad - Soluciones Empresariales - Todo acerca de Google - Google.com in English \xc2\xa92010 - Privacidad', 'doc_data_serial_id': 3181L, 'title': 'Google'}
COMMIT

Answer 3

Thanks for all your help but the problem was more basic. The content was an XML Element, but I got confused by the print....

Thanks again