The problem of using cx_Oracle to connect Python to Oracle

if you want to use Python to extract the emp data table under the-sharpcscott user in the native Oracle database, execute the following program:

import pandas as pd
import cx_Oracle
import os

-sharp
os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.UTF8"

-sharpdataframe
-sharpquery():
-sharpIP
-sharpimport socket
-sharplocalhost = socket.gethostbyname(socket.gethostname())
def query(table):
    host = "localhost"    -sharpip C:\app\413022472\product\12.2.0\dbhome_1\network\admin\litsener.oraHOST()
    port = "1521"     -sharp
    sid = "orcl"    -sharp sqlplusselect instance_name from v$instance;
    dsn = cx_Oracle.makedsn(host, port, sid)

    -sharpscotttiger
    conn = cx_Oracle.connect("c-sharp-sharpscott", "tiger", dsn)  

    -sharpSQL
    sql = "select * from "+ table 

    -sharp pandasread_sqldataframe
    results = pd.read_sql(sql,conn) 

    conn.close
    return results

test_data = query("emp") -sharp

execute conn = cx_Oracle.connect ("c-sharp-sharpscott", "tiger", dsn) ) will report an error: the DatabaseError: ORA-12505: TNS: listener currently does not recognize the SID given in the connection descriptor

the content of the listner.ora document is as follows:

-sharp listener.ora Network Configuration File: C:\app\413022472\product\12.2.0\dbhome_1\network\admin\listener.ora
-sharp Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\413022472\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\413022472\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-2RE9AJU.lan)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

make sure that the name of sid is correct through sqlplus"s select instance_name from vogue instance:

could you tell me what went wrong? How to solve?

-sharp!/usr/bin/python import cx_Oracle as oracle import csv def oraclesql(cursor): -sharpfp = open('D:/1.sql') -sharpfp_sql = fp.read() cursor.execute("select * from emp") data = cursor.fetchall() -sharpprint (list(data)) return list(data) def write_to_csv(content): with open('oracle.csv','a') as csvfile: filename = ['empno','ename','job','mgr','hiredate','sal','comm','deptno'] -sharpfilename = ['LOTS','ORG_ID','YEAR_TIME','NAME_PATIENT','GENDER','BIRTHDAY','AGE','RESIDENT_ID','HUKOU_ADDRESS'] write = csv.writer(csvfile,delimiter=',') -sharp write.writeheader() write.writerows([content]) if __name__ == '__main__': ipaddr = "127.0.0.1" username = "scott" password = "scott" oracle_port = "1521" oracle_service = "orcl" try: db = oracle.connect(username+"/"+password+"@"+ipaddr+":"+oracle_port+"/"+oracle_service) -sharp except Exception as e: print(e) else: cursor = db.cursor() data = oraclesql(cursor) for i in data: text = list(i) -sharpprint(text) write_to_csv(text) cursor.close() db.close()