search

sqlalchemy-bot / test_alembic_1

0 stars 0 forks source link

bulk_insert failes when insert unicode #489

Closed sqlalchemy-bot closed 6 years ago

sqlalchemy-bot commented 6 years ago

Migrated issue, originally created by Handsome2734 (Handsome2734)

#!python2.7
# initial_version.py
def upgrade():
    actiontype = op.create_table(
        'actiontype',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('pkey', sa.String(16)),
        sa.Column('name', sa.String(16)),
        sa.Column('description', sa.Text()),
        sa.Column('suite_only', sa.Boolean(), default=False)
    )
  op.bulk_insert(actiontype, [
        {
            'pkey': 'launch',
            'name': u'启动',
            'description': u'启动被测试App'
        }, {
            'pkey': 'record',
            'name': u'录屏',
            'description': u'开始录制屏幕'
        }
    ])

This migration throws error UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-1: ordinal not in range(256) when executing alembic upgrade head. However the MySQL in alembic.ini is utf-8 encoding.

sqlalchemy-bot commented 6 years ago

Michael Bayer (zzzeek) wrote:

the error I get with both mysqlclient and pymysql is:

#!python

(1366, u"Incorrect string value: '\\xE5\\x90\\xAF\\xE5\\x8A\\xA8' for column 'name' at row 1") [SQL: u'INSERT INTO actiontype (pkey, name, description, suite_only) VALUES (%(pkey)s, %(name)s, %(description)s, %(suite_only)s)'] [parameters: ({'pkey': 'launch', 'suite_only': 0, 'description': u'\u542f\u52a8\u88ab\u6d4b\u8bd5App', 'name': u'\u542f\u52a8'}, {'pkey': 'record', 'suite_only': 0, 'description': u'\u5f00\u59cb\u5f55\u5236\u5c4f\u5e55', 'name': u'\u5f55\u5c4f'})]

however if I create the table with the correct character set, it works fine:

#!python

    actiontype = op.create_table(
        'actiontype',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('pkey', sa.String(16)),
        sa.Column('name', sa.String(16)),
        sa.Column('description', sa.Text()),
        sa.Column('suite_only', sa.Boolean(), default=False),
        mysql_charset="utf8"
    )

output:

#!sql

CREATE TABLE actiontype (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    pkey VARCHAR(16), 
    name VARCHAR(16), 
    description TEXT, 
    suite_only BOOL, 
    PRIMARY KEY (id), 
    CHECK (suite_only IN (0, 1))
)CHARSET=utf8

INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO actiontype (pkey, name, description, suite_only) VALUES (%(pkey)s, %(name)s, %(description)s, %(suite_only)s)
INFO  [sqlalchemy.engine.base.Engine] ({'pkey': 'launch', 'suite_only': 0, 'name': u'\u542f\u52a8', 'description': u'\u542f\u52a8\u88ab\u6d4b\u8bd5App'}, {'pkey': 'record', 'suite_only': 0, 'name': u'\u5f55\u5c4f', 'description': u'\u5f00\u59cb\u5f55\u5236\u5c4f\u5e55'})
INFO  [sqlalchemy.engine.base.Engine] COMMIT
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('ff52eaa4094a')
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT

try that, and also please specify:

  1. exact version of SQLAlchemy in use
  2. exact database driver name and version (e.g. pymysql etc)
  3. confirm you have the correct "coding" comment at the top of your script:
#!python

#!coding: utf-8
  1. exact version of MySQL / MariaDB
  2. OS / platform
  3. complete stack trace for your error

thanks!

sqlalchemy-bot commented 6 years ago

Handsome2734 (Handsome2734) wrote:

I tried adding ?charset=utf8 after sqlalchemy.url in alembic.ini, and it seemed to solve the problem.

sqlalchemy-bot commented 6 years ago

Michael Bayer (zzzeek) wrote:

I tried adding ?charset=utf8 after sqlalchemy.url in alembic.ini, and it seemed to solve the problem.

oh, yes you need that too, I thought that was what you meant when you said "However the MySQL in alembic.ini is utf-8 encoding.". Yeah that's all it is you need to have that set up see http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#charset-selection