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: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.
