1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

Cannot create index for json fields

Issue #3765 resolved
Vahid Mardani created an issue

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)

  3. Mike Bayer repo owner

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

  5. Vahid Mardani 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

  7. Log in to comment
Assignee
–
Type
bug
Priority
major
Status
resolved
Component
sql
Milestone
1.1
Votes
0
Watchers
1
Jira: the preferred issue tracker for Bitbucket. Join the team!