On Feb 21, 2014, at 2:42 PM, Kin <[email protected]> wrote: > Hello. > > i am not being able to write those traditional chinese characters to my mysql > database. > > From my understanding of the link below: > http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html > > I should have been able to write unicode characters to my mysql database, and > be able to read them as written. > > Unfotunately, while writing them as: '軟件開發人員' > I obtained their utf8 encoded version which is: '軟件開發人員' > > i did attempt writing them manually to sqlalchemy and the whole process work. > But I do not understand why this not working with sqlalchemy while I > specifically requested it to be written to the database as utf8 using > "charset=utf8" , then read from it as unicode using "use_unicode=1'" > > the mysql version is: 5.5.35-0ubuntu0.12.04.2-log > the sqlalchemy version is:0.8.3 > > Please let me know what you think. > > Here is the code i used to fill the database, table and columns: > > from sqlalchemy import create_engine > from sqlalchemy import Column, String, BigInteger > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy import types > > from sqlalchemy.dialects.mysql import VARCHAR > > engine = > create_engine('mysql+mysqldb://root:foo@<dbIpaddress>/testdb3?charset=utf8&use_unicode=1') > > Base = declarative_base() > > class Writers3(Base): > __tablename__ = "writers3" > __table_args__ = {'mysql_engine': 'InnoDB'} > id = Column(BigInteger(20), primary_key=True) > account = Column(VARCHAR(length=25, unicode = True)) > > def __init__(self, account): > self.account = account > > Base.metadata.create_all(engine) > > > And here is the code I used to fill the table with values: > > from clean_table_def import Writers3 > > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy import create_engine > > from sqlalchemy.orm import sessionmaker, scoped_session > > db_url = 'mysql://root:foo@dbIpAddress/testdb3' > > engine1 = create_engine(db_url, pool_recycle=2*60*60) > > > Session = sessionmaker(bind=engine1) > > session = Session() > > object1 = Writers3( '軟件開發人員') > >
two issues. One is that the utf8 encoding has to be present *for write
operations also*, so you need to set “charset” on the second database URL as
well.
The other is that assuming this is not Python 3, you must present non-ascii
strings as Python unicode objects, that is with a u’’ literal:
u'軟件開發人員'
MySQLdb’s “use_unicode” flag isn’t needed with modern versions of SQLalchemy.
Here is a full round trip with additional notes:
#!coding:utf-8
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
# note that sqlalchemy.BigInteger does *not* accept a length; use
# the MySQL-specific BIGINT if you need that
from sqlalchemy.dialects.mysql import BIGINT
# for VARCHAR, you can use plain sqlalchemy.String
from sqlalchemy import String
# you need charset=utf8 here, though SQLAlchemy > 7 or so
# doesn't really need use_unicode=1 (you can have it and it is fine, though
# SQLAlchemy's unicode conversion might be a little faster)
engine =
create_engine('mysql+mysqldb://scott:tiger@localhost/test?charset=utf8',
echo=True)
Base = declarative_base()
class Writers3(Base):
__tablename__ = "writers3"
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(BIGINT(20), primary_key=True)
account = Column(String(25))
def __init__(self, account):
self.account = account
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
s = Session(engine)
object1 = Writers3(u'軟件開發人員’) # use correct unicode literals
s.add(object1)
s.commit()
assert s.query(Writers3.account).scalar() == u'軟件開發人員'
> session.add(object1)
>
> session.commit()
>
> session.close()
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
