2017-08-09 02:42:14 (edited by TJ.Breitenfeldt 2017-08-09 16:15:58)

Hi, I am working on trying to pull in data from the magic the gathering database into my local sqlite3 database, and I am using python 3.4 with mtgsdk. you can get mtgsdk from pip install mtgsdk.
I believe I have mostly gotten the program set up, but I am getting an error that I can't figure out.
I am just testing with 1 card right now to make sure it is working.

Part of my problem is that I am not certain what python data types I can use to pass into the insert into statement. I am under the assumption that only ints, floats ,and strings work ,but I don't know, I can't find anything on this to confirm this. Also, I am assuming that None does not work as sql NULL.So, a lot of my code is joining lists and dictionaries, and accounting for None's.

The error I am getting is:

Traceback (most recent call last):
  File "mtgsdkTester.py", line 89, in <module>
    main()
  File "mtgsdkTester.py", line 15, in main
    insertCard(connection, cardProperties)
  File "mtgsdkTester.py", line 86, in insertCard
    cur.execute(sql, temp)
ValueError: parameters are of unsupported type

here is my sql and python.

SQL:

CREATE TABLE Cards (
  "id" INT,
  "name" TEXT,
  "multiverse_id" INT,
  "names" TEXT,
  "mana_cost" TEXT,
  "cmc" INT,
  "colors" TEXT,
  "color_identity" TEXT,
  "type" TEXT,
  "supertypes" TEXT,
  "subtypes" TEXT,
  "rarity" TEXT,
  "text" TEXT,
  "flavor" TEXT,
  "power" TEXT,
  "toughness" TEXT,
  "loyalty" TEXT,
  "rulings" TEXT,
  "legalities" TEXT,
  "set_name" TEXT
);

python:

import sqlite3

from mtgsdk import Card

def main():
    dbFile = "cards.db"
    connection = createConnection(dbFile)
    cards = Card.where(name="The Great Aurora").all()
    cardProperties = getCardProperties(cards[0])
    insertCard(connection, cardProperties)


def getCardProperties(card):
    names = joinList(card.names)
    colors = joinList(card.colors)
    color_identity = joinList(card.color_identity)
    supertypes = joinList(card.supertypes)
    subtypes = joinList(card.subtypes)
    legalities = formatLegalities(card.legalities)
    
    return (1, card.name, card.multiverse_id, names, card.mana_cost, card.cmc, colors, color_identity, card.type, supertypes, subtypes, card.rarity, card.text, card.flavor, card.power,
        card.toughness,card.loyalty, card.rulings, legalities,card.set_name)


def joinList(cardProperty):
    if cardProperty != None:
        return "|".join(cardProperty)
    
    return cardProperty


def formatLegalities(legalities):
    if legalities == None:
        return legalities
    
    legalitiesStr = ""
    legalitiesLength = len(legalities)
    
    for index in range(0, legalitiesLength):
        legalitiesStr += legalities[index]["format"] + ": "
        legalitiesStr += legalities[index]["legality"]
        
        if (index + 1) != legalitiesLength:
            legalitiesStr += "|"
    
    return legalitiesStr


def createConnection(dbFile):
    try:
        connection = sqlite3.connect(dbFile)
        return connection
    except Error as e:
        print(e)
    
    return None


def insertCard(connection, cardProperties):
    sql = "INSERT INTO Cards VALUES("
    cur = connection.cursor()
    cardPropertiesLength = len(cardProperties)
    
    for index in range(cardPropertiesLength):
        if cardProperties[index] == None:
            sql += "NULL"
        else:
            sql += "?"
        
        if (index + 1) != cardPropertiesLength:
            sql += ","
    
    sql += ");"
    temp = filter(None, cardProperties)    
    cur.execute(sql, temp)


main()

Thanks,

TJ Breitenfeldt

2017-08-09 14:19:32

You have only provided the topmost line of the error which, coincidentally, is a line you always get with a Python error ...so just from this I couldn't tell you  what the problem is smile

Zersiax

2017-08-09 16:19:54

Thanks Zersiax for letting me know, I edited my original post. For some reason the site cut out most of my post, and stripped out new lines. Someone else on a different thread had the same problem. I think there is some bug in the audiogames site.

TJ Breitenfeldt

2017-08-10 04:33:11

Hi,
According to https://sqlite.org/datatype3.html, the types, in order, are:

  • NULL. The value is a NULL value.

  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  • BLOB. The value is a blob of data, stored exactly as it was input.

You may wish to change your list to a dictionary, since that can accept multiple types. Alternatively, change everything to a blob big_smile

"On two occasions I have been asked [by members of Parliament!]: 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out ?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question."    — Charles Babbage.
My Github

2017-08-11 02:46:42

At Ethin, thank you for the reply, I have tried setting all of my datatypes to text already, and I was getting an error, once I changed the datatypes to match the python datatypes, it seemed to function better. I am not sure why. I tried changing everything to blob, but I got some other error, so I changed it back. I figured out the error I was having though.

I was using filter() to filter out all of the None values from my tuple, but as a result it was returning an iterator not a tuple, and the execute() function only takes tuples as an argument.
So, I casted the iterator to a tuple and I am now getting a new error.


Traceback (most recent call last):
  File "mtgsdkTester.py", line 90, in <module>
    main()
  File "mtgsdkTester.py", line 16, in main
    insertCard(connection, cardProperties)
  File "mtgsdkTester.py", line 86, in insertCard
    cur.execute(sql, temp)
sqlite3.OperationalError: table Cards has 19 columns but 20 values were supplied

I have counted the number of elements in the insert statement, number of items in the tuple (temp), and the number of columns in my sql, but I keep getting 20 for everything. Does anyone see the problem?

