GVKun编程网logo

python 连接oracle数据库：cx_Oracle（python2.7连接oracle数据库）

3

对于python连接oracle数据库：cx_Oracle感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解python2.7连接oracle数据库，并且为您提供关于C#连接Oracle数据库

对于python 连接oracle数据库：cx_Oracle感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解python2.7连接oracle数据库，并且为您提供关于C# 连接Oracle数据库，免安装oracle客户端、c#连接Oracle数据库、cx_Oracle - Python Interface for Oracle Database、cx_oracle python 并选择 Like %variable%的宝贵知识。

本文目录一览：

python 连接oracle数据库：cx_Oracle（python2.7连接oracle数据库）

python 连接oracle数据库：cx_Oracle（python2.7连接oracle数据库）

 注意：64位操作系统必须安装64位oracle客户端，否则会连接报错

安装第三方库：cx_Oracle

一、连接数据库的三种方式：

1、cx_Oracle.connect(‘账号/密码@ip:端口/数据库实例名‘)

2、cx_Oracle.connect(‘账号‘,‘密码‘,‘ip:端口/数据库实例名‘)

3、tns = cx_Oracle.makedsn(‘ip‘,‘端口‘， ‘数据库实例名‘)
　　cx_Oracle.connect(‘账号‘,tns)

示例：

　　tns = cx_Oracle.makedsn("192.168.81.186","1521","utf8186")

　　oracle.connect("interconn","ios",tns )

 

二、操作数据与MysqL（pyMysqL）基本一致，参考MysqL

import cx_Oracle as oracle

conn=oracle.connect("interconn/[email protected]:1521/utf8186") 　　　　#(‘账号/密码@ip:端口/数据库实例名‘)cursor=conn.cursor() 　　　　　　 #使用cursor()方法获取数据库的操作游标(游标是记录操作哪个库、表、字段、时间等信息)try: 　　cursor.execute("select * from mp_log_load") 　　　　 #Selectsql可以是其他数据库操作变量,执行sql语句,返回的是影响行数 　　OneData=cursor.fetchone()　　　　　　　　#一条查询结果，返回类型为tuple 　　AllData=cursor.fetchall() 　　　　　　　　 #所有查询结果，返回类型为tuple：((login_name1,passwd1,),(login_name2,passwd2),)，可遍历 　　conn.commit() 　　　　 　　 #提交操作 　　cursor.close() 　　　　　　 #关闭游标 　　conn.close() 　　　　　　　　 #关闭数据库连接except pyMysqL.Error as e: 　　print("MysqL error %d:%s" %(e.args[0],e.args[1])) 　　　　　　　　 #捕获异常（如数据库无法连接：ip、端口错误等） 　　conn.rollback() 　　　　　　 #报错时回退 　　cursor.close() 　　　　　　　　 #关闭游标 　　conn.close() 　　　　　　　　 #关闭数据库连接

C# 连接Oracle数据库，免安装oracle客户端

C# 连接Oracle数据库，免安装oracle客户端

 

一、方案1

首先下面的内容，有待我的进一步测试和证实。18.12.20

被证实了，还需要安装Oracle客户端，或者本机上安装oracle数据库软件。 18.12.20

 

1.下载Oracle.ManagedDataAccess.dll

2.添加到C#引用

3.命名空间引用

using Oracle.ManagedDataAccess.Client;

4. 连接参数的获取

然后打开Oracle安装的文件夹，以Oracle10为例D:\Oracle10\NETWORK\ADMIN\tnsnames.ora,打开这个文件，会看到

如果没有请按照图片内容输入即可，host=[主机名称],SERVICE_NAME=[随意取]

5.完成上述后，连接字符串：

strConnection = “Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LocalHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));Persist Security Info=True;User ID=XXXX;Password=XXXX;”；

简单说明一下这个连接字符串，HOST=LostHost|127.0.0.1|主机名三者均可，选择一个就行；

SERVICE_NAME=[即你刚才在tnsnames中取得名字]；

然后在输入你的Oracle账户名密码即可

 

6.测试代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        string  strConnection =@ "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=XXX)));Persist Security Info=True;User ID=XXX;Password=XXX;";//以上为一行，显示为三行
        public Form1()
        {
            InitializeComponent();
            connectTest();
        }
        public void connectTest()
        {
            try
            {
                string str_sql = "SELECT * FROM XXX";
                OracleConnection conn = new OracleConnection(strConnection);
                OracleCommand cmd1 = new OracleCommand(str_sql, conn);
                conn.Open();
                MessageBox.Show("连接成功！");
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
                MessageBox.Show("READSQL");
            }
        }
    }
}

 

 二、方案2

2.1 asp.net远程连接oracle数据库不用安装oracle客户端的方法

下面是asp.net连接远程Oracle数据库服务器步骤：

