Skip to content Skip to sidebar Skip to footer
Home / Database / Odbc / Oracle / Pyqt / Python

How To Connect To Database Using Qoci Or Qodbc With Correct Encoding?

Post a Comment
I want to connect to Oracle database using PyQt5 QOCI driver. Here is some example code I'm using: from PyQt5 import QtSql # create connection STAP = QtSql.QSqlDatabase.addDatabas

Solution 1:

I have created a solution using

  • Oracle Database 12c with Database Characterset AL32UTF8 (mandatory to store Unicode !!)
  • SCOTT schema
  • Oracle Instant client 12.2 with ODBC package (can be downloaded for free from Oracle)
  • Oracle SQL Developer (Tool able to input Unicode characters and connect by Java/JDBC)
  • Python 3.8

Python code below is in my understanding self explaining - only IP, PORT and SERVICE in connect string need to be changed. To watch the Unicode characters on shell/cmd, you need to set the environment variable

PYTHONIOENCODING=UTF-8

Unfortunatly this does not work on Eclipse IDE with PyDEV so I used try-except to get running code. Gave me a few hours headdache...

#  # Safe python file as UTF-8 - otherwise you get no UTF-8 output !!!!## Unix: #   export PYTHONIOENCODING=UTF-8# # Windows:#   set PYTHONIOENCODING=UTF-8## Eclipse/PyDev: #   create for run/debug environment variable  #   PYTHONIOENCODING=UTF-8## ODBC: #   Oracle Instantclient 12.2 + ODBC package## DB:#   Oracle RDBMS 12.2 with Database Characterset AL32UTF8 to allow Unicode## SQL Tool to Execute SQL (JDBC)#   Oracle SQL Developer## SQL#   connect scott/tiger#   create table polish(col1 varchar2(50));#   insert into polish(col1) values('SQLD ł ń');#   commit;## import pyodbc 

bl = " "
UTF8 = "UTF-8"     
strict = "Strict"
s1 = "Test "+UTF8
print(s1)
s1 = chr(322) + bl + chr(324) 
m = bytes(s1,UTF8)   
print(m)
try:       
    print(m.decode(UTF8,strict))
except:
    passprint()  

print("Test ODBC and " + UTF8)        
print("Test ODBC and " + UTF8)  
cs = "DRIVER={DRIVERNAME};UID={USERID};PWD={PASSWD};DBQ={IP_OR_HOSTNAME}:{PORT}/{SERVICE_OR_SID};"
csfill = cs.format(DRIVERNAME="Oracle in instantclient_12_2", 
                   IP_OR_HOSTNAME="111.222.33.44", 
                   PORT=12102, 
                   SERVICE_OR_SID="DB1212UTF",
                   USERID="SCOTT",
                   PASSWD="tiger")     
print(csfill)      
cn = pyodbc.connect(csfill)

cursor = cn.cursor()
# Do the insert - can be done using normal parameters and Unicode strings...
cursor.execute("insert into Polish(COL1) values ( ? )", u"Python ł ń")

# perform commit if want to inspect in SQL Developer# cursor.commit()

cursor = cn.cursor()
# We need to cast COL1 so that unicode is shipped as ' \xxxx'# unfortunatly Unicode deos not work directly # so we use ASCIISTR() to do that...
cursor.execute('SELECT ASCIISTR(COL1)"COL1" from Polish') 
rows = cursor.fetchall()


for row in rows: 
    s =""
    x = row.COL1   
    y = 0
    j = len(x)-1# Parse incoming column for Oracle-Style Unicode like ' \0142'while y <= j:
        if y + 5 <= j: 
            # detect if oracle unicode begins with blank and slash ->  ' \'
            sc = x[y]+x[y+1]
            if sc == " \\":
                # create unicode character
                c = x[y+2]+x[y+3]+x[y+4]+x[y+5]
                s += bl + chr(int(c,16))
                # step forward to next character
                y += 5else:
                # no unicode 4 characters before end !! 
                s += chr(ord(x[y]))        
        else:
            # no unicode - regular ASCII
            s += chr(ord(x[y]))          
        y += 1  
    m = bytes(s,UTF8)     
    print(m)  
    try:
        print(m.decode(UTF8,strict))
    except:
        pass   
cursor.close()
cn.close()

Running application gives

Test UTF-8
b'\xc5\x82 \xc5\x84'
ł ń

