[Sqlalchemy-tickets] Issue #3291: Problem using binary type with foreign key relationship between models (zzzeek/sqlalchemy)

[David C. <iss...@bi...>]

New issue 3291: Problem using binary type with foreign key relationship between models
https://bitbucket.org/zzzeek/sqlalchemy/issue/3291/problem-using-binary-type-with-foreign-key

David Czarnecki:

Running into a problem trying to use a binary type with a foreign key relationship between models. This stems from trying to use the UUIDType from SQLAlchemy-Utils and choosing the binary form, which uses a BINARY(16) type for the UUID column. This is using MariaDB. Relevant software and library versions are included at the end.

Here are the create table scripts:


```
#!mysql

MariaDB [flask_accounts_development]> show create table account\G;
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `id` binary(16) NOT NULL,
  `username` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified
```

```
#!mysql
MariaDB [flask_accounts_development]> show create table address\G;
*************************** 1. row ***************************
       Table: address
Create Table: CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` binary(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `address_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified
```

Account and Address model definitions:

```
#!python
from app import db
from sqlalchemy_utils import UUIDType

class Account(db.Model):
    id = db.Column(UUIDType, primary_key=True)
    username = db.Column(db.String(64), unique=True, nullable=False)
    addresses = db.relationship('Address', backref=db.backref('account'), cascade='all, delete-orphan')

    def __init__(self, username):
        self.username = username

    def __repr__(self):
        return '<Account id=%r name=%r>' % (self.id, self.username)

class Address(db.Model):
    id          = db.Column(db.Integer, primary_key=True)
    account_id  = db.Column(UUIDType, db.ForeignKey('account.id'), nullable=False)

    def __repr__(self):
        return '<Address id=%r account_id=%r>' % (self.id, self.account_id.hex)
```

Code to exercise the issue:

```
#!python
from app.accounts.models import Address
from app import db
from app.accounts.models import Account
import uuid

account = Account(username='david')
account.id = uuid.uuid4()

db.session.add(account)
db.session.commit()

address = Address(account=account)

db.session.add(address)
db.session.commit()

account.addresses
```

Running this from a Flask shell, I get the following error trying to get the addresses for an account:

```
#!python
In [12]: account.addresses
/Users/dczarnecki/.virtualenvs/ba-flask-migrate-test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:436: Warning: Invalid utf8 character string: 'DEF4C0'
  cursor.execute(statement, parameters)
Out[12]: []
```

The relationship is empty this way. I can however navigation the relationship from address to account.

```
#!python
In [11]: address.account
Out[11]: <Account id=UUID('5b608608-d29b-4f8e-bfa4-a80640ffaff3') name=u'david'>
```

Any thoughts on setting up the relationship correctly or where the issue might be going from account to addresses? 

Software:

MariaDB: mysql --version
mysql  Ver 15.1 Distrib 10.0.15-MariaDB, for osx10.10 (x86_64) using readline 5.1

Flask-SQLAlchemy: 2.0

SQLAlchemy-Utils: 0.26.6

MySQL-python: 1.2.5