0

I am using this function to import my local csv file to MySQL:

def write_to_db(df, database_name, table_name, primary_key=True):
    """
    Creates a sqlalchemy engine and write the dataframe to database
    Source: https://stackoverflow.com/questions/55750229/how-to-save-a-data-frame-as-a-table-in-sql
    """
    # replacing infinity by nan
    df = df.replace([np.inf, -np.inf], np.nan)

    # create sqlalchemy engine
    engine = create_engine("mysql+pymysql://{user}:{pw}@91.xxxx/{db}"
                           .format(user="xx",
                                   pw="xxx",
                                   db=database_name))

    # Write to DB
    df.to_sql(table_name, engine, if_exists='replace', index=False, chunksize=100)
    with engine.connect() as con:
        con.execute('ALTER table ' + table_name + ' add id int primary key auto_increment;')

Than, I just import csv file and use above function to import csv to MySQL:

subjekti = pd.read_csv('mypath/sub_21072020.csv', sep=';', nrows=10)  # I tried with encoding='utf-8' too
write_to_db(subjekti, 'db_name', 'table_name', primary_key=True)

Funtcntion works, but it mess up encodings. Some Croatian characters are converted to ?. That is, č and ć are imported as ? while other charcters are fine.

Mislav
  • 1,533
  • 16
  • 37
  • What collation is your table (and columns) using? You can check the collation by running `SHOW TABLE STATUS WHERE Name LIKE "your-table-name-goes-here";` If you want to use any kind of utf-8 characters, they should be using a utf8 collation. The MySQL documentation on character sets and collation is available here: https://dev.mysql.com/doc/refman/5.7/en/charset.html – Frost Jul 21 '20 at 17:26
  • @Frost do ytou know how should I change `write_to_db` function to set utf-8 coallition? – Mislav Jul 21 '20 at 20:12
  • My table is constructed automatically by calling pandas to_sql functoin. – Mislav Jul 21 '20 at 20:12

0 Answers0