0

I'm pulling data from a website using their api. I'm then wanting to save the data into a database. For some of the fields I need to save a section of the json data that was pulled into one field.

The table layout is created using this:

cur.execute('create table if not exists KillData(KillID INT, Victim TEXT, KillTime TEXT, System INT,'
            ' Attackers TEXT, Items TEXT, Price REAL)')

This is what I have right now to insert the data:

for kill_mail in response:
    kill = KillMail(kill_mail)
    cur.execute("insert into KillData values(?, ?, ?, ?, ?, ?, ?)",
                (int(kill.get_kill_id()), kill.get_victim(), kill.get_kill_time(), kill.get_system(),
                 kill.get_attackers, kill.get_items(), kill.get_price()))

where KillMail is just a class which is used to get certain values within the json data. For example:

class KillMail:
def __init__(self, json_kill_mail):
    self.json_kill_mail = json_kill_mail

def get_attackers(self):
    return json.dumps(self.json_kill_mail['attackers'])

def get_items(self):
    return json.dumps(self.json_kill_mail['items'])

kill.get_attackers and kill.get_items() contain items within them because there may be only one attacker/item or up to 100 or more. This is why I'm thinking that saving the data in json format as a string will be best so that when pulling it I can just json.load() to reconstruct the json object to pull info from it.

The error I'm getting when running the code is:

in main kill.get_attackers, kill.get_items(), kill.get_price())) sqlite3.InterfaceError: Error binding parameter 4 - probably unsupported type.

The only thing I could find online about doing this is this.

I tried the suggested things in that post but nothing seemed to work. I also tried to change the two fields to BLOB's but that gave the same error.

So my question is: How can I save a json formatted string into one field?

I can post more info if needed.

Community
  • 1
  • 1
Cameron
  • 53
  • 7
  • 2
    Um. For starters, to save a json-formatted string you need to *pass it a json-formatted string*. `str()` doesn't json-format things, `json.dumps` does. This alone isn't your problem, but check the type of "parameter 1" in your query, which looks to be `kill.get_victim()`. – Steve Jessop May 19 '16 at 15:36
  • Thanks for that. I updated the post to correct those errors. The error now returns parameter 4 instead of 1 because I used your suggestion which fixed the issue with parameter 1. – Cameron May 19 '16 at 16:41
  • parameter 4 is `kill.get_attackers`, which is a function, not the result of calling the function. – Steve Jessop May 19 '16 at 21:46

1 Answers1

0

So, seems it was a typo on my part. For the table setup there was a set of '' that was not supposed to be in there. The table setup looked like this if you put it all on one line and not two lines like I had it:

cur.execute('create table if not exists KillData(KillID INT, Victim TEXT, Attackers TEXT, Items Text,'' KillTime TEXT, System INT, Price REAL)')

So, by removing the '' before KillTime the error no longer happens and the code works.

Cameron
  • 53
  • 7