如何在mysql中读取json值

fruv7luv 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(612)

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

我需要将json输出插入到数据库中，但我的源代码引发了一个错误：
源代码：

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()

有人能帮我吗，因为我需要得到开、关、高和低的值。
错误：

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")

sql mysql python mysql-python pymysql

来源：https://stackoverflow.com/questions/62845572/how-to-read-json-value-in-mysql

1条答案

按热度按时间

cgyqldqp1#

您有5列，因此有5个绑定变量。你的插入模板太复杂了
对于任何数据库操作，您都应该处理批处理，而不是逐行处理。你真的应该用 execute many() 我假设ohlc是json，只需要将它转换成一个字符串

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()

输出

[(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}')]

赞(0）回复(0）举报 2021-07-24

我来回答

如何在mysql中读取json值

1条答案

相关问题

热门标签

最新问答