Description
Bug Report
OperationalError: (1046, 'No database selected') when querying json attribute
Description
Following the tutorial in datajoint docs here to use the json datatype. Using the same schema and Team table as in tutorial, I run into an error when querying the car attribute like so:Team & {'car.length': 100}. However, using this syntax works: Team & "car->>'$.length' < 50"
Reproducibility
Include:
- OS MacOS Monterey 12.4
- Python Version 3.8.18
- MySQL Ver 14.14 Distrib 5.7.24, for osx10.9 (x86_64) using EditLine wrapper
- MySQL Deployment Strategy: local-docker
- DataJoint Version: 0.14.1
- Complete error stack as a result of evaluating the above steps:
{
"name": "OperationalError",
"message": "(1046, 'No database selected')",
"stack": "---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/IPython/core/formatters.py:708, in PlainTextFormatter.call(self, obj)
701 stream = StringIO()
702 printer = pretty.RepresentationPrinter(stream, self.verbose,
703 self.max_width, self.newline,
704 max_seq_length=self.max_seq_length,
705 singleton_pprinters=self.singleton_printers,
706 type_pprinters=self.type_printers,
707 deferred_pprinters=self.deferred_printers)
--> 708 printer.pretty(obj)
709 printer.flush()
710 return stream.getvalue()
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/IPython/lib/pretty.py:410, in RepresentationPrinter.pretty(self, obj)
407 return meth(obj, self, cycle)
408 if cls is not object \
409 and callable(cls.dict.get('repr')):
--> 410 return _repr_pprint(obj, self, cycle)
412 return _default_pprint(obj, self, cycle)
413 finally:
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/IPython/lib/pretty.py:778, in repr_pprint(obj, p, cycle)
776 """A pprint that just redirects to the normal repr function."""
777 # Find newlines and replace them with p.break()
--> 778 output = repr(obj)
779 lines = output.splitlines()
780 with p.group():
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/expression.py:646, in QueryExpression.repr(self)
635 def repr(self):
636 """
637 returns the string representation of a QueryExpression object e.g. str(q1).
638
(...)
641 :rtype: str
642 """
643 return (
644 super().repr()
645 if config["loglevel"].lower() == "debug"
--> 646 else self.preview()
647 )
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/expression.py:651, in QueryExpression.preview(self, limit, width)
649 def preview(self, limit=None, width=None):
650 """:return: a string of preview of the contents of the query."""
--> 651 return preview(self, limit, width)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/preview.py:13, in preview(query_expression, limit, width)
11 if width is None:
12 width = config["display.width"]
---> 13 tuples = rel.fetch(limit=limit + 1, format="array")
14 has_more = len(tuples) > limit
15 tuples = tuples[:limit]
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/fetch.py:258, in Fetch.call(self, offset, limit, order_by, format, as_dict, squeeze, download_path, *attrs)
256 ret = return_values[0] if len(attrs) == 1 else return_values
257 else: # fetch all attributes as a numpy.record_array or pandas.DataFrame
--> 258 cur = self._expression.cursor(
259 as_dict=as_dict, limit=limit, offset=offset, order_by=order_by
260 )
261 heading = self._expression.heading
262 if as_dict:
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/expression.py:633, in QueryExpression.cursor(self, offset, limit, order_by, as_dict)
631 sql += " LIMIT %d" % limit + (" OFFSET %d" % offset if offset else "")
632 logger.debug(sql)
--> 633 return self.connection.query(sql, as_dict=as_dict)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/connection.py:342, in Connection.query(self, query, args, as_dict, suppress_warnings, reconnect)
340 cursor = self._conn.cursor(cursor=cursor_class)
341 try:
--> 342 self._execute_query(cursor, query, args, suppress_warnings)
343 except errors.LostConnectionError:
344 if not reconnect:
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/connection.py:298, in Connection._execute_query(cursor, query, args, suppress_warnings)
296 cursor.execute(query, args)
297 except client.err.Error as err:
--> 298 raise translate_query_error(err, query)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/datajoint-0.14.1-py3.8.egg/datajoint/connection.py:296, in Connection._execute_query(cursor, query, args, suppress_warnings)
293 if suppress_warnings:
294 # suppress all warnings arising from underlying SQL library
295 warnings.simplefilter("ignore")
--> 296 cursor.execute(query, args)
297 except client.err.Error as err:
298 raise translate_query_error(err, query)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/cursors.py:153, in Cursor.execute(self, query, args)
149 pass
151 query = self.mogrify(query, args)
--> 153 result = self._query(query)
154 self._executed = query
155 return result
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/cursors.py:322, in Cursor._query(self, q)
320 conn = self._get_db()
321 self._clear_result()
--> 322 conn.query(q)
323 self._do_get_result()
324 return self.rowcount
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/connections.py:558, in Connection.query(self, sql, unbuffered)
556 sql = sql.encode(self.encoding, "surrogateescape")
557 self._execute_command(COMMAND.COM_QUERY, sql)
--> 558 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
559 return self._affected_rows
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/connections.py:822, in Connection._read_query_result(self, unbuffered)
820 else:
821 result = MySQLResult(self)
--> 822 result.read()
823 self._result = result
824 if result.server_status is not None:
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/connections.py:1200, in MySQLResult.read(self)
1198 def read(self):
1199 try:
-> 1200 first_packet = self.connection._read_packet()
1202 if first_packet.is_ok_packet():
1203 self._read_ok_packet(first_packet)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/connections.py:772, in Connection._read_packet(self, packet_type)
770 if self._result is not None and self._result.unbuffered_active is True:
771 self._result.unbuffered_active = False
--> 772 packet.raise_for_error()
773 return packet
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/protocol.py:221, in MysqlPacket.raise_for_error(self)
219 if DEBUG:
220 print("errno =", errno)
--> 221 err.raise_mysql_exception(self._data)
File ~/opt/anaconda3/envs/mea/lib/python3.8/site-packages/pymysql/err.py:143, in raise_mysql_exception(data)
141 if errorclass is None:
142 errorclass = InternalError if errno < 1000 else OperationalError
--> 143 raise errorclass(errno, errval)
OperationalError: (1046, 'No database selected')"
}
Expected Behavior
I expected same query output as in tutorial.
Additional Research and Context
I have installed latest datajoint version from source using pip as conda seems to only pull older 0.13 version. Was also running into a separate error with the table definition line for unique index, which I then commented out of my code:
unique index(car.length:decimal(4, 1)) # Add an index if this key is frequently accessed
Error:
("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(json_value(car, _utf8mb4'$.length' returning decimal(4, 1))))\n) ENGINE=InnoDB' at line 5", 'CREATE TABLE IF NOT EXISTS root_json.#team2 (\nname varchar(40) NOT NULL COMMENT "team name",\ncar json DEFAULT NULL COMMENT "A car belonging to a team (null to allow registering first but specifying car later)",\nPRIMARY KEY (name),\nunique index ((json_value(car, _utf8mb4'$.length' returning decimal(4, 1))))\n) ENGINE=InnoDB, COMMENT "A team within a company"')