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


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



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:UsersMikeDesktopBinanceBinance_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:Python27libsite-packagesMySQLdbcursors.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
2 days ago





Can you provide the full stack trace?
– Lane Terry
2 days ago





@TysonU, it's added now
– Mikey Mike
2 days ago





@LaneTerry, just added it
– Mikey Mike
2 days ago





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
2 days ago



cur.execute()


symbols


cur.execute(final_str, *symbols)


*args


', '.join(['%s']*12)


insert_str


("%s, "*12)[:-2]




1 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:UsersMikeDesktopBinanceBinance_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:UsersMikeDesktopBinanceBinance_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
2 days ago





@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
2 days ago





@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
yesterday






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

paramiko-expect timeout is happening after executing the command

paramiko-expect timeout is happening after executing the command Can someone help me why timeout is happening after executing the command .I am trying to SSH to a machine and execute a command.I want to store the result of the executed command . I have referred paramiko-timeout but i didn't get my expected result. import traceback try: import paramiko except ImportError: raise Exception("Please install paramiko , pip install paramiko") import pexpect from paramiko_expect import SSHClientInteraction def main(): HOSTNAME = "minion5.net" USERNAME = "root" PASSWORD = "help@432" PROMPT = "root@*:~$s+" try: client = paramiko.SSHClient() client.load_system_host_keys() client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) client.connect(hostname=HOSTNAME, username=USERNAME, password=PASSWORD) with SSHClientInteraction(client, timeout=20, display=True) as interact: ...
Read more

Opening a url is failing in Swift

Opening a url is failing in Swift breakpointing inside the callback and logging success shows false. I added itms-apps to my plist: LSApplicationQueriesSchemes and put this in my app delegate: func application(_ app: UIApplication, open url: URL, options: [UIApplicationOpenURLOptionsKey : Any] = [:]) -> Bool { return true } func moreTapped() { let url = NSURL(string: "itms-apps://itunes.com/developer/quantum-productions/id979315877") if #available(iOS 10.0, *) { UIApplication.shared.open(url! as URL, options: [:], completionHandler: {(success: Bool) in }) } else { UIApplication.shared.openURL(url! as URL) } } Are you testing on a real device? – rmaddy Jun 21 at 23:57 I try on a device, get "Cannot Connect to App Store". Device is online ...
Read more

Export result set on Dbeaver to CSV

Export result set on Dbeaver to CSV Normally I use Dbeaver for windows and always export my result set like this: run my query --> select the result --> export the result set --> select export to clipboard --> done This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it. The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below: exporting data set But once I go further in the process, in the last step I get: no query Note that in "source" it was suppose to show the query that originated the result set, but instead it says just "select. As a result it does't select my result or anything (besides being "successful"). Normally my query would show up there automatically and I couldn't find any option that corrects this problem in the menus. Please help me! Thanks. Aft...
Read more