[PYTHON] Addicted to character code by inserting and extracting data with SQLAlchemy

Addictive to SQLAlchemy and character encoding (UTF-8, Unicode)

problem

If you put the data in MySQL by yourself, it will not be garbled if you take it out with sqlalchemy, but if you put it in the dump file that you got from a friend, it will be garbled if you take it out with sqlalchemy / (^ o ^) \

solution

When using sqlalchemy to move data in and out, ** Insert and extract (select) always use the same character code (setting). ** **


Commentary

This time, the following basics? I was trying to make a good connection.

# -*- coding: utf-8 -*-

from sqlalchemy import create_engine,
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import MetaData


#Processing related to connection with db
engine = create_engine('mysql://username:password@localhost/dbname?charset=utf8')
Base = declarative_base()
metadata = MetaData()
Session = sessionmaker(bind=engine)
session = Session()

There is no fatal problem if you put it in and out by yourself. However, be careful when handling data received from other people with sqlalchemy.

This time, I got a dump file of mysql and inserted the data, but when I took it out via sqlalchemy, the characters were garbled / (^ o ^) \

Status

myself:

create_engine('mysql://username:password@localhost/dbname')

charset is not set to utf-8 / (^ o ^) \

Opponent: The charset was properly set to utf-8. (I didn't use sqlalchemy because I was inserting data into MySQL with rails.)

How did you fix it?

I rewrote it as follows.

create_engine('mysql://username:password@localhost/dbname?charset=utf8')

I just added? Charset = utf8 at the end. However, the following flow is not good.

Insert data (without? Charset = utf8) ↓ Extract data (with? Charset = utf8 added) The characters are garbled.

Let's insert and retrieve data with the same settings. In other words

Insert data (with? Charset = utf8 added) ↓ Extract data (with? Charset = utf8 added)

This way, garbled characters will not occur.

And vice versa. If you inserted the data without? charset = utf8, retrieve it without? charset = utf8.

If you retrieve the data with? Charset = utf8 attached, the character data will be returned in Unicode. If you did not add? charset = utf8, it was returned ascii.

I want to make Python3 as soon as possible \ (^ o ^) /

Recommended Posts

Addicted to character code by inserting and extracting data with SQLAlchemy
[Python / Ruby] Understanding with code How to get data from online and write it to CSV
What to do if you are addicted to Windows character code
Mass generation of QR code with character display by Python
Get additional data to LDAP with python (Writer and Reader)
Introduction to RDB with sqlalchemy Ⅰ
How to update with SQLAlchemy?
How to Alter with SQLAlchemy?
group_by with sqlalchemy and sum
How to Delete with SQLAlchemy?
"Introduction to data analysis by Bayesian statistical modeling starting with R and Stan" implemented in Python
Generate error correction code to restore data corruption with zfec library
Operate Jupyter with REST API to extract and save Python code
A note I was addicted to when creating a table with SQLAlchemy
Move data to LDAP with python Change / Delete (Writer and Reader)
How to build Python and Jupyter execution environment with VS Code
Connect to multiple databases with SQLAlchemy
How to deal with imbalanced data
How to deal with imbalanced data
Using Sessions and Reflections with SQLAlchemy
How to Data Augmentation with PyTorch
Linux commands related to character code
How to INNER JOIN with SQLAlchemy
Save json data received by mosquitto on docker to db and Elasticsearch
Character code conversion. There is no nfk. Alternative with iconv and tr
I was addicted to creating a Python venv environment with VS Code
Sort anime faces by scraping anime character pages with Beautiful Soup and Selenium
Python code to train and test with Custom Vision of Cognitive Service
Feel free to knock 100 data sciences with Google Colab and Azure Notebooks!