Warning 1366 Incorrect string value
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
Comments (13)
-
repo owner -
reporter 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.
-
repo owner 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.
-
reporter 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, :(
-
repo owner It is DEFAULT utf8. You can put any kind of data in the table. I can demonstrate if you'd like.
-
reporter 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)
-
repo owner 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.
-
repo owner 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',)]
-
reporter Thanks, I'll see you in a moment.
-
reporter 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
-
reporter - changed component to engine
-
repo owner 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.
-
repo owner - changed status to closed
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!
- Log in to comment
for MySQL you need to set charset:
http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#charset-selection
please confirm this works thanks!