Test ODBC and UTF-8
DRIVER=Oracle in instantclient_12_2;UID=SCOTT;PWD=tiger;DBQ=111.222.33.44:12102/DB1212UTF;
b'SQLD \xc5\x82 \xc5\x84'
SQLD ł ń
b'Python \xc5\x82 \xc5\x84'
Python ł ń

Solution 2:

I suggest you download the Oracle SQL Developer which allows you to store polish characters corretly by performing

connect scott/tiger
createtable polish(col1 varchar2(50));
insertinto polish(col1) values('SQLD ł ń');
commit;
select COL1 from polish;

After that perform the row below using SQLPlus

set NLS_LANG=POLISH_POLAND.EE8MSWIN1250
sqlplus /nolog

and execute SQL:

connect scott/tiger
insertinto polish(col1) values('SQL*PLUS ł ń');
commit;

and check if row is correctly represented in database doing

select * from Polish;

using SQL Developer - not SQL*Plus !!

This should return

SQLD ł ń
SQL*PLUS ł ń

If row inserted by SQL*Plus is different you got a problem and you may need to create a AL32UTF8 DB.

If SQL*Plus inserts correctly, try to implement the solution shown below

/* 
   WINDOWS:
   ========
   
   Oracle Instant Client 12.2 + ODBC Driver installed and registered in c:\oracle\instantclient_12_2
   
   create  following environment variables or use "set" before starting application from commandline
   
   ORACLE_HOME=c:\oracle\instantclient_12_2
   PATH=%PATH%;%ORACLE_HOME%
   TNS_ADMIN=%ORACLE_HOME%
   NLS_LANG=POLISH_POLAND.EE8MSWIN1250

   Replace in connect string below 
     111.222.33.44 by Server IP
     12102         by Port of TNS-Listener on Server 
     DB1212UTF     by Service or SID of Oracle DB
     
   create 

      %ORACLE_HOME%\sqlnet.ora

   i.e
   
     c:\oracle\instantclient_12_2\sqlnet.ora
     
   with lines
   
   DIAG_ADR_ENABLED = OFF
   TRACE_LEVEL_CLIENT = 16
   TRACE_DIRECTORY_CLIENT = c:\oracle\instantclient_12_2\trc
   
   create after that directory
   
   c:\oracle\instantclient_12_2\trc
   
   UNIX/LINUX:
   ===========

   Download Oracle Instant Client 12.2 + ODBC Driver into /tmp
   Oracle Instant Client 12.2 + ODBC Driver located in ~/oracle/instantclient_12_2
   i.e.
   
   $ mkdir ~/oracle
   $ cd ~/oracle
   $ unzip /tmp/instantclient-basic-linux.x64-11.2.0.4.0.zip
   $ unzip /tmp/instantclient-odbc-linux.x64-12.2.0.1.0-2.zip
   $ unzip /tmp/oracle-instantclient12.2-odbc-12.2.0.1.0-2.zip
   $ unzip /tmp/instantclient-sqlplus-linux.x64-11.2.0.4.0.zip 
   
   create  following environment variables or use "export" before starting application from commandline     
   
   export ORACLE_HOME=$HOME/oracl/instantclient_12_2
   export PATH=$PATH:$ORACLE_HOMEexport TNS_ADMIN=$ORACLE_HOMEexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOMEexport NLS_LANG=POLISH_POLAND.EE8MSWIN1250

   Replace in connect string below 
     111.222.33.44 by Server IP
     12102         by Port of TNS-Listener on Server 
     DB1212UTF     by Service or SID of Oracle DB

   create directory
   
   mkdir -p ~/oracle/instantclient_12_2/trc
     
   create 

      $ORACLE_HOME/sqlnet.ora

   i.e
   
     ~/oracle/instantclient_12_2/sqlnet.ora
     
   with lines
   
   DIAG_ADR_ENABLED = OFF
   TRACE_LEVEL_CLIENT = 16
   TRACE_DIRECTORY_CLIENT = XXXX
   
   !!!! Replace the XXXX by the fully qualified path of !!!
   !!!! ~/oracle/instantclient_12_2/trc                 !!!
   
   perform
   
   cd$ORACLE_HOMEls libsqora*
   
   and write down name of ODBC driver - normally
   
   libsqora.so.12.1
   
   so maybe (*)
   
   /user/home/scotty/oracle/instantclient_12_2/libsqora.so.12.1
   
   Perform a SQL*Plus Connect to Database using no TNS entry and ensure Port,Host and service are correct:
   
   sqlplus scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.222.33.44)(PORT=12102))(CONNECT_DATA=(SERVICE_NAME=DB1212UTF)))

   and perform a SELECT, after that exitin$ORACLE_HOME/trc should be a trace generated
   
   after that, build a simple QT application using the code below and 
   ensure Port,Host and service are corret.
   
   Normally, you could create a User DSN in ~.odbc.ini to use 
   
   DRIVER={Oracle in instantclient_12_2}
   
   or you qualify the library direct - like
   
   DRIVER={/user/home/scotty/oracle/instantclient_12_2/libsqora.so.12.1}
   
   start application and if not connecting, goto
   
   $ORCLE_HOME/trc
   
   and lookup for latest traces - they contain errors etc.
   
*/   

