Python3 upload data from CSV to Oracle

1. To enable Python to operate the Oracle database, first install the Cx_oracle package.
2. Create a simple Python file to test whether the installation was successful.

1 #!/usr/bin/env Python32 #-*-coding:utf-8-*-3 4 Importcx_oracle5DNS_TNS=CX_ORACLE.MAKEDSN ('Host', port,service_name='Servic name')  6con = Cx_oracle.connect ('username','Password', Dns_tns)7Cur=con.cursor ()8sql ="SELECT * FROM test_table" 9 cur.execute (SQL)TenRe =Cur.fetchall () One  forIteminchRe: A     Print(item) - cur.close () -Con.close ()

3. Insert operation (small number of inserts)

1 #!/usr/bin/env Python32 #-*-coding:utf-8-*-3 4 Importcx_oracle5 ImportCSV6 7DNS_TNS=CX_ORACLE.MAKEDSN ('Host', port,service_name='Service Name')  8con = Cx_oracle.connect ('username','Password', Dns_tns)9Cur=con.cursor ()TenWith open ('/home/cyn/test.csv', newline="', encoding='Utf-8') as F: OneData_reader =Csv.reader (f) A      forRowinchData_reader: -sql ="INSERT into Runbook_pid_w_user (host_name, Account, Host_type) VALUES ('"+ row[0] +"', '"+ row[1] +"' , ' Windows Server ')"  -         Print(SQL) the cur.execute (SQL) - Con.commit () - cur.close () -Con.close ()

4. When the number of data inserted is large, you can use Executemany to insert the operation.

1 #!/usr/bin/env Python32 #-*-coding:utf-8-*-3 4 Importcx_oracle5 ImportCSV6 7DNS_TNS=CX_ORACLE.MAKEDSN ('Host', port,service_name='Service Name')  8con = Cx_oracle.connect ('username','Password', Dns_tns)9Cur=con.cursor ()TenWith open ('/home/cyn/test.csv', newline="', encoding='Utf-8', errors="Ignore") as F: OneData_reader =Csv.reader (f) AM = [] -i =0 -sql ="INSERT into Runbook_pid_pim (host_name, Account, Host_type) VALUES (: 1,: 2,: 3)" the cur.prepare (SQL) -      forRowinchData_reader: -i + = 1 -         ifI!=1: +M.append ((row[0],row[1],row[2])) -         ifI% 500 = =0: +             Print(i) A Cur.executemany (None, M) atM = [] - Cur.executemany (None, M) -     Print(i) - Con.commit () - cur.close () -Con.close ()

Python3 upload data from CSV to Oracle