Results are different between python and mysql for similar command

Question

I was using python to create a dictionary but as my data got larger I started to get memory errors so I thought I would save memory and just write the data to a database instead, but the results are not the same. I think this has to do with defaultdict's behavior(but I'm not sure).

Here's the working python code(it basically builds a table of values):

from collections import defaultdict
data = [2,5,10]
target_sum = 100
# T[x, i] is True if 'x' can be solved
# by a linear combination of data[:i+1]
T = defaultdict(bool)           # all values are False by default
T[0, 0] = True                # base case

for i, x in enumerate(data):    # i is index, x is data[i]
    for s in range(target_sum + 1): #set the range of one higher than sum to include sum itself
        for c in range(s / x + 1):  
            if T[s - c * x, i]:
                T[s, i+1] = True

#check the python dict results
count = 0
for x in T:
    if T[x] == True:
        print x, ':', T[x]
        count = count +1

print 'total count is ', count
#False is 152 and True is 250.  Total is: 402

The result is a large table of values(you can see the breakdown in the comment. This is the correct result I want), but when I change the last line of the first for statement to add to a database and not to a local dict, the results differ.

Here's my modified code that is problematic:

cursor = conn.cursor ()
cursor = conn.cursor ()
cursor.execute ("DROP TABLE IF EXISTS data_table")
cursor.execute ("""
    CREATE TABLE data_table
    (
      value     CHAR(80),
      state BOOL
    )
  """)
#with database
for i, x in enumerate(data):    # i is index, x is data[i]
    for s in range(target_sum + 1): #set the range of one higher than sum to include sum itself
        for c in range(s / x + 1):  
            cursor.execute(""" SELECT value, state FROM data_table WHERE value='%s' """ % ([s - c * x, i]))
            if cursor.rowcount == 0:
                #print 'nothing found, adding'
                cursor.execute (""" INSERT INTO data_table (value, state) VALUES ('%s', False)""" % ([s - c * x, i]))
            elif cursor.rowcount == 1:
                cursor.execute (""" UPDATE data_table SET state=True WHERE value = '%s'""" % ([s - c * x, i]))
                #print 'record updated'
    conn.commit()

#False is 17 and True is 286. Total is: 303

Just to sum it up(in case you don't want to run the code), defaultdict creates an false entry when something is queried( in this case if T[s - c * x, i]:) so to replicate this feature I do a mysql lookup for the value and if it doesn't exist then I create it, if it does exist then I set it to true. I highly suspect I'm failing to replicate the functionality correctly

The only other thing I was thinking is python displays the results as (222, 0) : False but mysql is doing [222,0] not sure if that makes a difference.

Answer 1

Your two examples are not updating the same key:

# First example
if T[s - c * x, i]:
    T[s, i+1] = True
    # Key is (s, i+1)

# Second example
elif cursor.rowcount == 1:
    cursor.execute (""" UPDATE data_table SET state=True WHERE value = '%s'""" % ([s - c * x, i]))
    # Key is (s - c * x, i)

IMO it would make more sense to just store the True cases in the database, that might make your program simpler. Otherwise, you'll also need to check if (s, i+1) exists in the database, update it to True if it does, create a new row if it doesn't.

P.S. I also missed the command where you set (0, 0) to True. Shouldn't that be in an insert, just after you created your database?

Update: also found another problem in your code: the select command just checks whether or not a row exists, not what its value is. To correctly replicate your first example, your code should be:

cursor.execute (""" INSERT INTO data_table (value, state) VALUES ('%s', True)""" % ([0, 0]))
conn.commit()
# Inserted the (0,0) case
for i, x in enumerate(data):
    for s in range(target_sum + 1):
        for c in range(s / x + 1):  
            cursor.execute(""" SELECT value, state FROM data_table WHERE value='%s' """ % ([s - c * x, i]))
            if cursor.rowcount == 0:
                cursor.execute (""" INSERT INTO data_table (value, state) VALUES ('%s', False)""" % ([s - c * x, i]))
            elif cursor.rowcount == 1:
                (value, state) = cursor.fetchone() # Gets the state
                if state: # equivalent to your if in the first example
                    insertOrUpdate(conn, [s, i+1])
    conn.commit()

Changed lines commented.

Update 2: that was not enough... (as I said, it'd be much simpler if you just stored the True values). Moved the part inside the if here, for readability:

def insertOrUpdate(conn, key):
    cursor.execute(""" SELECT value, state FROM data_table WHERE value='%s' """ % key)
        if cursor.rowcount == 0:
            # Insert as True if not exists
            cursor.execute (""" INSERT INTO data_table (value, state) VALUES ('%s', True)""" % key)
        elif cursor.rowcount == 1:
            (value, state) = cursor.fetchone()
            if !state:
                # Update as True, if it was False
                cursor.execute (""" UPDATE data_table SET state=True WHERE value = '%s'""" % key)

---

Update 3: Just to contrast, look how the program would be simpler by just storing the True values. It also uses less disk space, takes less time and behaves more like the defaultdict.

cursor = conn.cursor ()
cursor.execute ("DROP TABLE IF EXISTS data_table")
cursor.execute ("""
    CREATE TABLE data_table(
        value     CHAR(80)
    )
""")

cursor.execute (""" INSERT INTO data_table (value) VALUES ('%s')""" % [0, 0])
conn.commit()

for i, x in enumerate(data):    # i is index, x is data[i]
    for s in range(target_sum + 1): #set the range of one higher than sum to include sum itself
        for c in range(s / x + 1):  
            cursor.execute(""" SELECT value FROM data_table WHERE value='%s' """ % ([s - c * x, i]))
            if cursor.rowcount == 1:
                cursor.execute(""" SELECT value FROM data_table WHERE value='%s' """ % [s, i+1])
                if cursor.rowcount == 0:
                    cursor.execute (""" INSERT INTO data_table (value) VALUES ('%s')""" % [s, i+1])
    conn.commit()