#include <QCoreApplication>

int main(int argc, char *argv[])
{
       
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3");

 
    
    QString = "DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=111.222.33.44:12102/DB1212UTF;"
   
    db.setDatabaseName(connectString);
    //db.setUserName("SCOTT"); // Set Login Username
    //db.setPassword("tiger"); // Set Password if required
    if(!db.open())
    {
        cout << "Can't Connect to DB !" << endl;
    }
    else
    {
        cout << "Connected Successfully to DB !";
        QSqlQuery query;
        query.prepare("SELECT COL1 FROM POLISH");
        if(!query.exec())
        {
            cout << "Can't Execute Query !" << endl;
        }
        else
        {
            cout << "Query Executed Successfully !";
            while(query.next())
            {
                cout << "COL1: " << query.value(0).toString() << endl;

            }
        }
    }

    return 0;
}

Please review my encoding from python - I use

SELECT ASCIISTR(COL1)"COL1" from Polish

which returns the unicode characters in Oracle style as regular ASCII codes - like

' \xxxx'

instead normal

'\uxxxx'

checkout how I evaluate/extract the unicodes from colums value and build up a unicode string on my own.

Alternativly you my create a AL32UTF8 Database and use

NLS_LANG=POLISH_POLAND.AL32UTF8

Special characters often a mess when using non-Unicode characters.

Solution 3:

I tested the issue with Client=Server on my RHEL server with

export LANG=de_DE.UTF-8 
export NLS_LANG=POLISH_POLAND.AL32UTF8

which enables you even to enter all kind of Unicode characters in SQL*Plus - retrieving and insert works perfect.

After that, switching to

export NLS_LANG=POLISH_POLAND.EE8MSWIN1250

shows scrambled characters. Since we are still on terminal UTF-8 and Database is AL32UTF8 there must be a problems with EE8MSWIN1250 on Linux - but since AL32UTF8 works we do not have to care about

Switching to Windows Client finally gives correct results with

CHCP 1250
set NLS_LANG=POLISH_POLAND.EE8MSWIN1250

Search for "WORKING" below.

So - Next step is ODBC / QT. Will come back when QT has been build succesfully :-)

Server: Database 12.1.0.2 AL32UTF8 / RHEL 7.7/ LANG=de_DE.UTF-8

Client: W10Pro64bit / Instant client 12.1.0.2SQL Developer:
==============truncatetable polish;
insertinto polish(col1) values('SQLD ł ń');
commit;
select*from polish;

COL1
--------
SQLD ł ń

Server:
=======
export NLS_LANG=POLISH_POLAND.AL32UTF8

SQL>insertinto polish(col1) values('XXXX ł ń');

1row created.

SQL>select*from polish;

COL1
--------------------
SQLD ł ń
XXXX ł ń


export NLS_LANG=POLISH_POLAND.EE8MSWIN1250

SQL>insertinto polish(col1) values('XXXX ł ń');

1row created.

SQL>select*from polish;

COL1
--------------------
SQLD ▒ ▒
XXXX ▒ ▒
XXXX ł ń

SQL>commit;


SQL Developer:
==============select*from polish;

COL1
--------
SQLD ł ń
XXXX ł ń
XXXX Ĺ‚ Ĺ„ 


CLIENT 
=======

C:\ORACLE\IC\12201\instantclient_12_2>set NLS_LANG=POLISH_POLAND.AL32UTF8

C:\ORACLE\IC\12201\instantclient_12_2>chcp 65001
Aktive Codepage: 65001.

C:\ORACLE\IC\12201\instantclient_12_2>sqlplus scott/tiger

SQL*Plus: Release12.2.0.1.0 Production on Wt Paź 1301:44:502020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Data i godzina ostatniego pomyślnego logowania: Wt Paź 13202001:38:45+02:00

Połączono z:
Oracle Database 12c Enterprise Edition Release12.1.0.2.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
andReal Application Testing options

SQL>select*from polish;

