0

I'm using sqlalchemy to connect to a MySQL database and pandas read_sql_query to store the data in a dataframe. Here is my code:

import mysql.connector
import pandas as pd
import pymysql
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://' + username + ':' + password + '@' + server + ':' + port + '/' + database)

df = pd.read_sql_query('''
  SELECT *
  FROM
    table
  LIMIT 10
''', engine)

One of the columns in the MySQL table that I'm trying to get data from consists utf8 (Persian) characters and when I use the code on my local computer, the result is fine and the utf8 characters can store in the dataframe. But when I'm trying to do the same thing on the remote server with the exact code, the utf8 characters are being replaced with question marks.

How can I get the same results on the server too?

P.S:

  • My computer's OS: Ubuntu 18.04
  • Server's OS: Ubuntu 16.04
Saeed Esmaili
  • 764
  • 3
  • 12
  • 34
  • Do you have the same version of python, pandas and sqlalchemy on the server as well? – Dan Aug 01 '18 at 10:12
  • @Dan Not exactly. local: python 3.6.5, pandas 0.23.0, sqlalchemy 1.1.11 - Server: python 3.5.2, pandas 0.23.1, sqlalchemy 1.2.10 – Saeed Esmaili Aug 01 '18 at 10:28
  • Worth trying to upgrade you local pandas and sqlalchemy to match :/ Also, is it connecting to the same physical database or is the DB mirrored so the server connects to a different physical DB to your local machine? Because then the DB version might make a difference. – Dan Aug 01 '18 at 11:11
  • @dan I upgraded the libraries and the code still works fine on the local computer. Yes, both local and the server are trying to get data from the same mysql server. – Saeed Esmaili Aug 01 '18 at 11:20
  • You could try exporting `PYTHIONIOENCODING="utf8"` on the server. Just guessing though. – lenz Aug 01 '18 at 11:57
  • See "question mark" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Aug 17 '18 at 23:04

0 Answers0