Warning 1366 Incorrect string value

[Isaac_Hernandez]

My code is the next

from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, INTEGER, VARCHAR

engine = create_engine('mysql://root:Vianney123@localhost:3306/test_sql_alchemy', echo=False) Session = sessionmaker(bind=engine) sesion_db = Session()

Base = declarative_base()

class Car(Base): tablename = "Cars"

Id = Column(INTEGER, primary_key=True)
Name = Column(VARCHAR)
Price = Column(INTEGER)

book = Car(Name='Isaac', Price=5459) sesion_db.add(book) sesion_db.commit()

rs = sesion_db.query(Car).all()

for car in rs: print(car.Name, car.Price)

The SQL of tables is this

CREATE TABLE cars ( id int(10) NOT NULL AUTO_INCREMENT, Name varchar(255) NOT NULL, Price int(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8

When i'm execute the warning is the next

C:\Users\isaac.hernandez\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py:504: Warning: (1366, "Incorrect string value: '\xE9xic' for column 'VARIABLE_VALUE' at row 496") cursor.execute(statement, parameters)

I try change the encode to utf8 in create_engine, actually use SQLAlchemy V. 1.1.14

[Mike Bayer]

for MySQL you need to set charset:

http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#charset-selection

please confirm this works thanks!

[Isaac_Hernandez]

I have tried with

engine = create_engine('mysql://root:Vianney123@localhost:3306/test_sql_alchemy?charset=utf8', echo=False)

and try

engine = create_engine('mysql://root:Vianney123@localhost:3306/test_sql_alchemy?charset=utf8mb4', echo=False)

not working.

[Mike Bayer]

that data is latin-1 encoded, it is not utf-8 encoded:

>>> x = b'\xE9xic' 
>>> print x.decode('latin1')
éxic
>>> print x.decode('utf-8')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/classic/.venv/lib64/python2.7/encodings/utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xe9 in position 0: invalid continuation byte

you should ensure that you want to use utf-8 encoding and not latin1, and if so you need to manually repair the corrupted data in your database.

[Isaac_Hernandez]

My Code is this.....

from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, INTEGER, VARCHAR

e = create_engine("mysql+pymysql://root:Vianney123@localhost:3306/test?charset=utf8mb4")

Session = sessionmaker(bind=e) sesion_db = Session()

Base = declarative_base()

class Car(Base): tablename = "cars"

Id = Column(INTEGER, primary_key=True)
Name = Column(VARCHAR)
Price = Column(INTEGER)

book = Car(Name='Pedro', Price=5872) sesion_db.add(book) sesion_db.commit()

rs = sesion_db.query(Car).all()

for car in rs: print(car.Name, car.Price)

My table in Character Set is utf8, :(

[Mike Bayer]

It is DEFAULT utf8. You can put any kind of data in the table. I can demonstrate if you'd like.

[Isaac_Hernandez]

show me.

This not working e = create_engine("mysql+pymysql://root:Vianney123@localhost:3306/test?charset=utf8")

and this e = create_engine("mysql+pymysql://root:Vianney123@localhost:3306/test")

and... Warning: (1366, "Incorrect string value: '\xE9xic' for column 'VARIABLE_VALUE' at row 496") result = self._query(query)

the data to insert

this Car(Name='Pedro', Price=5872)

[Mike Bayer]

the error is created on INSERT though, not SELECT (you did not illustrate what query you are running), so this is latin1 data in your program getting inserted.

[Mike Bayer]

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()

engine = create_engine("mysql://scott:tiger@localhost/test?charset=utf8")

with engine.connect() as conn:
    conn.execute("DROP TABLE IF EXISTS test_cars")

    conn.execute("""CREATE TABLE test_cars (
        id int(10) NOT NULL AUTO_INCREMENT,
        Name varchar(255) NOT NULL,
        Price int(100) NOT NULL, PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8""")

    # warning is here
    conn.execute(
        "INSERT INTO test_cars (id, name, price) VALUES (%s, %s, %s)",
        [
            (1, "normal name", 30000),
            (2, b'\xE9xic', 25000)
        ]
    )


class Cars(Base):
    __table__ = Table("test_cars", Base.metadata, autoload_with=engine)

s = Session(engine)

print s.query(Cars.Name).all()

output:

#!

$ python test2.py 
/home/classic/.venv/lib/python2.7/site-packages/MySQLdb/cursors.py:318: Warning: (1366L, u"Incorrect string value: '\\xE9xic' for column 'Name' at row 2")
  rows += self.execute(sql + postfix)
[(u'normal name',), (u'?xic',)]

[Isaac_Hernandez]

Thanks, I'll see you in a moment.

[Isaac_Hernandez]

One Last question....

execute your example C:\Users\isaac.hernandez\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py:470: Warning: (1366, "Incorrect string value: '\xE9xic' for column 'VARIABLE_VALUE' at row 496") cursor.execute(statement, parameters) [('normal name',)]

the error this is in sqlalchemy\engine\default.py:470

insert but warning continues ...

I am using python 3.6.2

[Mike Bayer]

that is because in at least some (but not all) of your examples, you are using the mysql-python (or mysqlclient) DBAPI which is written in C. Python only gives you a stack trace down to the Python code. Youll note in your comment at https://bitbucket.org/zzzeek/sqlalchemy/issues/4085/warning-1366-incorrect-string-value#comment-39990110 you used pymysql and you see the code line is listed inside of pymysql.

[Mike Bayer]

no bug illustrated here please reopen if you can reproduce a bug. for additional questions please reach out on the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy thanks!