Databases
StackOverflow2013
Postsdbo
POSQLAlchemy unsupported type error - and table design issues?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLAlchemy unsupported type error - and table design issues?
    primarykey
    data
    text
    <p>back again with some more SQLAlchemy shenanigans.</p> <p>Let me step through this.</p> <p>My table is now set up as so: </p> <pre><code>engine = create_engine('sqlite:///:memory:', echo=False) metadata = MetaData() students_table = Table('studs', metadata, Column('sid', Integer, primary_key=True), Column('name', String), Column('preferences', Integer), Column('allocated_rank', Integer), Column('allocated_project', Integer) ) metadata.create_all(engine) mapper(Student, students_table) </code></pre> <p>Fairly simple, and for the most part I've been enjoying the ability to query almost any bit of information I want provided I avoid the error cases below.</p> <p>The class it is mapped from is:</p> <pre><code>class Student(object): def __init__(self, sid, name): self.sid = sid self.name = name self.preferences = collections.defaultdict(set) self.allocated_project = None self.allocated_rank = 0 def __repr__(self): return str(self) def __str__(self): return "%s %s" %(self.sid, self.name) </code></pre> <p><em>Explanation: <code>preferences</code> is basically a set of all the projects the student would prefer to be assigned. When the allocation algorithm kicks in, a student's <code>allocated_project</code> emerges from this preference set.</em></p> <p>Now if I try to do this:</p> <pre><code>for student in students.itervalues(): session.add(student) session.commit() </code></pre> <p>It throws two errors, one for the <code>allocated_project</code> column (seen below) and a similar error for the <code>preferences</code> column:</p> <pre><code>sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 4 - probably unsupported type. u'INSERT INTO studs (sid, name, allocated_rank, allocated_project) VALUES (?, ?, ?, ?, ?, ?, ?)' [1101, 'Muffett,M.', 1, 888 Human-spider relationships (Supervisor id: 123)] </code></pre> <p>If I go back into my code I find that, when I'm copying the <code>preferences</code> from the given text files, it actually refers to the <code>Project</code> class which is mapped to a dictionary, using the unique project id's (<code>pid</code>) as keys. Thus, as I iterate through each student via their <code>rank</code> and to the <code>preferences</code> set, it <code>add</code>s not a project id, but the reference to the project id from the <code>projects</code> dictionary.</p> <pre><code>students[sid].preferences[int(rank)].add(projects[int(pid)]) </code></pre> <p>Now this is very useful to me since I can find out all I want to about a student's preferred projects without having to run another check to pull up information about the project id. The form you see in the error has the object print information passed as: </p> <pre><code>return "%s %s (Supervisor id: %s)" %(self.proj_id, self.proj_name, self.proj_sup) </code></pre> <p>My questions are:</p> <ol> <li><p>I'm trying to store an object in a database field aren't I?</p></li> <li><p>Would the correct way then, be copying the project information (project id, name, etc) into its own table, referenced by the unique project id? That way I can just have the project id field for one of the student tables just be an integer id and when I need more information, just <code>join</code> the tables? So and so forth for other tables?</p></li> <li><p>If the above makes sense, then how does one maintain the relationship with a column of information in one table which is a key index on another table?</p></li> <li><p>Does this boil down into a database design problem?</p></li> <li><p>Are there any other elegant ways of accomplishing this?</p></li> </ol> <p>Apologies if this is a very long-winded question. It's rather crucial for me to solve this, so I've tried to explain as much as I can, whilst attempting to show that I'm <em>trying</em> (key word here sadly) to understand what could be going wrong.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USPizzAzzra
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLAlchemy unsupported type error - and table design issues?
      1. USPizzAzzra
      1. VTFavorite
    2. VO
      singulars
      1. POSQLAlchemy unsupported type error - and table design issues?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload