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 _reprpprint(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/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)
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"')
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:
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 _reprpprint(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 accessedError: ("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 EXISTSroot_json.#team2(\nnamevarchar(40) NOT NULL COMMENT "team name",\ncarjson 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"')