How to read JSON value in MySQL

'ohlc': {'open': 22719.25, 'high': 22880.0, 'low': 22665.4, 'close': 22610.75}

The JSON output I need to insert into my database but my source code throws an error:

The source code:

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='', database='connect')

insert_data_into_table = "insert into ticks(last_price,date,Volume,ins_token,ohlc) values(%(last_price)s,%(date)s,%(Volume)s," 
                         "%(ins_token)s, %(ohlc)s)"


def insert_ticks(ticks):
    cursor = conn.cursor()
    for tick in ticks:
        cursor.execute(insert_data_into_table,{'last_price': tick['last_price'], 'date': tick['timestamp'], 'Volume': tick['volume'], 'ins_token': tick['instrument_token'], 'ohlc':tick['ohlc']})
    try:
        conn.commit()
    except Exception:
        conn.rollback()

Can anyone help me with this, as I need to get values for open, close, high and low.

error:

pymysql.err.ProgrammingError: (1064, "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 ''open': '22755.65', 'high': '22820', 'low': '22325.4', 'close': '22908.1'})' at line 1")

Answer

1. you have 5 columns, hence 5 bind variables. Your insert template is over complicated
2. with any database operations you should be working with batches not row by row. You really should use execute many()
3. I assume that ohlc is JSON, just need to convert it to a string

import mysql.connector
import json
from datetime import datetime
conn = mysql.connector.connect(host="127.0.0.1",user="sniffer",passwd="sniffer",database="sniffer")
curr = conn.cursor()
try: curr.execute("drop table ticks")
except: pass
curr.execute("create table ticks (last_price double, date datetime, Volume double, ins_token varchar(20), ohlc json)")
ohlc =  {'open': 22719.25, 'high': 22880.0, 'low': 22665.4, 'close': 22610.75}
ticks = [{"last_price": 100.2, "timestamp":str(datetime.now()), "volume":30, 
          "instrument_token":"APPL", "ohlc":ohlc}]
ins = "insert into ticks(last_price,date,Volume,ins_token,ohlc) values(%s, %s, %s, %s, %s)"
for tick in ticks:
    curr.execute(ins, (tick["last_price"], 
                       tick["timestamp"], 
                       tick["volume"], 
                       tick["instrument_token"],
                       json.dumps(tick["ohlc"])) )

curr.execute("select * from ticks")
curr.fetchall()

output

[(100.2,
  datetime.datetime(2020, 7, 11, 15, 51, 33),
  30.0,
  'APPL',
  b'{"open": 22719.25, "high": 22880.0, "low": 22665.4, "close": 22610.75}')]