Python Oracle Notları (cx_Oracle)

İndirlecek Paketler
32 bit işlemci için http://www.oracle.com/technetwork/topics/linuxsoft-082809.html
64 bit işlemci için http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
Yükleme

# Gerekli Debian paketleri
su -
aptitude install libaio1 alien fakeroot
exit
# Oracle InstantClient kurulumu
#rpm paketlerini deb paketlerine çevirme
fakeroot alien oracle-instantclient11.2-basiclite-11.2.0.2.0.i386.rpm
fakeroot alien oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm
fakeroot alien oracle-instantclient11.2-sqlplus-11.2.0.2.0.i386.rpm
# deb paketlerini yükleme
su
dpkg -i oracle-instantclient11.2-basiclite_11.2.0.2.0-2_i386.deb
dpkg -i oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.deb
dpkg -i oracle-instantclient11.2-sqlplus_11.2.0.2.0-2_i386.deb
# library
dpkg -L oracle-instantclient11.2-basiclite
...
/usr/lib/oracle/11.2/client/lib		<<<
...
vim /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/11.2/client/lib
ldconfig
exit
# test
sqlplus <user>/<password>@<ip_address_oracle_server>:1521/<database_name_>
SQL> select * from v$version;
# cx_Oracle rpm2deb
fakeroot alien cx_Oracle-5.1-11g-py26-1.i386.rpm
# cx_Oracle kurulumu
su
dpkg -i cx-oracle_5.1-2_i386.deb
ln -s /usr/lib/python2.6/site-packages/cx_Oracle.so /usr/lib/python2.6/lib-dynload/
exit
# cx_Oracle test
python
>>>import cx_Oracle
>>>

Kod

import cx_Oracle
cnn = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = cnn.cursor()
sql = """ SELECT *
FROM table
WHERE t_id = :id """
cur.prepare(sql)
cur.execute(None, {'id': 100})
res = cur.fetchall()
print(res)
cur.execute(None, {'id': 100})
res = cur.fetchall()
print(res)
sql = """ INSERT INTO table
VALUES (:1, :2) """
cur.execute(sql, ('1', 'test'))
cnn.commit()
cur.close()
cnn.close()

Tarih sorgulama örneği string olarak direkt sorgulamada hata alabilirsiniz.

def get_tarih_ornegi():
    try:
        cnn = cx_Oracle.connect(ORC_DATABASE)
        cur = cnn.cursor()
        tarih = datetime.today()
        tarih = datetime.strftime(tarih, '%Y-%m-%d') + ' 00:00:00'
        sql = """SELECT count(*) FROM KUR
        WHERE KUR_TRH = to_date(:tarih,'YYYY-MM-DD HH24:MI:SS')
        """
        sql = sql.decode("UTF-8")
        print sql 
        cur.execute(sql, {'tarih':tarih})
        res = cur.fetchone()
        print res 
    except Exception, err:
        if DEBUG:
            raise
        else:
            print(str(err))

cx_Oracle where şartında in kullanımına örnek

def get_data(sData): 
    try:
        result = ""  
        cnn = cx_Oracle.connect(ORC_DATABASE)
        cur = cnn.cursor()
        sql = """ SELECT *
        FROM TABLE   
        WHERE ID IN (%s)
        """
        # data = ['258923','258935']
        data = sData 
        sql = sql % ','.join([":"+str(i) for i in range(len(data))])
        cur.execute(sql,data)
        result = cur.fetchall()
    except Exception, err:
        if DEBUG:    
            print(str(err))
        else:
            raise    
        result = None
    finally:
        try:
            cur.close()
            cnn.close()
        except:      
            pass     
        return result

Özellikle kayıt ekleme esnasında türkçe karakter sorunu yaşarsanız aşağıdaki şekilde çözümleyebilirsiniz.

import os
os.environ["NLS_LANG"] = "American_America.UTF8"

Linkler
http://cx-oracle.sourceforge.net/
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
http://www.oracle.com/technetwork/articles/dsl/python-091105.html
notları hazırlayan Emrah abiye teşekkürler.