SQLAlchemy automatically converts str to unicode when committed

Question

When an object is inserted into the database using SQLAlchemy, all its properties corresponding to the String () columns are automatically converted 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 conclusion:

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

I know that I probably just need to work with unicode, but this will require digging through some third-party code, and I still don't have Python skills for this :)

Answer 1

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

engine.raw_connection (). connection.text_factory = str

Answer 2

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

Unlike SQLAlchemys active, handling date and time types for pysqlite, the default behavior of pysqlites is Unicode - all rows are returned as Python Unicode objects anyway. So even if the Unicode type is not used, you will still always receive Unicode data from the result set. It is highly recommended that you use the Unicode Type to represent strings, as it will raise a warning if a non-unicode Python string is passed from a user application. Mixing the use of non-unicode objects with returned Unicode objects can quickly create confusion, especially when using ORM, since internal data is not always represented by the actual database result string.