write unicode data to mssql with python?

Question

I'm trying to write a table from a .csv file with Hebrew text in it to an sql server database.
the table is valid and pandas reads the data correct (even displays the hebrew properly in pycharm),
but when i try to write it to a table in the database i get question marks ("???") where the Hebrew should be.

this is what i've tried, using pandas and sqlalchemy:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mssql+pymssql://server/test?charset=utf8')
connection = engine.connect()

df = pd.read_csv("temp.csv", low_memory=False, encoding="UTF-8")
table_name = "test"
df.to_sql(table_name, connection, index=False, if_exists="append")

this loads the table properly but fails to write the Hebrew,
any suggestions?

its read into a string in python and written as text into sql server. — Dror Bogin
– Dror Bogin, Commented Nov 25, 2018 at 14:00
Yes, but what is the datatype of your column? "String" is not a SQL Server data type. — Thom A
– Thom A ♦, Commented Nov 25, 2018 at 14:02
text has been deprecated since 2005, and doesn't support non-ansi characters. The unicode equivalent would be ntext, however, that too is deprecated. Change your column's datatype to nvarchar(MAX) in you need to store 4000+ character values with non-ansi characters. — Thom A
– Thom A ♦, Commented Nov 25, 2018 at 14:05

Thom A · Accepted Answer · 2018-11-25 14:14:50Z

4

You need to change the datatype of your column. text is deprecated, and varchar(MAX) should be used instead, however, neither can store unicode characters. To store unicode characters you would need to use ntext, which is also deprecated; you need to use nvarchar(MAX).

To change your column's definition, you can use this pseudo-SQL (You'll need to replace the parts in braces ({}) with the appropriate object names):

ALTER TABLE {YourTable} ALTER COLUMN {YourColumn} nvarchar(MAX);

Edit: note, this will not restore any data lost in your column. Once a non-ANSI character is inserted into a varchar (or similar) datatype the data is immediately lost and cannot be recovered apart from by changing the datatype and reentry.

answered Nov 25, 2018 at 14:14

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dror Bogin Over a year ago

Sorry about the long accept time, took me some time till I could verify.

M. Mariscal Over a year ago

For SQL Server how it should be? i'm facing the same problem

Thom A Over a year ago

This answer is for SQL Server @M.Mariscal .

M. Mariscal Over a year ago

can you help me with this question please? stackoverflow.com/questions/61813596/…

Thom A Over a year ago

My skills on Python(3) are beginner at best, @M.Mariscal .

|

Collectives™ on Stack Overflow

write unicode data to mssql with python?

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related