1.asp.net连接oracle服务器需要添加Sytem.Data.OracleClient命名空间。

2.连接时需要ConnectionString字符串，出现在web.config文件中，如下所示：

<connectionStrings>
  <add name="ConnectionString" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=你的数据库服务器ip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=你的数据库实例名)));User ID=scott;Password=tiger;Unicode=True" providerName="System.Data.OracleClient"/>
</connectionStrings>

上面的连接字符串添加了tns解析字符串，不需要再有OracleClient，否则需要安装，经过上面两步，在.Net Framework 2.0中远程连接Oracle 10g数据库服务器测试通过。

 

2.2 PL/SQL Developer不安装客户端连接远程oracle数据库

1、下载敏捷客户端InstantClient（40M左右，选择适合自己的下载，我的是instantclient-basic-win32-11.2.0.1.0.zip ）：

http://www.oracle.com/technetwork/cn/database/features/instant-client/index-092699-zhs.html

2、下载安装PLSQL（16M左右）：

http://www.onlinedown.net/soft/4902.htm

3、解压 instantclient-basic-win32-11.2.0.1.0.zip 到新建文件夹，如  D:/oracleclient/instantclient_11_2

用文本文档创建tnsnames.ora ，内容大致如下：

# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9204/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.          //以上为注释内容，因为我的tnsnames.ora是直接从服务器上download的
HSSL =                                             //数据库名称
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 218.108.242.30)(PORT = 1521))     //IP和端口
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hssl)               //数据库名称
    )
  )

 

用文本文档创建sqlnet.ora ，内容如下：

NAMES.DIRECTORY_PATH= (TNSNAMES)

SQLNET.AUTHENTICATION_SERVICES= (NTS)

到我的电脑、属性、高级、环境变量中添加一个环境变量，变量名为TNS_ADMIN，值为tnsnames.ora文件所在路径，

例如：D:/oracleclient/instantclient_11_2

 

4、安装完毕PLSQL后，启动，cancel，依次点击Tools->Preferences->Connection:

Oracle Home=D:/oracleclient/instantclient_11_2

OCI library=D:/oracleclient/instantclient_11_2/oci.dll

重新启动PLSQL即可进入数据库，初次登陆可能存在中文乱码的问题，解决方法如下：

 

到我的电脑、属性、高级、环境变量中添加一个环境变量：

变量名：NLS_LANG

变量值：AMERICAN_AMERICA.ZHS16GBK

重新连接，即可正常显示中文。

如果想转换为UTF8字符集，可以赋予“NLS_LANG”为 “AMERICAN_AMERICA.UTF8，其他编码同上

 

 

 

 

参考文章

C#连接Oracle数据库（无须安装客户端，只须添加dll即可）

在C#中，远程连接Oracle数据库（不安装客户端，无需多余配置）

.net远程连接oracle数据库不用安装oracle客户端

 

 

 

c#连接Oracle数据库

c#连接Oracle数据库


下面是使用OleDb连接Oracle数据库

Oracle连接字符串 mystr=“Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME= *****)));User Id=****;Password=*****;”

连接本地数据库只需更改上面**地方User Id表示用户名。

SERVICE_NAME试一试在Oracle11g\product\11.2.0\dbhome_1\database\spfileorcl.ora或Oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN下的两个ora文件中找一找


cx_Oracle - Python Interface for Oracle Database

cx_Oracle - Python Interface for Oracle Database

cx_Oracle - Python Interface for Oracle Database

Documentation · Installation . Release Notes . Source code · Mailing list

About cx_Oracle

cx_Oracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions.

cx_Oracle is licensed under a BSD license which you can find here.

cx_Oracle 6 has been tested with Python version 2.7, and with versions 3.4 and higher. You can use cx_Oracle with Oracle 11.2, 12.1 and 12.2 client libraries. Oracle''s standard client-server version interoperability allows connection to both older and newer databases. For example Oracle 12.2 client libraries can connect to Oracle Database 11.2 or later.

