Sqlalchemy support for NOT NULL

[zopyx]

The Kotti table specs seems to define a NOT NULL constraint. This causes the following error:

Traceback (most recent call last): File "bin/pserve", line 11, in sys.exit(main()) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/pyramid/scripts/pserve.py", line 51, in main return command.run() File "/Users/ajung/src/kotti/lib/python2.7/site-packages/pyramid/scripts/pserve.py", line 316, in run global_conf=vars) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/pyramid/scripts/pserve.py", line 340, in loadapp return loadapp(app_spec, name=name, relative_to=relative_to, _kw) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 247, in loadapp return loadobj(APP, uri, name=name, _kw) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 272, in loadobj return context.create() File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 710, in create return self.object_type.invoke(self) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 203, in invoke app = context.app_context.create() File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 710, in create return self.object_type.invoke(self) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 146, in invoke return fix_call(context.object, context.global_conf, _context.local_conf) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/util.py", line 55, in fix_call val = callable(_args, _kw) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/kotti/init.py", line 165, in main initialize_sql(engine) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/kotti/resources.py", line 782, in initialize_sql metadata.create_all(engine, tables=tables) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 3404, in create_all tables=tables) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1616, in _run_visitor conn._run_visitor(visitorcallable, element, _kwargs) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1245, in _run_visitor _kwargs).traverse_single(element) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 120, in traverse_single return meth(obj, *_kw) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 713, in visit_metadata self.traverse_single(table, create_ok=True) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 120, in traverse_single return meth(obj, **kw) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 732, in visit_table self.connection.execute(CreateTable(table)) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute return meth(self, multiparams, params) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 69, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 783, in _execute_ddl compiled File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context context) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception exc_info File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute cursor.execute(statement, parameters) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/cursor.py", line 53, in execute self._result = self.connection.client.sql(sql, parameters, bulk_parameters) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/http.py", line 205, in sql content = self._json_request('POST', self.path, data=data) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/http.py", line 365, in _json_request self._raise_for_status(response) File "/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/http.py", line 351, in _raise_for_status raise ProgrammingError(error.get('message', ''), error_trace=error_trace) sqlalchemy.exc.ProgrammingError: (ProgrammingError) SQLActionException[line 3:13: mismatched input 'NOT' expecting ')'] '\nCREATE TABLE tags (\n\tid INTEGER NOT NULL, \n\ttitle VARCHAR(100) NOT NULL, \n\tPRIMARY KEY (id), \n\tUNIQUE (title)\n)\n\n' ()

[chaudum]

first of all, table creation in general is not supported by our sqlalchemy dialect yet.

that is the reason why sqlalchemy creates an SQL statement that is not supported by Crate:

• the NOT NULL constraint for columns (will add it as an item to the backlog)
• VARCHAR(100) - crate only has a STRING data type (https://crate.io/docs/en/stable/sql/data_types.html)

@mfussenegger worked on table creation support for simple types (no OBJECT/ARRAY) during the snowsprint last week, however, it is not yet merged into master

[mfussenegger]

I've made a 0.13 release today which includes support for table creation using the ORM functionality for basic types (custom fields inside objects is still a TODO).

This should fix the NON NULL error here. Can you see if you can get it working now?

Will close this, feel free to re-open if there's still something not working

[zopyx]

At least the NOT NULL error disappeared with Kotti now. However there is a followup error. Not sure if you want to proceed with this one :)

2015-03-10 20:31 GMT+01:00 Mathias Fußenegger notifications@github.com:

Closed crate/sqlalchemy-cratedb#132 https://github.com/crate/sqlalchemy-cratedb/issues/132.

— Reply to this email directly or view it on GitHub https://github.com/crate/sqlalchemy-cratedb/issues/132.

[mfussenegger]

Can you paste the follow up error? Would be interesting to see what else we're missing.

Did you simply use a crate connection string in Kotti/SQLAlchemy or was there more tweaking involved to get to the point you're at? Would actually be interested to try that stuff myself =)

[zopyx]

I am connecting to

sqlalchemy.url = crate://xxxx.our-domain.de:4200

