用python将unicode数据写入mssql? [英] write unicode data to mssql with python?

问题描述

我正在尝试将其中包含希伯来语文本的.csv文件中的表写入sql服务器数据库.
该表有效并且熊猫正确读取了数据(甚至在pycharm中正确显示了希伯来语)，
但是当我尝试将其写入数据库中的表时，会出现问号("???" )，希伯来语应该在哪里.

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.

这是我尝试使用pandas和sqlalchemy进行的尝试:

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?

推荐答案

您需要更改列的数据类型.不建议使用 text ，而应使用 varchar(MAX)，但是，两者都不能存储unicode字符.要存储unicode字符，您将需要使用 ntext ，它也已弃用；您需要使用 nvarchar(MAX).

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).

要更改列的定义，可以使用此伪SQL(您需要将括号( {} )中的部分替换为适当的对象名):

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);

请注意，这将不恢复您列中丢失的所有数据.将非ANSI字符插入 varchar (或类似)数据类型后，除了更改数据类型和重新输入外，数据将立即丢失并且无法恢复.

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.

这篇关于用python将unicode数据写入mssql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！