SQLAlchemy automatically converts str to unicode on commit

Question

Hello,

When inserting an object into a database with SQLAlchemy, all it's properties that correspond to String() columns are automatically transformed from <type 'str'> to <type 'unicode'>. Is there a way to prevent this behavior?

Here is the code:

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.orm import mapper, sessionmaker

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

table = Table('projects', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50))
)

class Project(object):
    def __init__(self, name):
        self.name = name

mapper(Project, table)
metadata.create_all(engine)

session = sessionmaker(bind=engine)()

project = Project("Lorem ipsum")

print(type(project.name))

session.add(project)
session.commit()

print(type(project.name))

And here is the output:

<type 'str'>
<type 'unicode'>

I know I should probably just work with unicode, but this would involve digging through some third-party code and I don't have the Python skills for that yet :)

Answer 1

Unfortunately, you are out of luck and this does not seem to work with sqlite. A quote from SQLAlchemy 0.6.2 Documentation - SQLite - Unicode:

In contrast to SQLAlchemy’s active handling of date and time types for pysqlite, pysqlite’s default behavior regarding Unicode is that all strings are returned as Python unicode objects in all cases. So even if the Unicode type is not used, you will still always receive unicode data back from a result set. It is strongly recommended that you do use the Unicode type to represent strings, since it will raise a warning if a non-unicode Python string is passed from the user application. Mixing the usage of non-unicode objects with returned unicode objects can quickly create confusion, particularly when using the ORM as internal data is not always represented by an actual database result string.

Answer 2

Actually, there is a way to do that. Just execute this line of code after creating engine:

engine.raw_connection().connection.text_factory = str