Cannot create index for json fields
As mentioned in google groups post by mike bayer; https://groups.google.com/d/msg/sqlalchemy/3ws_6orlSDY/lxS2FZR9PA8J
from sqlalchemy import Column, Integer, create_engine, Index, MetaData, Table
from sqlalchemy.dialects.postgresql import JSONB
e = create_engine("postgresql://postgres:postgres@localhost/deleteme_jsonb", echo=True)
m = MetaData()
publishers = Table('publishers', m, Column('id', Integer), Column('info', JSONB))
Index("foo", publishers.c.info['name'].astext)
m.create_all(e)
The first time i run the script:
Traceback (most recent call last):
File "/home/vahid/pycharm-2016.2/helpers/pydev/pydevd.py", line 1556, in <module>
globals = debugger.run(setup['file'], None, None, is_module)
File "/home/vahid/pycharm-2016.2/helpers/pydev/pydevd.py", line 940, in run
pydev_imports.execfile(file, globals, locals) # execute the script
File "/home/vahid/pycharm-2016.2/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile
exec(compile(contents+"\n", file, 'exec'), glob, loc)
File "/home/vahid/workspace/sqlalchemy-attachment/practice/mike.py", line 15, in <module>
m.create_all(e)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 3826, in create_all
tables=tables)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1917, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File "/usr/lib/python3.5/contextlib.py", line 77, in __exit__
self.gen.throw(type, value, traceback)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1910, in _optional_conn_ctx_manager
yield conn
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1917, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1526, in _run_visitor
**kwargs).traverse_single(element)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 733, in visit_metadata
_is_metadata_operation=True)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 772, in visit_table
self.traverse_single(index)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_index
self.connection.execute(CreateIndex(index))
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 947, in execute
return meth(self, multiparams, params)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 998, in _execute_ddl
if not self.schema_for_object.is_default else None)
File "<string>", line 1, in <lambda>
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 433, in compile
return self._compiler(dialect, bind=bind, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler
return dialect.ddl_compiler(dialect, self, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 208, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 231, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1570, in visit_create_index
for expr in index.expressions
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1570, in <listcomp>
for expr in index.expressions
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 231, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 558, in visit_grouping
return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 995, in visit_binary
return disp(binary, operator_, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1006, in visit_custom_op_binary
element, " " + operator.opstring + " ", **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1019, in _generate_generic_binary
binary.right._compiler_dispatch(self, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1146, in visit_bindparam
bindparam, within_columns_clause=True, **kwargs)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1178, in render_literal_bindparam
return self.render_literal_value(value, bindparam.type)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1334, in render_literal_value
value = super(PGCompiler, self).render_literal_value(value, type_)
File "/home/vahid/.virtualenvs/falcon/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1196, in render_literal_value
"Don't know how to literal-quote value %r" % value)
NotImplementedError: Don't know how to literal-quote value 'name'
Comments (6)
-
repo owner -
repo owner -
repo owner - changed status to resolved
Build string/int processors for JSONIndexType, JSONPathType
Fixed regression in JSON datatypes where the "literal processor" for a JSON index value, that needs to take effect for example within DDL, would not be invoked for the value. The native String and Integer datatypes are now called upon from within the JSONIndexType and JSONPathType. This is applied to the generic, Postgresql, and MySQL JSON types.
Change-Id: Ifa5f2acfeee57a79d01d7fc85d265a37bd27c716 Fixes:
#3765→ <<cset f2fa9d000b44>>
-
repo owner thanks!
-
reporter Thanks a lot.
After more than 16 years programming, i found the sqlalchemy the most powerful and flexible ( let me say the only real ) ORM. I have done many enterprise and medium-sized projects, for seven years, powered by Mako, DogPile, sqlalchemy and alembic.
All the advanced tasks have been done by the ORM level such as Locks, CTE, Indexes, Constraints and full-text search. Incredibly, we've never written any SQL-Query directly. all is handled by your elegant ORM.
Mako is perfect. fastest ever and full-featured template engine.
Please accept my special thanks. for the revolution you've made in python world.
Best wishes for you and Dilly
Regards
Vahid
-
repo owner thanks for the compliments!
- Log in to comment
this is a 1.1 regression. note that 1.1 is still in beta.