如何使用robotframework数据库库将utf8字符插入oracle数据库 [英] how to insert utf8 characters into oracle database using robotframework database library

问题描述

我有一个机器人脚本，该脚本可以从sql文件中插入一些sql语句；其中一些语句包含utf8字符.如果我使用navicat工具将此文件手动插入数据库，一切都很好.但是，当我尝试使用机器人框架的数据库库执行此文件时，utf8字符会发疯！

I have a robot script which inserts some sql statements from a sql file; some of these statements contain utf8 characters. If I insert this file manually into database using navicat tool, everything's fine. But when I try to execute this file using database library of robot framework, utf8 characters go crazy!

这是我的utf8包含的sql语句:

This is my utf8 included sql statement:

INSERT INTO "MY_TABLE" VALUES (2, 'تست1');

这是我使用数据库库的方式:

This is how I use database library:

Connect To Database Using Custom Params     cx_Oracle   ${dbConnection}
Execute Sql Script      ${sqlFile}
Disconnect From Database

这是我从数据库中得到的:

This is what I get in the database:

������������ 1

我尝试直接使用cx_Oracle执行SQL文件，但仍然失败！原始库中似乎有问题.这就是我用来导入SQL文件的内容:

I have tried to execute the SQL file using cx_Oracle directly and it's still failing! It seems there is a problem in the original library. This is what I've used for importing SQL file:

import cx_Oracle
if __name__ == "__main__":
    dsn_tns = cx_Oracle.makedsn(ip, port, sid)
    db = cx_Oracle.connect(username, password, dsn_tns)
    sql_commands = open(sql_file_addr, 'r').read().split(";")
    cr = db.cursor()
    for command in sql_commands:
        if not command in ["", "\t", "\n", "\r", "\n\r", "\r\n", None]:
            print "Executing SQL command:", command
            cr.execute(command)

    db.commit()

我发现我可以在连接字符串中定义字符集.我已经为mysql数据库完成了该任务，并且该框架成功地将UTF8字符插入了数据库；这是我的MySQL连接字符串:

I have found that I can define character-set in the connection string. I've done it for mysql database and it the framework successfully inserted UTF8 characters into database; this is my connection string for MySQL:

database='db_name', user='db_username', password='db_password', host='db_ip', port=3306, charset='utf8'

但是我不知道如何为Oracle连接字符串定义字符集.我已经尝试过了:

But I don't know how to define character-set for Oracle connection string. I have tried this:

'db_username','db_password','db_ip:1521/db_sid','utf8'

我有这个错误:

TypeError: an integer is required

推荐答案

按照@Yu Zhang的建议，我在

As @Yu Zhang suggested, I read discussion in this link and I found out that I should set an environment variable NLS_LANG in order to have a UTF-8 connection to the database. So I've added below line in my test setup:

os.environ["NLS_LANG"] = "AMERICAN_AMERICA.AL32UTF8"

这篇关于如何使用robotframework数据库库将utf8字符插入oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！