0

I'm working with sqlite3 JSON1 extension in python3. The following code returns the output as string, but I'd like it to be a JSON object. Is this even possible in sqlite3 WITHOUT using json.loads()? 

conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('''SELECT json_quote(json_object('a',2,'c',json_object('e',5))),  
            json_array(1,2,'3',4),
            json_object('a',2,'c',json_object('e',5))            
         ''')
row = c.fetchone()
col_a, col_b, col_c = row
print(row)
print(type(col_a), type(col_b), type(col_c))

Output

('{"a":2,"c":{"e":5}}', '[1,2,"3",4]', '{"a":2,"c":{"e":5}}')
<class 'str'> <class 'str'> <class 'str'>

It would be better if I can get it to work via SQLalchemy (again, I would like to avoid using json.loads())

from sqlalchemy import create_engine, Column, String, MetaData, Table, JSON
from sqlalchemy.orm import mapper
from sqlalchemy.sql import text

engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData(bind=engine)

table = Table('device', metadata, Column('id', String, primary_key=True), Column('properties', JSON))
table.create()
properties = {'id': 'id1', 'device_name': 'device1', 'role': 'backbone', 'in_maint': True}
table.insert().values(id=properties['id'], properties=properties).execute()


class SqliteTable(object):
    pass


mapper(SqliteTable, table)

sql_string = '''SELECT json_extract(device.properties, '$') AS properties
                FROM device'''
sql_params = {}

with engine.connect() as connection:
    rows = connection.execute(text(sql_string), **sql_params).fetchall()

results = [{k: v for k, v in row.items()} for row in rows]
print(results)
engine.dispose()

The properties value is a string and not a JSON object.

[{'properties': '{"id":"id1","device_name":"device1","role":"backbone","in_maint":true}'}]

EDIT: I do not know upfront which columns returned will have JSON data in them, and therefore, the need to do this without performing a json.loads() on each column's value. Ultimately, this data will be returned back to the user in JSON. The API users will be executing raw SQL queries.

mru
  • 1
  • 2
  • At some point you have to turn json into a parsed form to do things with the data, so no, you can't avoid it. – Shawn Apr 24 '20 at 03:02
  • @Shawn I guess they were just trying to pass the buck to the libraries in use, which can be done using `TextClause.columns()` in SQLA. – Ilja Everilä Apr 24 '20 at 06:22
  • Thanks for your comments. I do not know upfront which columns returned will have JSON data in them, and therefore, the need to do this without performing a json.loads() on each column's value. Ultimately, this data will be returned back to the user in JSON. The API users will be executing raw SQL queries. – mru Apr 24 '20 at 13:44
  • How would you not know the type of what you are querying? – Ilja Everilä Apr 24 '20 at 14:25
  • The type of column in all tables are known, but I'll have no idea what tables are being queried (except if i look into the raw query and parse it). For example, someone could be querying the devices table and selecting three fields: - id (string), json_extract(properties), json_object(properties) how do i know before hand that columns 2 and 3 need to be converted to JSON before sending it back to the API user (in a JSON form and not as a string). Not sure if I'm communicating this correctly. Thanks – mru Apr 24 '20 at 20:46

0 Answers0