How to handle Python list of strings in MySQL column expecting a varchar(255)?

Question

(MySQL newbie alert)

I receive back from an API call values for a field price, and they are almost always a string e.g.

u'price': u'$49.99',

Sometimes though, there are two prices (the former price and the current one), and the API places them in a Python list:

u'price': [
  u'$9.99',
  u'$1,099.99'
],

With my column price defined as a varchar(255), the insertion fails when the price is a list. I don't want to split such pairs of price out into two separate columns. I know I can use Python to decompose the list into a string before the insertion, but is there a way in MySQL to allow for both a string and a list?

oh boy don't do this. tell us what two values would mean to you, and also consider going the decimal(12,2) route or the like — Drew
– Drew, Commented Sep 6, 2015 at 14:47
If there are calculations, then it might make sense to store price in cents even, but that's very much depending on what's supposed to be done with it and by which entity. — Ashalynd
– Ashalynd, Commented Sep 6, 2015 at 14:59
@Ashalynd Yes as per Woot4Moo's surmising, it's received as a string, and I don't need to perform any operations on/with it. — Pyderman
– Pyderman, Commented Sep 6, 2015 at 14:59

Woot4Moo · Accepted Answer · 2015-09-06 14:55:08Z

So it seems like you are trying to solve two different problems. First I believe your schema is correct insofar as you are storing a value as a varchar and not a decimal (especially since you aren't doing calculations on it).
Second MySQL use relational algebra so the concept of a "list" of values doesn't make sense. Store each as its own distinct row (tuple) and join them back together at runtime.

However, I think you need to consider the following approach:

Create a new table with the following attributes:

price_history(  
    price varchar(45),  
    date_changed datetime
)

This will enable you to properly handle the API call that comes back from your vendor. I would contest that the API is non-deterministic from the perspective that there are two returns that could occur. The return should (in my opinion) always return as a list / array to ensure that consumers can always handle the response without undo burden (but that is beyond the scope)

Collectives™ on Stack Overflow

How to handle Python list of strings in MySQL column expecting a varchar(255)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related