海上月明

          editer by sun
          posts - 162, comments - 51, trackbacks - 0, articles - 8
             :: 首頁 :: 新隨筆 ::  :: 聚合  :: 管理

          cx_Oracle使用方法[轉]

          Posted on 2010-11-02 18:32 pts 閱讀(6445) 評論(0)  編輯  收藏

           


           

          正確安裝好cx_oracle之后,要使用它來連接到oracle數據庫進行操作,具體應該分3步走:

          第一步:導入cx_Oracle,建立連接

          >>> import cx_Oracle      #導入模塊
          >>> db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE') 建立連接,3個參數分開寫
          特別注意:這里的 'localhost:1521/XE'可以是你oracle net manager配置的鏈接名,如oracl,我就是在這里耽擱了半天

          >>> db1 = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE') 建立連接,3個參數連寫
          >>> dsn_tns = cx_Oracle.makedsn('localhost', 1521, 'XE')
          >>> print dsn_tns
          (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
          (CONNECT_DATA=(SID=XE)))
          >>> db2 = cx_Oracle.connect('hr', 'hrpwd', dsn_tns)
          >>> <strong style="line-height: normal;">print db.version</strong><br style="line-height: normal;" />
          10.2.0.1.0<br style="line-height: normal;" />
          >>> <strong style="line-height: normal;">versioning = db.version.split('.')</strong><br style="line-height: normal;" />
          >>> <strong style="line-height: normal;">print versioning</strong><br style="line-height: normal;" />
          ['10', '2', '0', '1', '0']<br style="line-height: normal;" />
          >>> <strong style="line-height: normal;">if versioning[0]=='10':</strong><br style="line-height: normal;" />
          ...       <strong style="line-height: normal;">print "Running 10g"</strong><br style="line-height: normal;" />
          ... <strong style="line-height: normal;">elif versioning[0]=='9':</strong><br style="line-height: normal;" />
          ...      <strong style="line-height: normal;">print "Running 9i"</strong><br style="line-height: normal;" />
          ...<br style="line-height: normal;" />
          Running 10g<br style="line-height: normal;" />
          >>> <strong style="line-height: normal;">print db.dsn</strong><br style="line-height: normal;" />
          localhost:1521/XE
          第二步:建立Cursor光標
          >>>cursor = db.cursor() 建立一個cursor
          之后,我們可以調用這個cursor.execute(‘SQL‘) 來執行SQL語句。比如:
          >>>cursor.execute(‘select * from tabs’)
          執行完畢以后,可以調用cursor.fetchall()一次取完所有結果,或者cursor.fetchone()一次取一行結果
          >>>row=cursor.fetchall()
          >>>for x in row:
                For y in x:
                   Print y,
                 Print
          這樣就可以按照表格的形式打印取得的結果了!
          在從oracle取出數據的時候,考慮到它的數據類型了嗎?下面就是數據類型的對應表

          Datatypes

          During the fetch stage, basic Oracle data types get mapped into their Python equivalents. cx_Oracle maintains a separate set of data types that helps in this transition. The Oracle - cx_Oracle - Python mappings are:
          Oracle
          cx_Oracle
          Python
          VARCHAR2
          NVARCHAR2
          LONG
          cx_Oracle.STRING
          str
          CHAR
          cx_Oracle.FIXED_CHAR
          NUMBER
          cx_Oracle.NUMBER
          int
          FLOAT
          float
          DATE
          cx_Oracle.DATETIME
          datetime.datetime
          TIMESTAMP
          cx_Oracle.TIMESTAMP
          CLOB
          cx_Oracle.CLOB
          cx_Oracle.LOB
          BLOB
          cx_Oracle.BLOB
          帶參數的查詢:
          >>> named_params = {'dept_id':50, 'sal':1000}
          >>> query1 = cursor.execute('SELECT * FROM employees 
          WHERE department_id=:dept_id AND salary>:sal', named_params)
          >>> query2 = cursor.execute('SELECT * FROM employees 
          WHERE department_id=:dept_id AND salary>:sal', dept_id=50, sal=1000)
          這種是名字參數,還可以按位置參數:
          r1 = cursor.execute('SELECT * FROM locations 
          WHERE country_id=:1 AND city=:2', ('US', 'Seattle'))
          注意:
          當只有一次參數的時候,也要把它寫成元組的形式,比如
          Cursor.execute(‘select name from user where id=:1’,(login_Id,))
          千萬要注意,login_id后面還帶有一個逗號,如果沒有逗號,他其實就是一個數據對象,但是當他后面有個逗號的時候,他就變成了元組的一個數據項,千萬要記住啊,我就是在這里徘徊了很久。!
          Cursor. Prepare的用法,
          這個方法就是在prepare之后,你再去execute的時候,就不用寫上sql語句參數了
          >>> cursor.prepare('SELECT * FROM jobs WHERE min_salary>:min')
          >>> r = cursor.execute(None, {'min':1000}) #注意,第一個參數是None,
          一次執行多條sql語句
          Large insert operations don't require many separate inserts because Python fully supports inserting many rows at once with the cx_Oracle.Cursor.executemany method. Limiting the number of execute operations improves program performance a lot and should be the first thing to think about when writing applications heavy on INSERTs.
          Let's create a table for a Python module list, this time directly from Python. You will drop it later.
          >>> create_table = """<br style="line-height: normal;" />
          CREATE TABLE python_modules (<br style="line-height: normal;" />
            module_name VARCHAR2(50) NOT NULL,<br style="line-height: normal;" />
            file_path VARCHAR2(300) NOT NULL<br style="line-height: normal;" />
          )<br style="line-height: normal;" />
          """<br style="line-height: normal;" />
          >>> from sys import modules<br style="line-height: normal;" />
          >>> cursor.execute(create_table)<br style="line-height: normal;" />
          >>> M = []<br style="line-height: normal;" />
          >>> for m_name, m_info in modules.items():<br style="line-height: normal;" />
          ...     try:<br style="line-height: normal;" />
          ...       M.append((m_name, m_info.__file__))<br style="line-height: normal;" />
          ...     except AttributeError:<br style="line-height: normal;" />
          ...       pass<br style="line-height: normal;" />
          ...<br style="line-height: normal;" />
          >>> len(M)<br style="line-height: normal;" />
          76<br style="line-height: normal;" />
          >>> cursor.prepare("INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)")<br style="line-height: normal;" />
          >>> cursor.executemany(None, M)<br style="line-height: normal;" />
          >>> db.commit()<br style="line-height: normal;" />
          >>> r = cursor.execute("SELECT COUNT(*) FROM python_modules")<br style="line-height: normal;" />
          >>> print cursor.fetchone()<br style="line-height: normal;" />
          (76,)<br style="line-height: normal;" />
          >>> cursor.execute("DROP TABLE python_modules PURGE")

          只有注冊用戶登錄后才能發表評論。


          網站導航:
           
          主站蜘蛛池模板: 乌苏市| 林周县| 宜昌市| 上饶县| 安达市| 孟村| 商河县| 明水县| 施甸县| 西贡区| 长春市| 呼玛县| 克东县| 庐江县| 翁牛特旗| 海伦市| SHOW| 承德县| 阿图什市| 织金县| 泾阳县| 洱源县| 漯河市| 嵊泗县| 黔东| 苍溪县| 汤原县| 贵德县| 枣阳市| 稷山县| 会同县| 海林市| 开阳县| 山西省| 百色市| 青海省| 南宫市| 准格尔旗| 晋江市| 封丘县| 和平县|