Is there a MySQL equivalent to Python's set type?

Question

The title says it all: I am looking for a standard way to preserve Python [frozen]sets in a MySQL database.

Note: the SET MySql datatype is not an equivalent; it is more of a multiple-choice from a restricted list object.

Note (2): I can invent something ad-hoc; assuming my set is of strings which contain no commas, I can record it as a sorted comma-separated list of strings. I am looking for something which is a bit more general and more standard.

Note (3): I have looked at MySQL reference webpages. I could not find it there. I am still asking, as I am not so proficient with databases, and I think that it is possible that a standard method exists.

A table. If you want to create a relational data model, then make it a separate table. Of course, if you just want to dump a Python object (which happens to be a set) into a database and later recreate it, without querying it, different terms and condition apply. — , Feb 12 '14 at 14:48
I just wish to dump a simple Python object, such as a set of strings. — Bach, Feb 12 '14 at 14:55

score 1 · Answer 1 · answered Feb 12 '14 at 14:49

1

This is easy: "I am looking for a standard way to preserve Python [frozen]sets in a MySQL database."

This is not: "*SQL equivalent to Python's set type?"

The set type does more than contain values - it allows set operations with them - the closest approach would be the ENUM type - https://dev.mysql.com/doc/refman/5.0/en/enum.html - but you'd have to create an enum for each set you'd want.

However, if all you want is to preserve the values of a frozenset to retrieve it later from Python, you can easily serialize it with Pickle, and store it as a BLOB in the SQL database:

import pickle

...
data = pickle.dumps(my_frozen_set)
cursor.execute(""" INSERT INTO mytable VALUES (%s,)""", (data,))

answered Feb 12 '14 at 14:49

jsbueno

99,910
10
151
209

Pickle sounds expansive for that. If I know that this is a list of (limited) strings, at least, I can do much better with a custom serialization. – Bach Feb 12 '14 at 14:57
pickle will probably cost you much less than "custom serialization" (if you are in Python 2 remember to use the cPickle module, instead of pickle). Anyway, just doing `",".join(your_set) ` to serializare, followed by set(valye.split(",")) would make for a serialization. (until you have a "," ocurring in your set strings, that is). – jsbueno Feb 13 '14 at 13:02

score 1 · Accepted Answer · answered Feb 12 '14 at 14:51

The proper way to implement a set in SQL would be with two extra tables:

CREATE TABLE set (
    id int unsigned PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE set_member (
    set_id int unsigned NOT NULL,
    FOREIGN KEY (set_id) REFERENCES set(id),
    member <whatever_type_your_set_is_of> NOT NULL,
    UNIQUE (set_id, member)
);

(You might be able to avoid the set table, if you want to store sets in only one table, and that table has a unique ID, and you will only store one set per record in the table)

Since it is very likely that you're not looking for a proper SQL approach (which is actally fine, if you're never going to perform any in-database operation on those sets, besides storage and retrieval), you will likely store your set denormalized in a single field on a table instead of following recommended practices; in that case there is no native MySQL data type that can represent a Python set and you will need to serialize your set in a varchar or a text column. It is completely up to you to pick a serialization format that fits your needs.

I am not 100% satisfied (from MySQL, not from your answer), as it seems quite reasonable to have set datatypes which do allow some basic in-database operations (such as `SELECT * FROM table WHERE 'elm' in set_field`); however, I guess I'll have to accept that situation. — Bach, Feb 12 '14 at 15:44

Is there a MySQL equivalent to Python's set type?

2 Answers2

Linked