sqlalchemy插入資料到mysql異常

標籤：pac   pack   uri   ges   資料   mail   php   val   ati   

1. 插入中文報錯：

在資料庫名稱後面添加?charset=utf8
engine = create_engine("mysql+pymysql://root:[email protected]/data?charset=utf8", echo=True)

2. 正常插入，但是提示以下警告：

2018-06-06 16:28:39,789 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'2018-06-06 16:28:39,790 INFO sqlalchemy.engine.base.Engine {}D:\PythonProjects\venv\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 480")  result = self._query(query)

代碼如下：

from sqlalchemy import Column,Integer,String,DECIMAL,create_engine,DateTimefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()class Product(Base):    __tablename__ = 'cj_product_test'    id = Column(Integer,primary_key=True,autoincrement=True)    original_id = Column(String(255))    product_name = Column(String(255))    def __repr__(self):        return "<Product(id='%d', original_id='%s', product_name='%s')>" % (self.id, self.original_id, self.product_name)# 插入中文會報錯，需要在資料庫名稱後面添加?charset=utf8engine = create_engine("mysql+pymysql://root:[email protected]/data?charset=utf8", echo=True)Product.metadata.create_all(engine)DBSession = sessionmaker(bind=engine)product = Product(original_id='432143',product_name='施華洛世奇')session = DBSession()session.add(product)session.commit()session.close()

MySQL自生的一個BUG:
https://www.cnblogs.com/pengyusong/p/6008936.html
https://bugs.mysql.com/bug.php?id=82414

sqlalchemy插入資料到mysql異常