(Python/MySQL/JSON) Not enough arguments for format string

Question

Can anybody help me figure out what I'm doing stupidly? I'm attempting to propagate an SQL table with financial data from a json file. I get the error in the title, but I can't seem to figure out where it's coming from.

import json
import MySQLdb

open_time     = 0
openp         = 0
high          = 0
low           = 0
closep        = 0
volume        = 0
close_time    = 0
quoteassetvol = 0
trades        = 0
ignore1       = 0
ignore2       = 0
ignore3       = 0

con = MySQLdb.connect(host='localhost',user='root',db='binance_adabtc',passwd='abcde')
cur = con.cursor()

symbols = ["(JSON_EXTRACT('json_obj','$[i][open_time]'), (JSON_EXTRACT('json_obj','$[i][openp]'), (JSON_EXTRACT('json_obj','$[i][high]'), (JSON_EXTRACT('json_obj','$[i][low]'), (JSON_EXTRACT('json_obj','$[i][closep]'),(JSON_EXTRACT('json_obj','$[i][volume]'), (JSON_EXTRACT('json_obj','$[i][close_time]'), (JSON_EXTRACT('json_obj','$[i][quoteassetvol]'), (JSON_EXTRACT('json_obj','$[i][trades]'),(JSON_EXTRACT('json_obj','$[i][ignore1]'), (JSON_EXTRACT('json_obj','$[i][ignore2]'), (JSON_EXTRACT('json_obj','$[i][ignore3]'))"]

json_file = open("C:\Users\Mike\Desktop\Binance\Binance_ADABTC_5m_1512086400000-1529971200000.json","r")
json_obj = json.load(json_file)
json_file.close()

column_str = "(open_time, openp, high, low, closep, volume, close_time, quoteassetvol, trades, ignore1, ignore2, ignore3)"
insert_str = ("%s, "*12)[:-2]
final_str  = "INSERT INTO Binance_ADABTC_5m_1512086400000_1529971200000 (%s) VALUES (%s)" % (column_str, insert_str)

for i in range (0,178848):
    cur.execute(final_str,symbols)

con.commit()
#cnx.commit()
con.close()

The data from the file looks like this:

[[1512086400000, "0.00001204", "0.00001209", "0.00001161", "0.00001183", "771721.00000000", 1512086699999, "9.10638040", 126, "359700.00000000", "4.22792312", "52516956.22676400"], [1512086700000, "0.00001189", "0.00001194", "0.00001183", "0.00001189", "119120.00000000", 1512086999999, "1.41575664", 44, "49016.00000000", "0.58377518", "52659721.84287900"], [1512087000000, "0.00001191", "0.00001196", "0.00001183", "0.00001190", "260885.00000000", 1512087299999, "3.10077566", 65, "152594.00000000", "1.81778662", "52859721.84287900"], [1512087300000, "0.00001190", "0.00001196", "0.00001181", "0.00001184", "508088.00000000", 1512087599999, "6.03010107", 95, "123506.00000000", "1.46831546", "52859721.84287900"], [1512087600000, "0.00001187", "0.00001190", "0.00001171", "0.00001174", "312477.00000000", 1512087899999, "3.69618651", 63, "155121.00000000", "1.84118817", "53289721.44287900"], [1512087900000, "0.00001170", "0.00001177", "0.00001155", "0.00001156", "296718.00000000", 1512088199999, "3.43350676", 66, "148.....

The full stack trace is:
    Traceback (most recent call last):
  File "C:/Users/Mike/.PyCharmCE2018.1/config/scratches/scratch_6.py", line 27, in <module>
    cur.execute(final_str,symbols)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not enough arguments for format string

Answer 1

I may be missing something here, the I'm not quit getting the big workaround with the symbols. If you simply want to pull the data from the json rows and put them in their respected columns in a database then it should be a lot simpler. Maybe something like this.

import json
import MySQLdb

con = MySQLdb.connect(host='localhost',user='root',db='binance_adabtc',passwd='abcde')
cur = con.cursor()

json_file = open("C:\Users\Mike\Desktop\Binance\Binance_ADABTC_5m_1512086400000-1529971200000.json","r")
json_obj = json.load(json_file)
json_file.close()

column_str = "(open_time, openp, high, low, closep, volume, close_time, quoteassetvol, trades, ignore1, ignore2, ignore3)"
insert_str = ("%s, "*12)[:-2]
final_str = """INSERT INTO Binance_ADABTC_5m_1512086400000_1529971200000 (%s) VALUES (%s)""" % (column_str, insert_str)

for i in json_obj:
    #print final_str % tuple(i)
    cur.execute(final_str,tuple(i))

con.commit()
cnx.commit()
con.close()

Another slightly different more pythonic approach. If you are having problems with the database column formating try dropping your table and running this. It has the create table as well.

import json
import MySQLdb

#Open db and create cursor
con = MySQLdb.connect(host='localhost',
                      user='root',
                      db='binance_adabtc',
                      passwd='abcde')
cur = con.cursor()

#Filepath
filePath = """C:\Users\Mike\Desktop\Binance\Binance_ADABTC_5m_1512086400000-1529971200000.json"""

#Open file and put it in list
json_file = open(filePath,"r")
json_list = json.load(json_file)
json_file.close()

#Split filepath the extract name
table_name = filePath.split("\\")[-1].split(".")[0].replace("-", "_")

#MySQL create table statement
cur.execute("""CREATE TABLE IF NOT EXISTS {} (id INT PRIMARY KEY AUTO_INCREMENT, \
                                  open_time BIGINT, \
                                  openp FLOAT, \
                                  high FLOAT, \
                                  low FLOAT, \
                                  closep FLOAT, \
                                  volume FLOAT, \
                                  close_time BIGINT, \
                                  quoteassetvol FLOAT, \
                                  trades INT, \
                                  ignore1 FLOAT, \
                                  ignore2 FLOAT, \
                                  ignore3 FLOAT)""".format(table_name))
con.commit()

#MySQL create table and insert statement
insert = """INSERT INTO {} (open_time, \
                            openp, \
                            high, \
                            low, \
                            closep, \
                            volume, \
                            close_time, \
                            quoteassetvol, \
                            trades, \
                            ignore1, \
                            ignore2, \
                            ignore3) \
                            VALUES ({})""".format(table_name, ("%s, "*12)[:-2])
#Print the insert if needed
#print " ".join(insert.split())

#Loop through list and insert
for i in json_list:
    cur.execute(insert, tuple(i))

#Commit and close
con.commit()
con.close()

Take note that the cursor.execute() needs tuples instead of lists for multiple inputs.