Features

  • Easily installed from PyPI.

  • Support for Python 2 and 3.

  • Support for Oracle Client 11.2, 12.1 and 12.2. Oracle''s standard cross-version interoperability, allows easy upgrades and connectivity to different Oracle Database versions.

  • Connect to Oracle Database 9.2, 10, 11 or 12 (depending on the Oracle Client version used).

  • SQL and PL/SQL Execution. The underlying Oracle Client libraries have significant optimizations including compressed fetch, pre-fetching, client and server result set caching, and statement caching with auto-tuning.

  • Full use of Oracle Network Service infrastructure, including encrypted network traffic and security features.

  • Extensive Oracle data type support, including large object support (CLOB and BLOB).

  • Direct binding to SQL objects. One great use case is binding Python objects to Oracle Spatial SDO objects.

  • Array operations for efficient INSERT and UPDATEs.

  • Array row counts and batch error handling for array operations.

  • Fetching of large result sets.

  • REF CURSOR support.

  • Support for scrollable cursors. Go back and forth through your query results.

  • Fetch PL/SQL Implicit Results. Easily return query results from PL/SQL.

  • Row Prefetching. Efficient use of the network.

  • Client Result Caching. Improve performance of frequently executed look-up statements.

  • Support for Advanced Queuing. Use database notifications to build micro-service applications.

  • Continuous Query Notification. Get notified when data changes.

  • Support for Edition Based Redefinition. Easily switch applications to use updated PL/SQL logic.

  • Support for setting application context during the creation of a connection, making application metadata more accessible to the database, including in LOGON triggers.

  • End-to-end monitoring and tracing.

  • Transaction Management.

  • Session Pooling.

  • Database Resident Connection Pooling (DRCP).

  • Privileged Connections.

  • External Authentication.

  • Database startup and shutdown.

  • Oracle Database High Availability Features, such as FAN notifications and Transaction Guard support.

DB API specification exclusions: The time data type is not supported by Oracle and is therefore not implemented. The methodcursor.nextset() is not implemented either as the DB API specification assumes an implementation of cursors that does not fit well with Oracle''s implementation of cursors and implicit results. See the method cursor.getimplicitresults() for more information.

Installation

See cx_Oracle Installation for detailed instructions.

  • The simplest way to install cx_Oracle is with pip:

    python -m pip install cx_Oracle --upgrade

    If a binary wheel package is not available on PyPI for your platform, the source package will be used.

    Note that if you download a source zip file directly from GitHub then you will also need to download an ODPI-C source zip file and extract it inside the directory called "odpi".

  • After cx_Oracle is installed, Oracle client libraries must also be installed and configured. These can be from Oracle Instant Client, from a local Oracle Database, or from a full Oracle Client installation.

    If you need the libraries, download and unzip the Oracle Instant Client ''Basic'' package for your platform and set PATH, LD_LIBRARY_PATH, or similar platform-specific library path loading environment. See the installation notes for ODPI-Cfor help.

    Versions 11.2, 12.1 and 12.2 of the Oracle Client libraries on Linux, Windows and macOS are supported. Users have also reported success with other platforms.

If you require cx_Oracle 5.3, download a Windows installer from PyPI or use python -m pip install cx-oracle==5.3 to install from source.

Very old versions of cx_Oracle can be found in the files section at Sourceforge.

Example

from __future__ import print_function

    import cx_Oracle

    connection = cx_Oracle.connect("hr", "welcome", "localhost/orclpdb")

    cursor = connection.cursor()
    cursor.execute("""
	    SELECT first_name, last_name
	    FROM employees
	    WHERE department_id = :did AND employee_id > :eid""",
	    did = 50,
	    eid = 190)
    for fname, lname in cursor:
	print("Values:", fname, lname)

For more examples, please see the samples and the test suite. You can also look at the scripts in cx_OracleTools and the modules in cx_PyOracleLib.

Documentation

See the cx_Oracle Documentation and Release Notes.

Help

Issues and questions can be raised with the cx_Oracle community on GitHub or on the mailing list.

cx_oracle python 并选择 Like %variable%

cx_oracle python 并选择 Like %variable%

如何解决cx_oracle python 并选择 Like %variable%

我正在尝试使用 %tempo% 执行基于“tempo”变量的查询 我在这里找到的解决方案都没有帮助我的问题

import cx_oracle
query="""SELECT description,local,point,date  
           FROM tbl_ext_tempo 
          WHERE point like ''%'' || :0 || ''%'' 
            AND ROWNUM < 8 
          ORDER BY date DESC
       """
cursor.execute(query,tempo)

异常值：

ORA-01036: illegal variable name/number

解决方法

您可以定义一个列表（lst），并将当前字符串按格式附加到列表中

lst = []
tempo = ''someValue''
query="""
         SELECT description,local,point,"date"
           FROM tbl_ext_tempo
          WHERE point LIKE :0          
            AND ROWNUM < 8
          ORDER BY "date" DESC
       """   
lst.append(''%{}%''.format(tempo))
cursor.execute(query,(lst))
print(cursor.fetchall())

date 不能作为保留关键字的列名，也许它可能是 "date" 作为引用。

关于python 连接oracle数据库：cx_Oracle和python2.7连接oracle数据库的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于C# 连接Oracle数据库，免安装oracle客户端、c#连接Oracle数据库、cx_Oracle - Python Interface for Oracle Database、cx_oracle python 并选择 Like %variable%的相关信息，请在本站寻找。

本文标签：

上一篇python 数据库更新操作（python 更新数据库数据）

下一篇python 类(3) property

相关文章