Insert Data To Oracle Table By Python

最新推荐文章于 2023-04-09 18:04:42 发布

原创最新推荐文章于 2023-04-09 18:04:42 发布 · 905 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #table #oracle #python #file #sql

Python 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一种使用Python脚本将CSV文件中的数据批量导入到Oracle数据库的方法。该脚本利用cx_Oracle库连接Oracle数据库，并通过读取CSV文件逐行插入数据。此过程涉及错误代码的读取及格式化处理，适用于需要频繁更新数据库的应用场景。

This is an example to read the data from a CSV file and insert the data to a Oracle table using python script.

# Program Name: insert_data.py # Date: 2009-08-13 import cx_Oracle import datetime conn_xxx = cx_Oracle.connect('xxx/xxx@TNS Name') curs_xxx = conn_xxx.cursor() row_wid = 111 severity_cd = 'Warning' created_by = 'stu' now = datetime.datetime.now() error_file = 'C:/Documents and Settings/user name/Desktop/DeleteFlagTest/error_code.csv' f = open(error_file) lines = f.readlines() for i in range(1,len(lines)): error_code = lines[i][:4] error_desc = lines[i][5:-1] sql = """insert into TABLE_NAME (ROW_WID, ERROR_CODE, ERROR_CODE_DESCRIPTION_TXT, SEVERITY_CD, CREATED_ON_DT, CHANGED_ON_DT, CREATED_BY, CHANGED_BY) values ( :row_wid, :error_code,:error_desc,:severity_cd,:created_on_dt,:changed_on_dt,:created_by,:changed_by)""" bindVar = {'row_wid':row_wid,'error_code':error_code, 'error_desc':error_desc,'severity_cd':severity_cd,/ 'created_on_dt':now,'changed_on_dt':now,'created_by':created_by,'changed_by':created_by} curs_xxx.execute(sql,bindVar) # print sql row_wid = row_wid + 1 conn_xxx.commit() curs_xxx.close() conn_xxx.close() print 'The error codes have been inserted. Please check result!'