DDL and TypeError: 'dict' object does not support indexing

[Petr Kobalíček]

Hi devs,

I added %% into my DDL and sqlalchemy raises the TypeError: 'dict'
object does not support indexing.

The critical part is:

row_array t_record%%ROWTYPE in the DDL.

Em I using the DDL correctly? I read that I need to double the '%'
character and I did that.

Best regards
Petr Kobalicek

The callable code is here (using postgres):
--------------------------------------

#!/usr/bin/env python

from sqlalchemy import MetaData
from sqlalchemy import Table, Column

from sqlalchemy import Integer, Boolean
from sqlalchemy import Unicode, UnicodeText
from sqlalchemy import DDL

from sqlalchemy import engine_from_config
from sqlalchemy import func
from sqlalchemy import select

from sqlalchemy import orm
from sqlalchemy import text

metadata = MetaData()

RecordTable = Table('t_record', metadata,
Column('record_id', Integer, primary_key = True),
Column('idx', Integer, default=None),
Column('msg', UnicodeText)
)

sql_create = DDL(
"\n"

"CREATE OR REPLACE FUNCTION t_record_new()\n"
" RETURNS trigger AS\n"
"$BODY$\n"
" BEGIN\n"
" NEW.idx := (SELECT COUNT(r.idx) AS t FROM t_record AS r);\n"
" RETURN NEW;\n"
" END;\n"
"$BODY$\n"
"LANGUAGE 'plpgsql';\n"

"\n"

"CREATE OR REPLACE FUNCTION t_record_up(param_id BIGINT)\n"
" RETURNS void AS\n"
"$BODY$\n"
" DECLARE\n"
" r INT;\n"
" row_array t_record%%ROWTYPE;\n"
" BEGIN\n"
" -- Get index of the row we need to move.\n"
" SELECT t_record.idx\n"
" FROM t_record\n"
" WHERE t_record.record_id = $1\n"
" INTO r;\n"
" \n"
" FOR row_array IN SELECT *\n"
" FROM t_record\n"
" WHERE t_record.idx >= r\n"
" ORDER_BY t_record.idx\n"
" LIMIT 2\n"
" LOOP\n"
" \n"
" END LOOP;\n"
" \n"
" UPDATE t_record\n"
" SET idx=111\n"
" WHERE t_record.record_id = $1;\n"
" \n"
" RETURN;\n"
" END;\n"
"$BODY$\n"
"LANGUAGE 'plpgsql';\n"
"\n"

"CREATE TRIGGER t_record_new BEFORE INSERT ON t_record\n"
"FOR EACH ROW\n"
" EXECUTE PROCEDURE t_record_new();\n"
)
sql_create.execute_at('after-create', RecordTable)

sql_drop = DDL(
"\n" +

"DROP TRIGGER IF EXISTS t_record_new ON t_record;\n" +
"DROP FUNCTION IF EXISTS t_record_new();\n"
)
sql_drop.execute_at('before-drop', RecordTable)

class RecordModel(object):
def __init__(self, msg = u""):
self.msg = msg

def __repr__(self):
return u"REC - Idx(" + unicode(self.idx) + u"), Msg(" + self.msg + u")"

def printRecords():
print u"\nRECORDS:"
rows = session.execute(select([RecordTable])).fetchall()
for row in rows: \
print u"REC - Idx(" + unicode(row.idx) + u"), Msg(" + row.msg + u")"

orm.mapper(RecordModel, RecordTable,
properties = {
"id": RecordTable.c.record_id,
"msg": RecordTable.c.msg
}
)

engine = engine_from_config({
"sqlalchemy.url": "postgresql://someuser:somepass@localhost/somedb",
"sqlalchemy.convert_unicode": True,
"sqlalchemy.echo": True
}, prefix="sqlalchemy.")
metadata.bind = engine

metadata.drop_all(checkfirst=True)
metadata.create_all()

sm = orm.sessionmaker(bind = engine, autoflush = True)
session = orm.scoped_session(sm)

The full error log:
--------------------------------------

Traceback (most recent call last):
File "C:\My\Devel\Web\Test\sqla_readonly.py", line 114, in <module>
metadata.create_all()
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\schema
.py", line 2013, in create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py", line 1647, in create
connection=connection, **kwargs)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py", line 1682, in _run_visitor
**kwargs).traverse_single(element)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\sql\vi
sitors.py", line 77, in traverse_single
return meth(obj, **kw)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\ddl.py", line 42, in visit_metadata
self.traverse_single(table, create_ok=True)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\sql\vi
sitors.py", line 77, in traverse_single
return meth(obj, **kw)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\ddl.py", line 65, in visit_table
listener('after-create', table, self.connection)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\schema
.py", line 2200, in __call__
return bind.execute(self.against(target))
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py", line 1157, in execute
params)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py", line 1210, in _execute_ddl
return self.__execute_context(context)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py", line 1268, in __execute_context
context.parameters[0], context=context)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\base.py", line 1360, in _cursor_execute
context)
File "C:\Python\lib\site-packages\sqlalchemy-0.6.3-py2.7.egg\sqlalchemy\engine
\default.py", line 288, in do_execute
cursor.execute(statement, parameters)
TypeError: 'dict' object does not support indexing

[Michael Bayer]

On Aug 30, 2010, at 12:10 PM, Petr Kobalíček wrote:

> Hi devs,
>
> I added %% into my DDL and sqlalchemy raises the TypeError: 'dict'
> object does not support indexing.
>
> The critical part is:
>
> row_array t_record%%ROWTYPE in the DDL.
>
> Em I using the DDL correctly? I read that I need to double the '%'
> character and I did that.

unfortunately the Psycopg2 dialect is not applying its extra step of doubling up "%%" for the benefit of the DBAPI to DDL() sequences, and ticket #1897 is added for this. Its not clear if we can really fix this in the 0.6 series since it would not be backwards compatible to those working around it so the ticket is targeted at 0.7.

At the moment, triple escaping is required in this case:

foo%%%%bar

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Petr Kobalíček]

Hi Michael,

thanks for reply, I tried triple escaping before I posted the first
message, but the error is the same.

Interesting is fact that i can add 20 '%' characters into the DDL but
in SQL INFO I always see only one '%'.

Is there other workaround? Can I safely replace the line described in:
http://www.sqlalchemy.org/trac/ticket/1897
?

Best regards
Petr Kobalicek

[Petr Kobalíček]

Hi Michael,

triple escaping works, sorry for misinformation.

Best regards
Petr

[Sven A. Schmidt]

Just wondering if it's any different if you try the tripe quote syntax
"""...""". For example in a similar case I use

q = """
select
os,
count(os)
from (
select
distinct
s.id,
os
from
server s
join instance_server ins on s.id = ins.server_id
join instance i on ins.instance_servers_id = i.id
join nar n on i.nar_id = n.id
where
upper(n.nar_name) = upper(:app)
and host_type = 'PHYSICAL'
)
group by os
order by os
"""
q = text(q)
conn = session.connection()
rows = conn.execute(q, app=app).fetchall()

with the additional benefit that I can copy the SQL verbatim to an SQL
editor for testing.

-sas