Here is my updated code for my sql and python:
note: I change the loyalty from text to int, didn't seem to make a difference since I am testing a non planeswalker, but I tested the value of the variable with a planeswalker, and it is an int.

sql:

CREATE TABLE Cards(
  "id" int
  "name" TEXT,
  "multiverse_id" INT,
  "names" TEXT,
  "mana_cost" TEXT,
  "cmc" INT,
  "colors" TEXT,
  "color_identity" TEXT,
  "type" TEXT,
  "supertypes" TEXT,
  "subtypes" TEXT,
  "rarity" TEXT,
  "text" TEXT,
  "flavor" TEXT,
  "power" TEXT,
  "toughness" TEXT,
  "loyalty" INT,
  "rulings" TEXT,
  "legalities" TEXT,
  "set_name" TEXT
);

python:

import sqlite3

from mtgsdk import Card

def main():
    dbFile = "cards.db"
    connection = createConnection(dbFile)
    cards = Card.where(name="The Great Aurora").all()
    cardProperties = getCardProperties(cards[0])    
    insertCard(connection, cardProperties)
    

def getCardProperties(card):
    names = joinList(card.names)
    colors = joinList(card.colors)
    color_identity = joinList(card.color_identity)
    supertypes = joinList(card.supertypes)
    subtypes = joinList(card.subtypes)
    legalities = formatLegalities(card.legalities)
    
    return (1, card.name, card.multiverse_id, names, card.mana_cost, card.cmc, colors, color_identity, card.type, supertypes, subtypes, card.rarity, card.text, card.flavor, card.power,
        card.toughness,card.loyalty, card.rulings, legalities,card.set_name)


def joinList(cardProperty):
    if cardProperty != None:
        return "|".join(cardProperty)
    
    return cardProperty


def formatLegalities(legalities):
    if legalities == None:
        return legalities
    
    legalitiesStr = ""
    legalitiesLength = len(legalities)
    
    for index in range(0, legalitiesLength):
        legalitiesStr += legalities[index]["format"] + ": "
        legalitiesStr += legalities[index]["legality"]
        
        if (index + 1) != legalitiesLength:
            legalitiesStr += "|"
    
    return legalitiesStr


def createConnection(dbFile):
    try:
        connection = sqlite3.connect(dbFile)
        return connection
    except Error as e:
        print(e)
    
    return None


def insertCard(connection, cardProperties):
    sql = "INSERT INTO Cards VALUES("
    cur = connection.cursor()
    cardPropertiesLength = len(cardProperties)
    
    for index in range(cardPropertiesLength):
        if cardProperties[index] == None:
            sql += "NULL"
        else:
            sql += "?"
        
        if (index + 1) != cardPropertiesLength:
            sql += ","
    
    
    sql += ");"
    temp = filter(None, cardProperties)
    temp = tuple(temp)
    cur.execute(sql, temp)


main()

Thanks,

TJ Breitenfeldt

2017-08-12 01:09:09

Your supposed to get 20. Your database has 20 columns, so you should be getting 20 values. I'm curious what the set_name column is for though. If it's not being used for anything, remove it, and remove it in the tuple. Then try reexecuting the statement again.
Another way to debug this is to open sqlite3's console and execute the statements one at a time. It's slower, for sure, but it will allow you to see what goes wrong where. So, back-up the current code you have, then change your python code to something like:

import sqlite3

from mtgsdk import Card

def main():
    dbFile = "cards.db"
    connection = createConnection(dbFile)
    cards = Card.where(name="The Great Aurora").all()
    cardProperties = getCardProperties(cards[0])    
    insertCard(connection, cardProperties)
    

def getCardProperties(card):
    names = joinList(card.names)
    colors = joinList(card.colors)
    color_identity = joinList(card.color_identity)
    supertypes = joinList(card.supertypes)
    subtypes = joinList(card.subtypes)
    legalities = formatLegalities(card.legalities)
    
    return (1, card.name, card.multiverse_id, names, card.mana_cost, card.cmc, colors, color_identity, card.type, supertypes, subtypes, card.rarity, card.text, card.flavor, card.power,
        card.toughness,card.loyalty, card.rulings, legalities,card.set_name)


def joinList(cardProperty):
    if cardProperty != None:
        return "|".join(cardProperty)
    
    return cardProperty


def formatLegalities(legalities):
    if legalities == None:
        return legalities
    
    legalitiesStr = ""
    legalitiesLength = len(legalities)
    
    for index in range(0, legalitiesLength):
        legalitiesStr += legalities[index]["format"] + ": "
        legalitiesStr += legalities[index]["legality"]
        
        if (index + 1) != legalitiesLength:
            legalitiesStr += "|"
    
    return legalitiesStr


def createConnection(dbFile):
    try:
        connection = sqlite3.connect(dbFile)
        return connection
    except Error as e:
        print(e)
    
    return None


def insertCard(connection, cardProperties):
    sql = "INSERT INTO Cards VALUES("
    cur = connection.cursor()
    cardPropertiesLength = len(cardProperties)
    
    for index in range(cardPropertiesLength):
        if cardProperties[index] == None:
            sql += "NULL"
        else:
            sql += "?"
        
        if (index + 1) != cardPropertiesLength:
            sql += ","
    
    
    sql += ");"
    temp = filter(None, cardProperties)
    temp = tuple(temp)
    # cur.execute(sql, temp)
    print (temp)


main()

That will give you the SQL statement generated. execute that in sqlite3 and see if it successfully executes. Then delete the data and fix the problem after that. (Alternatively, create a new database file as a temporary DB, set the DB file to the temp DB and execute the statement on that DB. Then delete the DB and fix the error then.)

"On two occasions I have been asked [by members of Parliament!]: 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out ?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question."    — Charles Babbage.
My Github