Is There A Mysql Equivalent To Python's Set Type?

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 multi

Solution 1:

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

CREATETABLEset (
    id int unsigned PRIMARY KEY AUTO_INCREMENT
);

CREATETABLE set_member (
    set_id int unsigned NOTNULL,
    FOREIGN KEY (set_id) REFERENCESset(id),
    member<whatever_type_your_set_is_of>NOTNULL,
    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.

Solution 2:

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,))