Traceback (most recent call last):
  File "bin/pserve", line 11, in <module>
    sys.exit(main())
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/pyramid/scripts/pserve.py",
line 51, in main
    return command.run()
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/pyramid/scripts/pserve.py",
line 316, in run
    global_conf=vars)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/pyramid/scripts/pserve.py",
line 340, in loadapp
    return loadapp(app_spec, name=name, relative_to=relative_to, **kw)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py",
line 247, in loadapp
    return loadobj(APP, uri, name=name, **kw)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py",
line 272, in loadobj
    return context.create()
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py",
line 710, in create
    return self.object_type.invoke(self)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py",
line 203, in invoke
    app = context.app_context.create()
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py",
line 710, in create
    return self.object_type.invoke(self)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/loadwsgi.py",
line 146, in invoke
    return fix_call(context.object, context.global_conf,
**context.local_conf)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/paste/deploy/util.py",
line 55, in fix_call
    val = callable(*args, **kw)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/kotti/__init__.py",
line 165, in main
    initialize_sql(engine)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/kotti/resources.py",
line 782, in initialize_sql
    metadata.create_all(engine, tables=tables)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
line 3404, in create_all
    tables=tables)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1616, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1245, in _run_visitor
    **kwargs).traverse_single(element)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
line 120, in traverse_single
    return meth(obj, **kw)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
line 713, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py",
line 120, in traverse_single
    return meth(obj, **kw)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
line 732, in visit_table
    self.connection.execute(CreateTable(table))
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 729, in execute
    return meth(self, multiparams, params)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",
line 69, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 783, in _execute_ddl
    compiled
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 958, in _execute_context
    context)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1159, in _handle_dbapi_exception
    exc_info
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 951, in _execute_context
    context)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 436, in do_execute
    cursor.execute(statement, parameters)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/cursor.py",
line 53, in execute
    self._result = self.connection.client.sql(sql, parameters,
bulk_parameters)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/http.py",
line 205, in sql
    content = self._json_request('POST', self.path, data=data)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/http.py",
line 365, in _json_request
    self._raise_for_status(response)
  File
"/Users/ajung/src/kotti/lib/python2.7/site-packages/crate/client/http.py",
line 351, in _raise_for_status
    raise ProgrammingError(error.get('message', ''),
error_trace=error_trace)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) SQLActionException[line
7:9: no viable alternative at input 'TEXT'] '\nCREATE TABLE nodes (\n\tid
INT, \n\ttype STRING, \n\tparent_id INT, \n\tposition INT, \n\t"_acl" TEXT,
\n\tname STRING, \n\ttitle STRING, \n\tannotations TEXT, \n\tpath STRING,
\n\tPRIMARY KEY (id), \n\tUNIQUE (parent_id, name), \n\tFOREIGN
KEY(parent_id) REFERENCES nodes (id)\n)\n\n' ()

2015-03-12 16:50 GMT+01:00 Mathias Fußenegger notifications@github.com:

Can you paste the follow up error? Would be interesting to see what else we're missing.

Did you simply use a crate connection string in Kotti/SQLAlchemy or was there more tweaking involved to get to the point you're at? Would actually be interested to try that stuff myself =)

— Reply to this email directly or view it on GitHub https://github.com/crate/sqlalchemy-cratedb/issues/132.

[chaudum]

hi @zopyx

I can see that there is a TEXT type in the table creation statement. Crate has only a STRING type which can be used for all string/text columns (see Data Types)

We'll also add a mapping from TEXT to STRING.

[zopyx]

I am aware of that :-)

2015-03-13 9:48 GMT+01:00 Christian Haudum notifications@github.com:

hi @zopyx https://github.com/zopyx

I can see that there is a TEXT type in the table creation statement. Crate has only a STRING type which can be used for all string/text columns (see Data Types https://crate.io/docs/en/latest/sql/data_types.html)

— Reply to this email directly or view it on GitHub https://github.com/crate/sqlalchemy-cratedb/issues/132.

[chaudum]

@zopyx I've added the type mapping to the Crate dialect: https://github.com/crate/crate-python/commit/9ad2141e2ebc926ef72327d82869e9e86783a364 and the change is now in master