python SQLAlchemy & pandas read_sql_query Japanese Processing

Hello, nice to meet you.

with mysql like function in sqlaichemy and pandas.read_sql_query I'd like to select a keyword in Japanese, but how can I move it?

If you select the keyword in English as below, you can move.

 statement="SELECT* FROM orderitem WHERE item_description like '%example%'"
df = pd.read_sql_query (text(statement), engine)

I get an error when I use Japanese keywords.

 statement="SELECT* FROM order item WHERE item_description like '% Japanese %'"
df = pd.read_sql_query (text(statement), engine)

Error:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xe6 in position 55: original not in range (128)

ps - This query "SELECT* FROM order item WHERE item_description like '% Japanese %'" can be moved in terminal, but I get an error when I work with python.

My code:

import pymysql
importos
import pandas aspd
from sqlalchemy import create_engine, text

db=pymysql.connect(db=db, user='root', passwd=passwd, charset='utf8', unix_socket="/var/run/mysqld/mysqld.sock", local_infile=True)
cursor=db.cursor()
cursor.execute("SET NAMES utf8")
engine=create_engine('mysql+pymysql://root:passwd@localhost:4406/db?charset=utf8')

statement="SELECT* FROM order item WHERE item_description like '% Japanese %'"
df = pd.read_sql_query (text(statement), engine)

I looked it up a lot. Even if I add encoding=utf-8 between the engineer and connection, it doesn't work...

I would appreciate it if you could let me know.
Thank you.

python pandas japanese sqlalchemy

2022-09-29 22:48

2 Answers

In the end, I will refer to the comments on tellk.

#Put "u" in front of the double quotation
statement=u "SELECT* FROM order item WHERE item_description like '% Japanese %'"

Resolved in !

My python is 2.7 and the code is as follows.

import pymysql
importos
import pandas aspd
from sqlalchemy import create_engine, text

db=pymysql.connect(db=db, user='root', passwd=passwd, charset='utf8', 
unix_socket="/var/run/mysqld/mysqld.sock", local_infile=True)
cursor=db.cursor()
cursor.execute("SET NAMES utf8")
engine=create_engine('mysql+pymysql://root:passwd@localhost:4406/db?charset=utf8')

statement="SELECT* FROM order item WHERE item_description like '% Japanese %'"
df = pd.read_sql_query (text(statement), engine)

2022-09-29 22:48

By any chance, is the Python version you are using 2 series? If so, try writing SQL statements in Unicode strings.

#Put "u" in front of the double quotation
statement=u "SELECT* FROM order item WHERE item_description like '% Japanese %'"

Also, read_sql_query can be a string.(I don't know which is better, text.)

 statement=u "SELECT* FROM order item WHERE item_description like '% Japanese %'"
# pass a string directly without using a text function
df = pd.read_sql_query (statement, engine)

http://pandas.pydata.org/pandas-docs/version/0.18.1/generated/pandas.read_sql_query.html

2022-09-29 22:48

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656