COL1
--------------------
SQLD ł ń
XXXX ł ń
XXXX Ĺ‚ Ĺ„

SQL>insertinto polish(col1) values('WIN ł ń');
  2/
ERROR:
ORA-01740: w identyfikatorze brak jest znaku podwójnego cudzysłowu


WORKING
=======

C:\ORACLE\IC\12201\instantclient_12_2>set NLS_LANG=POLISH_POLAND.EE8MSWIN1250

C:\ORACLE\IC\12201\instantclient_12_2>chcp 1250
Aktive Codepage: 1250.

C:\ORACLE\IC\12201\instantclient_12_2>sqlplus scott/tiger

SQL*Plus: Release12.2.0.1.0 Production on Wt Paź 1302:20:522020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Data i godzina ostatniego pomyślnego logowania: Wt Paź 13202002:17:14+02:00

Połączono z:
Oracle Database 12c Enterprise Edition Release12.1.0.2.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
andReal Application Testing options

SQL>select*from polish;

COL1
--------------------
SQLD ł ń
8859-2 ł ń
XXXX ł ń
XXXX Ĺ‚ Ĺ„

SQL>insertinto polish(col1) values('WIN ł ń');

Utworzono wierszy: 1.SQL>select*from polish;

COL1
--------------------
SQLD ł ń
WIN ł ń
8859-2 ł ń
XXXX ł ń
XXXX Ĺ‚ Ĺ„ 

SQL>commit;

Ukończono zatwierdzanie.

SQL> exit


SQL Developer
=============
SQLD ł ń
WIN ł ń
8859-2 ł ń
XXXX ł ń
XXXX Ĺ‚ Ĺ„

Solution 4:

OK - setting on command line

set NLS_LANG=POLISH_POLAND.AL32UTF8
chcp 65001

and executing the code

import sys
from PyQt5 import QtSql
from PyQt5 import QtCore, QtWidgets
from PyQt5.QtWidgets import QMainWindow, QLabel, QGridLayout, QWidget
from PyQt5.QtWidgets import QPushButton
from PyQt5.QtWidgets import QMessageBox
from PyQt5.QtCore import QSize    

classMainWindow(QMainWindow):
    def__init__(self):
        QMainWindow.__init__(self)

        self.setMinimumSize(QSize(300, 200))    
        self.setWindowTitle("PyQt messagebox example - pythonprogramminglanguage.com") 

        pybutton = QPushButton('Show messagebox', self)
        pybutton.clicked.connect(self.clickMethod)
        pybutton.resize(200,64)
        pybutton.move(50, 50)        

    defclickMethod(self):
        from PyQt5 import QtSql
        from PyQt5.QtWidgets import QApplication, QWidget, QPushButton, QMessageBox
        
        STAP = QtSql.QSqlDatabase.addDatabase('QODBC3')
        STAP.setDatabaseName("DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=nerva:12102/DB1212UTF;");
        STAP.open()
        
        Q = QtSql.QSqlQuery()
        SQL = "SELECT COL1 FROM POLISH"
        Q.prepare(SQL)
        Q.exec_()
        rec = Q.record()
        
        rs = "Number of columns: {0}\n".format(rec.count())
        
        nameCol = rec.indexOf("COL1") # index of the field "name"while Q.next():
            rs += "{0}\n".format(Q.value(nameCol))
        
            
        QMessageBox.about(self, SQL , rs)
        
        

if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    mainWin = MainWindow()
    mainWin.show()
    sys.exit( app.exec_() )

gives

enter image description here

I think the issue is solved :-)

Solution 5:

Ok - QT 5.15.1 compiled with MingW64 on W10 succesfully.

Building with QT Creator with g++ following code

#include<QCoreApplication>#include<QString>#include<QVariant>#include<QtSql/QSqlDatabase>#include<QtSql/QSqlDriver>#include<QtSql/QSqlQuery>#include<iostream>usingnamespace std;

intmain(int argc, char *argv[]){

        QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3");

        QString connectString = QString("DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=nerva:12102/DB1212UTF;");

        db.setDatabaseName(connectString);
        cout << "-----------------------" << endl
             << connectString.toStdString() << endl
             << "-----------------------" << endl;

        if(!db.open())
        {
            cout << "Can't Connect to DB !" << endl;
        }
        else
        {
            cout << "Connected Successfully to DB !" << endl;

            QSqlQuery query;

            QString SQL = QString("SELECT COL1 FROM POLISH");
            cout << "-----------------------" << endl
                 << SQL.toStdString() << endl
                 << "-----------------------" << endl;

            query.prepare(SQL);

            if(!query.exec())
            {
                cout << "Can't Execute Query !" << endl;
            }
            else
            {        
                cout << "Query Executed Successfully !" << endl
                     << "-----------------------" << endl;
                while(query.next())
                {
                    QString x = query.value(0).toString();

                     cout << "COL1: " <<  x.toStdString()  << endl;

                }
            }
        }

        return0;
    }

