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