[sqlalchemy] unable to add unicode characters such as ('軟件開發人員') to mysql database using sqlalchemy

[Kin]

Hello.

i am not being able to write those traditional chinese characters to my
mysql database.
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:***@dbIpAddress/testdb3'*

*engine1 = create_engine(db_url, pool_recycle=2*60*60)*

*Session = sessionmaker(bind=engine1)*

*session = Session()*

*object1 = Writers3( '軟件開癌人員')*

*session.add(object1)*

*session.commit()*

*session.close()*

[Michael Bayer]

Post by Kin
Hello.
i am not being able to write those traditional chinese characters to my mysql database.
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.
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
Base = declarative_base()
__tablename__ = "writers3"
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(BigInteger(20), primary_key=True)
account = Column(VARCHAR(length=25, unicode = True))
self.account = account
Base.metadata.create_all(engine)
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
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:***@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'軟件開癌人員'

Post by Kin
session.add(object1)
session.commit()
session.close()
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Kin]

The python version is: 2.7.3

And while attempting to commit my change, this is the error message I
obtained:

*UnicodeEncodeError: 'latin-1' codec can't encode characters in position
0-5: ordinal not in range(256)*

I actually did obtain that a couple of time while trying to send a unicode
string as argument to the ORM class.

Post by Kin
Hello.
i am not being able to write those traditional chinese characters to my
mysql database.
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.
*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 =
*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)*
*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*
*engine1 = create_engine(db_url, pool_recycle=2*60*60)*
*Session = sessionmaker(bind=engine1)*
*session = Session()*
*object1 = Writers3( '軟件開癌人員')*
*session.add(object1)*
*session.commit()*
*session.close()*

[Michael Bayer]

try the script I gave you, start with that.

Post by Kin
The python version is: 2.7.3
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-5: ordinal not in range(256)
I actually did obtain that a couple of time while trying to send a unicode string as argument to the ORM class.
Hello.
i am not being able to write those traditional chinese characters to my mysql database.
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.
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
Base = declarative_base()
__tablename__ = "writers3"
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(BigInteger(20), primary_key=True)
account = Column(VARCHAR(length=25, unicode = True))
self.account = account
Base.metadata.create_all(engine)
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
engine1 = create_engine(db_url, pool_recycle=2*60*60)
Session = sessionmaker(bind=engine1)
session = Session()
object1 = Writers3( '軟件開癌人員')
session.add(object1)
session.commit()
session.close()
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Kin Rachid KONE KITO]

I did.

It is your script that gave me the error.
i had to correct *object1 = Writers3(u'³n¥ó¶}µo€H­û¡Š)*
(as it gave me a syntax error due to the closing quote), then set the right
Ips.
The error I obtained come from that modified script.

[Kin]

Thousands Apologies Michael.
I tried to add your code to mine instead of doing the reverse, and it
miserably failed.

i did the reverse and it worked.
Thanks a lot for your help!