"typeerror: String Argument Without An Encoding" Flask And Sqlalchemy

I'm slowly trying to build a small Rest API using Flask, Marshmallow, SQLAlchemy, and SQLite3. At some point in the recent future my simple 'Get' call for the users table seemed t

Solution 1:

Long story short, you have text data in some row in the picture column of the users table.

This is possible because unlike many other SQL implementations, SQLite has dynamic typing, compared to static. The type you give to a column defines its affinity, or in other words the recommended type for data stored in that column, but it is a recommendation, not a requirement. In the end you can have text data in an INTEGER column, or such as in your case, data using the TEXT storage class in a BLOB column.

An example:

In [2]: class User(Base):
   ...:     __tablename__ ='user'
   ...:     id =Column(Integer, primary_key=True)
   ...:     picture =Column(LargeBinary)
   ...:     

In [3]: metadata.create_all()
...
2018-03-0909:33:37,785 INFO sqlalchemy.engine.base.Engine 
CREATETABLEuser (
        id INTEGERNOTNULL, 
        picture BLOB, 
        PRIMARY KEY (id)
)
...

In [4]: engine.execute("insert into user (picture) values ('BANG!')")
Out[4]: <sqlalchemy.engine.result.ResultProxy at0x7f7ad8bcc278>

Trying to fetch users does not end well:

In [5]: session.query(User).all()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent calllast)
<ipython-input-5-f1275abaf1fd>in<module>()
----> 1 session.query(User).all()

...

~/Work/SO/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py in process(value)
    900             def process(value):
    901                 if valueisnotNone:
--> 902                     value = bytes(value)903returnvalue904return process

TypeError: string argument without an encoding

You'll have to go through your user data and fix manually. Using the typeof() function you can find the offending rows:

In [9]: session.query(User.id).\
   ...:     filter(func.typeof(User.picture) == 'text').\
   ...:     all()
Out[9]: [(1)]