OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

json.decoder.JSONDecodeError: Extra data - error when reading from database using python

  • Thread starter Thread starter Anand
  • Start date Start date
A

Anand

Guest
I am trying to read a json column from SingleStore database using 'singlestoredb' library in Python. Below is the code.

Code:
import singlestoredb as s2
import pandas as pd

query = """
select  
uuid   
,output           
,environment        
from table_name where  uuid = 'aaa' and environment = 'live'
"""

conn = s2.connect(
    user = " ",
    password = " ",
    host = " ",
    port = " ",
    database = " ",
    results_type='dict',
    charset='utf8mb4'
    )

with conn.cursor() as cur:
    cur.execute(query)
    df = pd.DataFrame(cur.fetchall()) 
    print(df)

This piece of code is throwing below exception ...

Code:
Traceback (most recent call last):
  File ".../Python/test_singlestoredb.py", line 61, in <module>
    cur.execute(query)
  File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/cursors.py", line 208, in execute
    result = self._query(query)
  File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/cursors.py", line 393, in _query
    conn.query(q)
  File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 863, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 1228, in _read_query_result
    result.read()
  File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 1643, in read
    self._read_result_packet(first_packet)
  File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 1725, in _read_result_packet
    self._read_rowdata_packet()
  File "/usr/local/Cellar/[email protected]/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/json/__init__.py", line 346, in loads
    return _default_decoder.decode(s)
  File "/usr/local/Cellar/[email protected]/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/json/decoder.py", line 340, in decode
    raise JSONDecodeError("Extra data", s, end)
json.decoder.JSONDecodeError: Extra data: line 1 column 137182 (char 137181)

But, same script works fine when I put 'output' column at the end of the select list like below.

Code:
query = """
select  
uuid   
,environment    
,output               
from table_name where  uuid = 'aaa' and environment = 'live'
"""

'output' column contains a large JSON value. It is in proper json format.

Below is the column length details.

Code:
+--------------+----------------+----------------------+---------------------+---------------------+
| length(uuid) | length(output) | length(trim(output)) | length(output_text) | length(environment) |
+--------------+----------------+----------------------+---------------------+---------------------+
|           36 |         137189 |               137189 |              138342 |                   4 |
+--------------+----------------+----------------------+---------------------+---------------------+

Column definition in table: 'output' JSON COLLATE utf8mb4_general_ci DEFAULT NULL,

Can't put the 'output' column at the end always. Could you please help me on this.

Thanks, Anand.

Tried different encoding. Datatype conversion. Checked online.
<p>I am trying to read a json column from SingleStore database using 'singlestoredb' library in Python. Below is the code.</p>
<pre><code>import singlestoredb as s2
import pandas as pd

query = """
select
uuid
,output
,environment
from table_name where uuid = 'aaa' and environment = 'live'
"""

conn = s2.connect(
user = " ",
password = " ",
host = " ",
port = " ",
database = " ",
results_type='dict',
charset='utf8mb4'
)

with conn.cursor() as cur:
cur.execute(query)
df = pd.DataFrame(cur.fetchall())
print(df)

</code></pre>
<p>This piece of code is throwing below exception ...</p>
<pre><code>Traceback (most recent call last):
File ".../Python/test_singlestoredb.py", line 61, in <module>
cur.execute(query)
File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/cursors.py", line 208, in execute
result = self._query(query)
File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/cursors.py", line 393, in _query
conn.query(q)
File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 863, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 1228, in _read_query_result
result.read()
File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 1643, in read
self._read_result_packet(first_packet)
File ".../Library/Python/3.9/lib/python/site-packages/singlestoredb/mysql/connection.py", line 1725, in _read_result_packet
self._read_rowdata_packet()
File "/usr/local/Cellar/[email protected]/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/json/__init__.py", line 346, in loads
return _default_decoder.decode(s)
File "/usr/local/Cellar/[email protected]/3.9.5/Frameworks/Python.framework/Versions/3.9/lib/python3.9/json/decoder.py", line 340, in decode
raise JSONDecodeError("Extra data", s, end)
json.decoder.JSONDecodeError: Extra data: line 1 column 137182 (char 137181)

</code></pre>
<p>But, same script works fine when I put 'output' column at the end of the select list like below.</p>
<pre><code>query = """
select
uuid
,environment
,output
from table_name where uuid = 'aaa' and environment = 'live'
"""
</code></pre>
<p>'output' column contains a large JSON value. It is in proper json format.</p>
<p>Below is the column length details.</p>
<pre><code>+--------------+----------------+----------------------+---------------------+---------------------+
| length(uuid) | length(output) | length(trim(output)) | length(output_text) | length(environment) |
+--------------+----------------+----------------------+---------------------+---------------------+
| 36 | 137189 | 137189 | 138342 | 4 |
+--------------+----------------+----------------------+---------------------+---------------------+
</code></pre>
<p>Column definition in table:
<code>'output' JSON COLLATE utf8mb4_general_ci DEFAULT NULL,</code></p>
<p>Can't put the 'output' column at the end always. Could you please help me on this.</p>
<p>Thanks,
Anand.</p>
<p>Tried different encoding. Datatype conversion. Checked online.</p>
 

Latest posts

T
Replies
0
Views
1
Timothy Treaster
T
Top