(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

Really need a few lines of the file or a sample file to tell whats going on. — TysonU, Jun 29 '18 at 01:07
It looks like `cur.execute()` needs the `symbols` to be expanded, e.g. `cur.execute(final_str, *symbols)` as it is just iterating over `*args`. Note: `', '.join(['%s']*12)` would be a more canonical form of creating `insert_str` vs. `("%s, "*12)[:-2]`. — AChampion, Jun 29 '18 at 01:16
@AChampion, so I expanded out symbols as you suggested and added the asterisk and got this new error: TypeError: execute() takes at most 3 arguments (514 given) And thank you for the note — Mikey Mike, Jun 29 '18 at 01:33
Because `symbols` is just one long string... you actually need to parse this into separate arguments. I looked at the docs and my previous assumption was wrong - you need a sequence with all of the args, which you had in the first place but you currently have a list of `1` string. And you need a list with `12` items. — AChampion, Jun 29 '18 at 01:44

TysonU · Accepted Answer · 2018-06-29T21:18:23.333

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.

Thank you for the code, it works! Do you mind elaborating on how the tuple(i) function can be used in this case to replace where I had *symbols* ? — Mikey Mike, Jun 29 '18 at 16:09
@MikeyMike To be honest, i'm still not sure exactly what you had been trying to achieve by using the symbols. (Since I couldn't ever get it to work myself.) The json converts the text file into a list that contains a list with the columns that will be stored in the database. Also, if I understand the MySQL library correctly, the cursor takes in tuples instead of lists, so that's the reason for converting the list into a tuple. I may update my answer with a more pythonic way of doing this yet."Way less code" — TysonU, Jun 29 '18 at 19:49
@MikeyMike Updated my answer and added a create table part so I could actually create and see the data myself. Works fine for me. — TysonU, Jun 29 '18 at 21:20

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

1 Answers1