Executing with different code pages and NLS_LANG settings

C:\DEV\QT\build-t1-Desktop-Debug\debug>chcp 1250
Aktive Codepage: 1250.

C:\DEV\QT\build-t1-Desktop-Debug\debug>set NLS_LANG=POLISH_POLAND.EE8MSWIN1250

C:\DEV\QT\build-t1-Desktop-Debug\debug>t1
-----------------------DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=nerva:12102/DB1212UTF;
-----------------------Connected Successfully to DB !
-----------------------SELECT COL1 FROM POLISH
-----------------------Query Executed Successfully !
-----------------------
COL1: SQLD ďż˝ ďż˝
COL1: WIN ďż˝ ďż˝
COL1: 8859-2 ďż˝ ďż˝
COL1: XXXX ďż˝ ďż˝
COL1: XXXX Ĺ‚ Ĺ„

C:\DEV\QT\build-t1-Desktop-Debug\debug>chcp 65001
Aktive Codepage: 65001.

C:\DEV\QT\build-t1-Desktop-Debug\debug>set NLS_LANG=POLISH_POLAND.EE8MSWIN1250

C:\DEV\QT\build-t1-Desktop-Debug\debug>t1
-----------------------DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=nerva:12102/DB1212UTF;
-----------------------Connected Successfully to DB !
-----------------------SELECT COL1 FROM POLISH
-----------------------Query Executed Successfully !
-----------------------
COL1: SQLD � �
COL1: WIN � �
COL1: 8859-2 � �
COL1: XXXX � �
COL1: XXXX ł ń

C:\DEV\QT\build-t1-Desktop-Debug\debug>chcp 1250
Aktive Codepage: 1250.

C:\DEV\QT\build-t1-Desktop-Debug\debug>set NLS_LANG=POLISH_POLAND.AL32UTF8

C:\DEV\QT\build-t1-Desktop-Debug\debug>t1
-----------------------DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=nerva:12102/DB1212UTF;
-----------------------Connected Successfully to DB !
-----------------------SELECT COL1 FROM POLISH
-----------------------Query Executed Successfully !
-----------------------
COL1: SQLD Ĺ‚ Ĺ„
COL1: WIN Ĺ‚ Ĺ„
COL1: 8859-2 Ĺ‚ Ĺ„
COL1: XXXX Ĺ‚ Ĺ„
COL1: XXXX Ĺ‚ Ĺ„

C:\DEV\QT\build-t1-Desktop-Debug\debug>chcp 65001
Aktive Codepage: 65001.

C:\DEV\QT\build-t1-Desktop-Debug\debug>set NLS_LANG=POLISH_POLAND.AL32UTF8

C:\DEV\QT\build-t1-Desktop-Debug\debug>t1
-----------------------DRIVER={Oracle in instantclient_12_2};UID=SCOTT;PWD=tiger;DBQ=nerva:12102/DB1212UTF;
-----------------------Connected Successfully to DB !
-----------------------SELECT COL1 FROM POLISH
-----------------------Query Executed Successfully !
-----------------------
COL1: SQLD ł ń
COL1: WIN ł ń
COL1: 8859-2 ł ń
COL1: XXXX ł ń
COL1: XXXX Ĺ‚ Ĺ„

So - comparing this with SQL*Plus tests, we got a strange behavior

SQL*Plus on Windows requires

set NLS_LANG=POLISH_POLAND.EE8MSWIN1250
chcp 1250

SQL*Plus on Unix requires

export NLS_LANG=POLISH_POLAND.AL32UTF8
export LANG=pl_PL.UTF-8

QT/C++/ODBC application on Windows requires

set NLS_LANG=POLISH_POLAND.AL32UTF8
chcp 65001

I assume that QT uses Unicode by default and so the behaviour is more comparable to Linux/Unix since POLISH_POLAND.AL32UTF8 is identical and codepage 65001 is equivalent to UTF-8.

Will test today or tomorrow PyQT5 ....

You may like these posts

Post a Comment for "How To Connect To Database Using Qoci Or Qodbc With Correct Encoding?"