david wrote:
> Hi,
>
> I can see that this has been discussed before, but I am totally
> baffled as to what is happening.
>
> I.
> ubuntu
> mysql version 5.0.38
>
> with :
> MySQL_python-1.2.2
> SQLAlchemy-03.10
> python-2.5
>
> Using SA, I can insert and select unicode data with no problem. All
> the mysql stuff looks like it is set to "latin1", the database wasn't
> created with any special options etc., I added nothing special to the
> connection string, but if I insert Russian characters (encoded in
> utf-8) into a column with type Unicode, It *works* and if I select, I
> get back the correct data. It even looks fine with the "mysql"
> program.
>
> II.
> Now, I try the same thing on a red-hat system
> with:
> mysql version 4.1.9
> MySQL_python-1.2.2
> SQLAlchemy-03.10
> python-2.4.2
>
> I realize I am changing two things (besides the os): mysql version,
> and python version.
>
> BUT, I can't get *anything* with sa and unicode and mysql to work!
>
> I ended up creating the database specifically with utf-8 charset to
> get even things with MySQLdb to work (which I finally did). But for
> anything to work, I had to do this beforehand:
>
> cursor.execute("set collation_connection=utf8_general_ci")
> cursor.execute("set collation_server=utf8_general_ci")
> cursor.execute("set character_set_results=utf8")
>
> With SA, when I try to do, say, an insert with something like:
>
> ins = utest_t.insert({'lastname':
> "hello"})
> conn.execute(ins)
>
> no matter what I do I get errors like:
> sqlalchemy.exceptions.DBAPIError: (LookupError) unknown encoding:
> latin1_swedish_ci
>
> Does anyone know what I am doing wrong here? Or, how I can make things
> right?
>
> BTW, I have tried various versions of the connection string:
>
> conn_string = "mysql://xx:[EMAIL PROTECTED]/utest?
> use_unicode=1&charset=utf8"
> conn_string = "mysql://xx:[EMAIL PROTECTED]/utest?
> use_unicode=0&charset=utf8"
> conn_string = "mysql://xx:[EMAIL PROTECTED]/utest"
>
> but with the same results.
>
> All I can say is, unicode on mysql is, well...... I won't say it.
can't reproduce this on 0.3.10 (or 0.3.11 or 0.4). the attached test
passes fine for me with zero special setup on nearly identical setup.
(i'm testing with python 2.4.4.)
all that's needed for unicode in mysql is to ensure that the connection
and table encodings aren't configured with a latin1 legacy default. the
mysql unicode data support is actually the best of all the open source
databases, and it's tied with postgres for nearly functional unicode
schemas. (sqlite wins that one.)
to guess wildly, this:
> sqlalchemy.exceptions.DBAPIError: (LookupError) unknown encoding:
> latin1_swedish_ci
could be due to specifying a collation where a charset should be in the
database configuration somewhere.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
from pkg_resources import require; require('MySQL-Python==1.2.2')
import sys
from sqlalchemy import *
assert sys.version_info[0:2] == (2,4)
base_url = 'mysql:///test'
#connect_args = {}
connect_args = {'unix_socket':'/var/tmp/mysql.sock'} # for jek's workstation
c = create_engine(base_url, connect_args=connect_args, pool_size=1).connect()
assert c.execute('SELECT VERSION()').scalar().startswith('4.1.9')
c.close()
unicode_data = u'\u0431\u043e\u0440\u0449'
str_data = unicode_data.encode('utf8')
for query in ("?use_unicode=1&charset=utf8",
"?use_unicode=0&charset=utf8",
"?"):
url = base_url + query
engine = create_engine(url, pool_size=1, connect_args=connect_args)
for table_options in ({}, {'mysql_charset': 'utf8'}):
print url, 'table options', table_options
meta = MetaData(engine)
t = Table('ut', meta,
Column('plain1', String(100)),
Column('uni1', Unicode(100)),
Column('plain2', String(100)),
Column('uni2', Unicode(100)),
**table_options)
meta.create_all()
try:
try:
t.insert().execute(plain1=unicode_data,
uni1=unicode_data,
plain2=str_data,
uni2=str_data)
print repr(list(t.select().execute()))
except Exception, ex:
if 'charset' not in url:
print ex
else:
raise
finally:
meta.drop_all()
print "OK"
